How to Import Text Files Into Excel: Step-by-Step Guide (2024) (2024)

How to import a text or CSV file in Excel

Before you learn how to import a text file into Excel, you need to learn the basics of the text file formats first.

What is a text or CSV file?

Text files are formatted in a very specific way so they can be read by programs like Microsoft Excel. There are different text file formats. The commonly used ones are:

• Comma-separated values text files (.csv), in which the comma character (,) typically separates each field of text.
• Delimited text files (.txt), in which the TAB character typically separates each field of text.

Now, how does Excel reads data from a text or CSV file? 🤔

Each line of the file is a “record”. In Excel, that means it’s a row. Records are delineated by line breaks. Each line break corresponds to one row in Excel.

Delimiters such as commas, tabs, or space characters separate fields within the record. CSV files are comma delimited.

So, when Excel sees “Excel,CSV,Formatting”, it will import data in an Excel worksheet like this:

You can change the separator character that is used in CSV files or text files. This may be necessary to make sure that the import or export operation works the way that you want it to.

You can import or export up to 1,048,576 rows and 16,384 columns 👍

Prepare the Text File

Of course, in a real-life setting, you don’t need to prepare the text file. You may have already downloaded it in TXT or CSV file formats waiting to be imported into an Excel file.

For our tutorial, let’s open a Notepad File to create a sample text file. If you’re using Mac, open TextEdit📝

Type the following data in your notepad as shown below. Or copy this and paste it into your notepad.

Last Name,First Name,Address
Reed,James,Washington
Owens,Donovan,Texas
Hutchins,Ricky,Wisconsin

Then save this text file to be used later.

There are two ways to import data from a text file with Excel: you can open it in Excel, or you can import it as an external data range. Let’s try to do each one of them 😊

Import a text or CSV file by opening it in Excel

The first way you can import text files in Excel is rather simple.

Just open a CSV file or a text file that you created in another program as an Excel workbook by using the Open command.

Open your practice workbook and do the following steps 👇

1. Go to File Tab.

2. Click Open.
3. Browse to the location that contains the text file.

The Open dialog box pops up.

4. Locate and double-click the text file that you want to open.

Since our file is a text file, the Text Import Wizard window pops up 🧙

There are 3 steps to go through. Let’s start with Step 1 out of 3.

The Text Import Wizard has determined that our data is Delimited. But to be sure:

5. Click Delimited for the Original Data Type.

6. Click Next to proceed to Step 2.

7. Click Comma as the delimiter.

The window will also show a Data Preview Field.

8. Click Next to proceed to Step 3.

Here you can select individual columns and apply specific formatting by clicking them and choosing an option from the Column data format list.

We’ll leave it as General, which is what you’ll most often use.

9. Select General.

10. Finally, click Finish to get your data.

Simple, right? 😊

Import a text or CSV file by connecting to it (Power Query)

You can also import data from a text file into an existing worksheet.

1. Go to Data Tab.
2. Click the Get Data drop-down button in the Get & Transform Data group.

3. Select From File.
4. Select From Text/CSV.

5. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.

In the preview dialog box, you have several options:

• Select Load if you want to load the data directly to a new worksheet.
• Select Load to if you want to load the data to a table, PivotTable/PivotChart, an existing worksheet, a new Excel worksheet, or simply create a connection. You also have the choice of adding your data to the Data Model.
• Select Transform Data if you want to load the data to Power Query, and edit it before bringing it to Excel.

6. Click Transform Data.

The data are already separated as you can see 👀

In the case that the data are NOT separated as they should be, here’s what to do: 👇

7. Click the Split column button.
8. Select By delimiter from the drop-down list.

9. Select Comma as the delimiter.

10. Click OK.

As you can see, it added another unnecessary column. To remove or undo any changes you’ve done, head over to the Query Setting panel on the right side of the window.

Click the ❌ symbol. This will remove or undo any applied steps.

Look at the data preview again. If everything looks good…

11. Finally, click the Close & Load button.