How to Import Data in Excel (from Text, CSV, Database, Web etc.) – 10 Methods

Importing data means bringing data from any external source (text file, another workbook, database, etc.) into your Excel workbook.

Feature Image for Importing Data in Excel

The image above showcases a CSV (Comma Separated Values) file which was imported to an Excel worksheet.


IWhat is Importing Data in Excel?
1. Importing Data in Excel by Using Copy and Paste
2. Importing Data from Notepad or Text File to Excel
  ⏵i) Importing Text File by Opening in Excel
 ⏵ii) Importing Data by Connecting to the Power Query
 ⏵iii) Importing Data by Using the Text Import Wizard in Excel
 ⏵Tips for Importing Text Files to Excel
3. Importing Data from File in Excel
 ⏵i) Importing Data from Another Workbook
 ⏵ii) Importing Data from CSV File in Excel
 ⏵iii) Importing Data from PDF to Excel
4. Importing Data from the Database to Excel
5. Importing Data from Google Forms to Excel
6. Importing Data from Other Sources in Excel
 ⏵i) Importing Data from a Table
 ⏵ii) Importing Data from the Web
 ⏵iii) Importing Data from Picture to Excel
7. Importing Data from Existing Data Connections and Maintain Automatic Updates in Excel
8. Importing HTML to Excel
9. Using the Text to Columns Feature to Import WhatsApp Group Contacts to Excel
10. Importing Data in Excel by Using VBA Macros
Importing and Creating a Data Model Relationship in Excel
Avoid Common Mistakes While Importing Data in Excel


Method 1 – Importing Data to Excel by Using Copy and Paste Feature

You want to import the Spanish LaLiga Standings 2023-2024 by using the copy-and-paste feature.

Table to be Copied

 

  • Open the website and select the data.> Presss Ctrl + C to copy.> Select A1 in the Excel Worksheet> Paste the Data.

This is the output.

Data in Excel

  • To format data as a table, select the data range (A1:G21) and press Ctrl+ T.
  • In the Create Table dialog box, check the My table has header.> Click OK.

 Create table

  • This is the output.

Table for Copy and Paste


Method 2 – Importing Data from the Notepad to Excel

You want to import data from the Notepad (text file).


i) Importing a Text File to Excel

You can import text files into your Excel workbook  by opening them in Excel. You have a text file (“Text File(Semicolon)” ) in Notepad..

Input Text

Note: The semicolon is used as a delimiter,  marking the boundaries between different pieces of information. Common delimiters include commas (,), tabs (\t), spaces, semicolons (;), or colons (:).
  • Open Excel.
  • Click Open> Browse.

 Clicking on Browse

  • Locate the file, select it and click Open.

Select the Desired Text File in the Open Dialogue Box

  • In the Text Import Wizard, select Delimited >and check “my data has headers”> Click Next.

Selecting Delimited in the Wizard

  • Check Semicolon> Click Next.

Selecting the Delimiter and On Next

  • Select Column data format as General > Click Finish.

 Clicking on Format and Finish the Process

  • Imported data will be displayed.

 Imported Data (Without Formatting)

  • Format the dataset.This is the output.

Imported Data with Format

Note: When you import text files by opening them in Excel, data will be imported into the different workbooks.

Download Text File (Input)

Download the file to practice.


ii) Importing Data by Connecting to the Power Query

  • Click Data> Get Data> From File> From Text/CSV.

Clicking on From Text-CSV in the Data Ribbon

  • In the Import Data dialog box, select the text file and click Import.

Selecting the Text File Separated by Semi-Colon

  • Select Semicolon as Delimiter and click Transform Data.

 Clicking on Transform Data

  • The Power Query Editor will be displayed. Click Close and Load.

 Click on Close and Load in the Power Query Editor

  • Data will be imported as a table in a worksheet.

Imported Data by Connecting Power Query


iii) Importing Data by Using the Text Import Wizard in Excel

Enable the Wizard.

  • Click File> Options.

Enable Legacy Wizard

  • In the Excel Options dialog box, click Data > Check From Text (Legacy) in Show legacy data imports wizards> OK. This will enable the wizard.

Enable Wizard

