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

Don’t you just wish you can magically import data that you need into a new usable format faster with nothing to do any manual work? 😩

Well, Microsoft Excel can grant your wish. Excel allows you to open other file formats such as comma-separated values (CSV) files and tab-separated (TXT) files. You can also import data from a text file by converting them into an Excel spreadsheet you can now work with.

Sounds awesome, right? 😀

Learn how to import data from a text file into Excel with our step-by-step guide.

Download this free practice workbook we’ve prepared for you to get started.

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.

This is now the result. The imported data is now in a new blank worksheet, not in your existing worksheet 😀

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 Text Power Query Editor window will pop up. It shows the data preview as well.

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.

Look at the preview to check if it’s good to go.

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.

Now, this is what it will look like. The data is imported into a new worksheet in the current Excel workbook 😊

That’s it – Now what

Clearly, Microsoft Excel helps you import data from text files with just a few clicks. This saves you from manually typing data from other file formats by converting them to a usable file format you can work on 👍

If this sounds awesome already, then you are in for a big surprise! Excel can actually help you do 50 tasks all at once with just a single click 🤯

That’s right, this is possible with Excel Mighty Macros. It’s perfect for your workflow automation. Learn how to record, and edit a macro, the basics of the VBA editor, and learn how to write your first macro from scratch. Learn it all with me!

Join my free email course to get started with macros. It’s free and perfect for beginners 😊

Other resources

Now that you learned how to import, how about you learn how to export as well? Learn How to Export Power BI Data to Excel with our step-by-step guide here.

You can also learn how to merge two spreadsheets in Excel to help you summarize data for your reports 📊📈 Read more about Excel consolidation here.