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:

imported data in excel file

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 ๐Ÿ‘

The only other notable thing about CSV files is that the double quote symbol indicates text fields. Thatโ€™s important because text fields can contain commas and line breaks.

If theyโ€™re contained within quotes, commas, and line breaks wonโ€™t end the field or the record.

Kasper Langmann, Microsoft Office Specialist

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

text file

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.
go to file in Excel
  1. Click Open.
  2. Browse to the location that contains the text file.
open command

The Open dialog box pops up.

  1. Locate and double-click the text file that you want to open.
open dialog box

If you open a CSV file, Excel automatically opens the text file and displays the data in a new workbook. If the file is a text file, Excel starts the Text Import Wizard.

Kasper Langmann, Microsoft Office Specialist

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:

  1. Click Delimited for the Original Data Type.
comma delimited
  1. Click Next to proceed to Step 2.
import text wizard
  1. Click Comma as the delimiter.
comma delimited

The window will also show a Data Preview Field.

  1. Click Next to proceed to Step 3.
import text wizard

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.

  1. Select General.
column data format
  1. Finally, click Finish to get your data.
click finish

This is now the result. The imported data is now in a new blank worksheet, not in your existing worksheet ๐Ÿ˜€

new excel workbook

Opening a text file in Excel does not change the format of the file โ€” you can see this in the Excel title bar, where the name of the file retains the text file name extension (for example, .txt or .csv).

Kasper Langmann, Microsoft Office Specialist
text format

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.
data tab
  1. Select From File.
  2. Select From Text/CSV.
open txt file or csv file in excel
  1. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.
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.
  1. Click Transform Data.
transform data group

The Text Power Query Editor window will pop up. It shows the data preview as well.

data preview field

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: ๐Ÿ‘‡

  1. Click the Split column button.
  2. Select By delimiter from the drop-down list.
comma delimited
  1. Select Comma as the delimiter.
comma separated values
  1. Click OK.
click ok

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

text editor - power query

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.

text editor - power query

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

  1. Finally, click the Close & Load button.
data preview field

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

import data in Microsoft Excel

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.