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 👍
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.
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
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 👇
- Go to File Tab.
- Click Open.
- Browse to the location that contains the text file.
The Open dialog box pops up.
- Locate and double-click the text file that you want to open.
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.
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:
- Click Delimited for the Original Data Type.
- Click Next to proceed to Step 2.
- Click Comma as the delimiter.
The window will also show a Data Preview Field.
- 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.
- Select General.
- 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 😀
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).
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.
- Go to Data Tab.
- Click the Get Data drop-down button in the Get & Transform Data group.
- Select From File.
- Select From Text/CSV.
- 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.
- 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: 👇
- Click the Split column button.
- Select By delimiter from the drop-down list.
- Select Comma as the delimiter.
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…
- 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 😊