Import the text file (semicolon is used as delimiter) as shown below into your Excel workbook using the Text Import Wizard.

 Input Text

 

  • Click Data> Get Data> From Legacy Wizards> From Text (Legacy).

. Clicking on Legacy Wizard

  • In the Import Text File dialog box, select the text file> Click Import.

Select and Import the File in the Wizard

  • In the Text Import Wizard dialog box, select Delimited> Check “My data has headers”> Next.

Clicking on Next in the Wizard

  • Check Semicolon> Click Next.

Selecting Semi-colon as Delimiter

  • Select Column data format as General > Click Finish.

 Formatting the Data Type in the Wizard

  • In the Import Data dialog box, select Existing Worksheet and A1> click OK.

Selecting the Worksheet and Range

  • This is the output.

Imported Data Using Import Data Wizard

  • Select the data range (A1:E10) and Click  Insert> Table. This is the output.

 Convert into Table


Tips for Importing Text Files to Excel

  • Use a text file format that Excel understands, like CSV or TXT.
  • Keep your text file with a clear structure, especially if it has columns and rows.
  • Use a separator (like a comma, semicolon, or tab) to divide your data into columns correctly.
  • If your file is massive, break it into smaller parts.
  • Check for inaccuracies after importing, like missing info or weird formatting.

Method 3 – Importing Data from a File in Excel

 

i) Importing Data from Another Workbook

You want to import the data from this workbook to another.

 Data in Another workbook

  • Click Data> From File> From Excel Workbook.

Clicking on from Another Workbook

  • In the Import Data dialog box, select the workbook> Click Import.

Select the File and Import

  • Select Table1 (you can check the preview to ensure you are importing the right data)> Click Load.

Select the Table in the Workbook and Load

  • This is the output.

 Imported Table from Another Workbook

Download Input Workbook

You can use this workbook to practice.


ii) Importing Data from a CSV File in Excel

A CSV (Comma-Separated Values) file is a simple text file where data is organized in rows and columns. Each value in a row is separated by a comma.

CSV files don’t store formatting or formulas and can be opened by various spreadsheet programs.

You have a CSV file.

 Input Text File

To import the CSV file into Excel:

  • Click Data> From File> From Text/CSV.
  • In the Import Data dialog box, select the CSV file> Click Import.

Select the File and Click on Import

  • Click Load.

Click on Load

  • This is the output.

 Imported CSV File in Excel

Note: If you save your text file as CSV, you can easily import data by opening the file.

Download CSV (Input)


iii) Importing Data from PDF to Excel

You can also import data from a PDF. Your PDF file should be organized like a table.

Input PDF

  • Click  Data> From File> From PDF.
  • In the Import Data dialog box, select the PDF file> Click Import.

Select the PDF File and Click on Import

  • Click Load.

Select the Table and Click on Load

  • This is the output.

 Imported Table from PDF File

You can also import an XML file, or even a folder to your Excel Worksheet.

Download PDF (Input)

Read More: How to Import PDF to Excel


Method 4 – Importing Data from a Database to Excel

There are several databases (Microsoft Access, SQL Server Database, Analysis Service).

You have imported data from a database: “From Database” (click here to Download)

From Database

 

  • Click Data> From Database> From Microsoft Access Database.

Clicking on From Database

  • Select the database file in the Import Data dialog box> Click Import.

Selecting the Database File

  • In the Navigator dialog box, select the data> Click Load.

 Selecting the Data in the Database from the Dialogue Box

  • This is the output.

 Database Imported as Table


Method 5 – Importing Data from Google Forms to Excel

  • Go to Google Forms> Click View in Sheets.

Click on View in Sheets

  • Click File> Download> Comma Separated Values(CSV). Save the CSV file.

Download it as CSV Files

  • Click Data> From File> From Text/CSV> Select the file in the Import Data dialog box> Click Import.

CSV File Download

  • Click Load in the Dialog box.
  • This is the output.

Imported Data from Google Forms

Download CSV from Google Form (Input)

Read More: How to Create Excel Sheet from Google Form


Method 6. Importing Data from Other Sources in Excel

i) Importing Data from a Table

You want to import this table.

