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.

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

I⏷What 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.

• 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.

• 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.

• This is the output.

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..

• Open Excel.
• Click Open> Browse.

• Locate the file, select it and click Open.

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

• Check Semicolon> Click Next.

• Select Column data format as General > Click Finish.

• Imported data will be displayed.

• Format the dataset.This is the output.

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.

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

• Select Semicolon as Delimiter and click Transform Data.

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

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

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

Enable the Wizard.

• Click File> Options.

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

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

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

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

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

• Check Semicolon> Click Next.

• Select Column data format as General > Click Finish.

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

• This is the output.

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

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.

• Click Data> From File> From Excel Workbook.

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

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

• This is the output.

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.

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.

• Click Load.

• This is the output.

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.

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

• Click Load.

• This is the output.

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)

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

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

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

• This is the output.

Method 5 – Importing Data from Google Forms to Excel

• Go to Google Forms> Click View in Sheets.

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

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

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

Download CSV from Google Form (Input)

Method 6. Importing Data from Other Sources in Excel

i) Importing Data from a Table

You want to import this 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.

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

• This is the output.

ii) Importing Data from the Web

You want to import this table from a website.

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.

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

• This is the output.

iii) Importing Data from a Picture to Excel

You want to import data from the following picture.

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

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

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

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.

• This is the output.

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

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

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

• This is the output.

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

Method 8 – Importing HTML to Excel

You have the HTML code for the data below.

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.

• In the navigator box, select the table. > Click Load.

• This is the output.

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

You have a WhatsApp group named “Programming”.

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.

• You can access the WhatsApp account on the computer.
• Select your WhatsApp group> Right-click “Programming” WhatsApp group.>Click 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.

• 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.

• Select Delimited > Click Next.

• Select Comma as delimiter> Click Next.

• Select General in Column data format > Click Finish. 

• 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+ (→).

• This is the output.

Method 10 – Importing Data in Excel by Using VBA Macros

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

B1:F14 contains the data you want to import.

• Go to the Developer tab> 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

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

This is the output.

Download Workbook (Input)

Importing and Creating a Data Model Relationship in Excel

You have two datasets in two different worksheets.

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

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

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

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

This will import the table, placing it beside the first 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.

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

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

• In the Manage Relationships dialog box, click 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.

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

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

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

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

• 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.

• Click Auto-Detect to create Relationships.

• This is the output.

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