Input Table

 

  • Go to the worksheet that contains the table you want to import. Select the whole table.
  • Click Data > Get Data > From Other Sources> From Table/Range.

 Clicking on From Table or Range

  • The table will be displayed on the Power Editor. Click Close and Load.

Click on Load&Close in the Power Query Editor

  • This is the output.

 Imported Data from Table


ii) Importing Data from the Web

You want to import this table from a website.

 Input From Web

Go to the Web page and copy the link.

  • Click Data> From Other Sources> From Web.
  • In the From Web dialog box, paste the link in the URL box> Click OK.

 Pasting the Web Link in the Dialogue Box

  • In the Navigator dialog box, select the table> Click Load.

Selecting the Table in the Dialogue Box

  • This is the output.

 Imported Data Table From Web


iii) Importing Data from a Picture to Excel

You want to import data from the following picture.

Data Picture

  • Click  Data > From Other Sources> From Picture> Picture from File.

 Clicking on Importing from Picture

  • In the Insert picture dialog box, select the file> Click Insert.

Selecting the Image to Import Data

  • The Data from Picture box will be displayed. Click Insert Data> Insert Anyway.

 Clicking on Insert Data

This will import the data as shown below:  Values are not correctly placed and an empty column B is added. Data must be manually placed in the correct cells.

Data from Picture

  • This is the output.

 Formatted Data from Picture

If you are using a Smartphone Excel app to import data from a picture:

Launch the Excel app.
Create a new workbook.
Click the camera icon.
Take a photo or choose one from your phone.
Adjust the pic, then tap “Continue.”
Click “Open.” See how many items need to be checked. Review all or hit “Open Anyway.

 

Read More: How to Extract Data from Image into Excel


Method 7. Importing Data from Existing Data Connections and Maintain Automatic Updates in Excel

Here, the Existing data table is Text_File_Semicolon (That we have imported in Method 2(i).

To import it:

  • Click  Data > Get Data Using an Existing Connection

Clicking on the Icon

  • In the Existing connection dialogue box, click Tables > select the existing table (i.e, Text_File_Semicolon). > Click OK.

 Selecting the Table From Existing Connection

  • In the Import Data dialog box, Click Table> Existing Worksheet>

Selecting the Worksheet

  • This is the output.

 Imported Data from Existing Connection

  • To update data automatically, click Data > Refresh All.

Update The Data Connection


Method 8 – Importing HTML to Excel

 

You have the HTML code for the data below.

 HTML Input

This dataset is the output of your HTML file.  To import it:

  • Click Data> From File> From Text/CSV.
  • In the Import Data dialog box, Click  All Files > select the HTML file > Click Import.

 Selecting the HTML file

  • In the navigator box, select the table. > Click Load.
Note: Check the preview before clicking Load.

 Click on Load

  • This is the output.

 Imported Data as Table from HTML


Method 9 – Using the Text to Columns Feature to Import WhatsApp Group Contacts to Excel

You have a WhatsApp group named “Programming”.

 Group WhatsApp

There are 311 participants. To import the contacts:

  • go to the web.whatsapp.com website.
  • You will see the one-time generated QR code.
  • Scan it on your smartphone to connect it to your WhatsApp account.

Scan QR Code to Connect WhatsApp

  • You can access the WhatsApp account on the computer.
  • Select your WhatsApp group> Right-click “Programming” WhatsApp group.>Click Inspect

Click on Inspect

  • A window with the back-end codes of your WhatsApp group contacts will be displayed.
  • Go to the Elements > Choose Copy> Click Copy element.

Copy from the Elements

  • Paste the back-end codes in your Excel sheet.
  • Select the entire code from the Excel sheet.> Go to Data > Click Text to Columns command.

 Convert the Text into Column

  • Select Delimited > Click Next.

 Clicking on Delimited

  • Select Comma as delimiter> Click Next.

Select Comma in the Wizard

  • Select General in Column data format > Click Finish. 

Set the Data Type as General

  • Select the entire row.> Click Copy
  • Right-click the Excel sheet. > Go to Paste Special > Select Transpose and click it.

Tips: You can select an entire row by pressing Ctrl+ Shift+ (→).

 Use Paste Special

  • This is the output.

Imported Data From Whatsapp


Method 10 – Importing Data in Excel by Using VBA Macros

You have this dataset in another Excel workbook like the image below.

 Input VBA to Import Data

B1:F14 contains the data you want to import.

  • Go to the Developer tab> Visual Basic.

Clicking on Visual Basic

In the VBA Editor, click Insert> Module. Enter the following code and save the file.

Sub Import_Data_Using_VBA()
Dim Location_of_File As String
Location_of_File = Application.GetOpenFilename
If Location_of_File = "False" Then
Beep
Exit Sub
End If
Application.ScreenUpdating = False
Set Import_Data = Workbooks.Open(Filename:=Location_of_File)
Import_Data.Worksheets(1).Range("B1:F14").Copy
Sheet16.Range("B4").PasteSpecial xlPasteValues
Import_Data.Close
Application.ScreenUpdating = True
End Sub

Insert Mode and Code

  • Press F5 to run the code.
  • In Open, select the workbook> Click Open.

Select the File after clicking F5

This is the output.

 Imported Data By VBA Macros

Download Workbook (Input)


Importing and Creating a Data Model Relationship in Excel

You have two datasets in two different worksheets.

Input Table

The second is in another worksheet that contains the ID of the employees, sales product, and amount by the employees.

Product Details

Import the second data table using the Copy and Paste feature:

  • Select the second data table(A1:C8).> Right-click  the mouse> Click Copy.

Copy the Table

  • Place the cursor on E5.> Paste the table.

This will import the table, placing it beside the first table.

Copy the Table

Both tables contain one column in common: ID. To create a relation between them, use the Table Relationship feature.

  • select the table> go to the Table Design tab> double-click the Table Name
  • Assign a name (i.e., Info_Table) to the new table.

 Naming the Table

  • Repeat the steps for the second dataset also. Rename the second table (i.e., Product)

 Name the other Table

  • Select a cell in the first table (Info_Table)> go to the Data tab> select Relationships in the Data Tools group.

 Selecting the Relationship

Clicking on New

  • In the Create Relationship command box, choose

In Table: The first created table (Info_Table)
In Column (Foreign): Common column between the tables (ID)
In Related table: The new table (Product)
In Related Column (Primary): The Common column between the tables (ID)

  • Click OK.

Selecting the Foreign Key

  • Go to the Insert tab> click Pivot Table> select From External Data Source.

 Clicking on from External Source

  • In the PivotTable from an external source window, click Choose Connection…

 Create the Connection

  • In Existing Connections, in the Tables group, select Tables. In Workbook Data Model > click Open.

 Choose the Table

  • Click OK to close the PivotTable from an external data source

Select the Worksheet

  • In Pivot Table Fields, the two tables will be shown: the Info table has two columns: ID, and Employee; and the Product table has 3 columns: ID, Product, and Sales. You want to create relationships by connecting Employee in the Info_Table and Sales in the Product table.
  • Drag Employee from the Info group to the Rows field, and enter Sales in the Values field.

Selecting Employee

  • Click Auto-Detect to create Relationships.

 Click on Auto Detect

  • This is the output.

Table showing Relationship

Note: While creating relationships between tables, tables must have at least one common Column.

 

Avoid Common Mistakes While Importing Data in Excel

  1. Eliminate special characters.
  2. Keep columns and rows in both files aligned and named.
  3. Export a spreadsheet as a template.
  4. Break down large files.
  5. Import fewer than 10,000 rows at once.
  6. Format all cells as text.
  7. Confirm column names, order, and completeness.
  8. Unmerge cells before importing.
  9. Select a suitable encoding during import.
  10. Ensure data length, formats, and values are correct.

Download Workbook (All Methods)

 

Importing Data in Excel: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Zahid Shuvo
Zahid Shuvo

Zahid Hasan Shuvo, a Naval Architecture and Marine Engineering graduate from BUET, Bangladesh, has contributed nearly a year to the Exceldemy Project as an Excel and VBA Content Developer. Within this timeframe, he has crafted over 8 tutorial articles, and besides offering valuable solutions to aid users effectively. Zahid also expresses keen interests in Excel & VBA, Data Analysis, Machine Learning, AI Engines, and Prompt Engineering, showcasing a diverse skill set and contributing to the dynamic environment of... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo