This tutorial will walk you through the steps to import a text file in MS Excel 2019.
The normal text file formats are:
- Text files with a .txt extension, and a TAB character that separates each text field.
- Text files with a .csv extension, and a comma separates each text field.
Both text file types are flat files without specific formatting. You can open or edit a .txt file in Notepad or other word processors, and a .csv file in MS Excel.
Note: You can use other separators that MS Excel supports in your .txt and .csv files.
4 Ways to import text files into Microsoft Excel:
- Open .txt and .csv file in MS Excel
- Initiate the Text Import Wizard
- Use Get and Transform Data Group
- Drag and drop the .txt file into MS Excel
For this exercise, we will use a text file with three columns. It includes the name, designation, and salary breakup of ten employees. Each column contains different text data separated by a tab.
Let us begin.
Method #1a: Open a .txt File in MS Excel
We will use this method to first open a .txt file and later open a .csv file.
Step #1: Open MS Excel
Click on the MS Excel icon to start the application.
Go to the File tab and click Open.
In the Open dialog box, browse to the text file you wish to import.
Click on the file name.
Click the Open button.
Excel launches the Text Import Wizard, as we are importing a .txt file.
Step #2: Follow the Text Import Wizard instructions
The wizard checks the file to ensure a seamless and correct data import. The wizard recognizes the data as Delimited. It means a tab separates each of the text fields.
In the Wizard you will see the following options:
- Delimited: This is selected by default.
- Fixed Width: Select this option if all the columns in the file are of the same length.
- Start import at row: Select a different row number if you do not want the import to start at row 1.
- My data has headers: Select this if the text file includes headers.
Click on the Next button.
Step #3: Follow the Text Import Wizard next step
Use this screen to keep the default delimiter or to select a different one.
Delimiters
Our example file uses tabs as a separator, so Tab is the default selection. The Wizard will pick the separator you used.
Data Preview displays the text as it will appear when separated into columns on the Excel sheet. If it does not seem right, select another delimiter.
Data Preview (Fixed width)
If you selected Fixed Width in the previous step, you can click and drag to set the field widths in the Data preview box.
Follow the instructions on the screen. Click and drag to create, delete, or move a line break on the horizontal scale in the Data preview box.
Click the Next button to continue.
Step #4: Follow the Text Import Wizard final step
Use this screen to set the Data Format for each column.
Click each column to select it, and choose the data format to apply.
General: Select this to convert number formats to Excel currency format.
Text: Select this to convert numbers to Excel text format.
If you do not want to import the selected column, click Do not import column (skip).
If your text file includes one or more dates, click Date and choose the date format. Click the down arrow in the Date field to select the date format.
Advanced: click this option to display the Advanced Text Import Settings dialog box.
Specify the decimal and thousands of separators that you have used in the text file.
Trailing minus for negative numbers:
Select this option if your file contains one or more numeric values with a trailing minus sign.
Click the OK button to save the settings.
Click the Finish button.
After opening a text file, Excel does not change the file format. Check the Excel title bar, it displays the file name with a .txt extension.
Click Save As, to save the file in Excel format.
Note: If Excel fails to convert the data after importing, you can still apply the format to each column or cell to match your need.
Method #1b: Open a .CSV File in MS Excel
Importing a .csv file or comma separated values text file is easy. In this file type, a comma (,) separates each text field.
Step #1: Open MS Excel
Click on the MS Excel icon to start the application.
Go to the File tab and click Open.
In the Open dialog box, browse to the file you wish to import.
Step #2: Select the file
Click on the .csv file to select it.
Click on the Open button.
Excel opens the .csv file and display the data in a new workbook.
After opening a comma-separated values file, Excel does not change the file format. Check the Excel title bar, it displays the file name with a .csv extension.
Click Save As, to save the file as an Excel file.
Method #2: Use Text Import Wizard
The Text Import Wizard is a legacy feature. It is supported by different versions for backward compatibility. To use the feature, you must first enable it.
Step #1: Open MS Excel
Click on the MS Excel icon to start the application.
Go to the File tab and click Options.
Step #2: Enable Text Legacy
In the Excel Options dialog box, select Data.
Under Show legacy data import wizards header, click the From Text (Legacy) check box.
Click the OK button to return to the spreadsheet.
Step #3: Import the Text File
In the main menu header, click on the Data tab.
Click on Get Data for a drop-down menu to open.
Select Legacy Wizards.
Select From Text (Legacy).
The Import Text File dialog box opens.
Select Text Files in the file type dropdown list.
Browse to select the file you wish to import.
Click on the file name.
Click on the Import button.
Step #4: Follow the Text Import Wizard instructions
The wizard checks the file to ensure a seamless and correct data import. The wizard recognizes the data as Delimited. It means a tab separates each of the text fields.
In the Wizard you will see the following options:
- Delimited: This is selected by default.
- Fixed Width: Select this option if all the columns in the file are of the same length.
- Start import at row: Select a different row number if you do not want the import to start at row 1.
- My data has headers: Select this if the text file includes headers.
Click on the Next button.
Step #5: Follow the Text Import Wizard next step
Use this screen to keep the default delimiter or to select a different one.
Delimiters
Our example file uses tabs as a separator, so Tab is the default selection. The Wizard will pick the separator you used.
Data Preview displays the text as it will appear when separated into columns on the Excel sheet. If it does not seem right, select another delimiter.
Data Preview (Fixed width)
If you selected Fixed Width in the previous step, you can click and drag to set the field widths in the Data preview box.
Follow the instructions on the screen. Click and drag to create, delete, or move a line break on the horizontal scale in the Data preview box.
Click the Next button to continue.
Step #6: Follow the Text Import Wizard final step
Use this screen to set the Data Format for each column.
Click each column to select it, and choose the data format to apply.
General: Select this to convert number formats to Excel currency format.
Text: Select this to convert numbers to Excel text format.
If you do not want to import the selected column, click Do not import column (skip).
If your text file includes one or more dates, click Date and choose the date format. Click the down arrow in the Date field to select the date format.
Advanced: click this option to display the Advanced Text Import Settings dialog box.
Specify the decimal and thousands separators that you have used in the text file.
Trailing minus for negative numbers:
Select this option if your file contains one or more numeric values with a trailing minus sign.
Click the OK button to save the settings.
Click the Finish button.
In the Import Data box, select where you want your data – in the Existing worksheet or a New worksheet.
Click the OK button to import the data.
After opening a text file, Excel does not change the file format. Check the Excel title bar, it displays the file name with a .txt extension.
Step #7: Save the file
Click Save As, to save the file in Excel format.
Note: if Excel fails to convert the data after importing, you can still apply the format to each column or cell to match your need.
Method #3: Use Get and Transform Data
Use this method to import data from a text file into an existing worksheet.
Step #1: Open a new spreadsheet
Click on the MS Excel icon to start the application.
Click on New to create a new spreadsheet.
In the main menu bar, click on the Data tab.
In the Data ribbon, in the Get & Transform Data group, click From Text/CSV.
Or
Click the Get Data icon.
From the dropdown menu select File and click From Text/CSV.
Step #2: Import the file
The Import Data dialog box will open.
Browse to locate the text file you want to import.
Click on the file name.
Click the Import button.
Step #3: Preview the file before loading
The Preview dialog box will open.
The following options will be displayed:
- File Origin: Keep the default as it corresponds to your machine’s system settings.
- Delimiter: Select the delimiter used to separate the values in your text file.
- Data Type Detection: Keep the default.
Select Load if you want to load the data directly to a new worksheet.
Click Load to import the file.
The data from the .txt file is loaded in Excel.
You may notice that using this method, Excel imports the data but does not assign the same file name.
You can click the down arrow in each of the columns to sort the data. The options are self-explanatory.
- Sort Smallest to Largest,
- Sort Largest to Smallest,
- Sort by Color,
- Filter numbers,
- Filter text, or
- Perform a Search.
Step #4: Save the file
Click Save As, to save the file as an Excel file.
Method #4: Drag and Drop the Text File into MS Excel
Use this easy method of importing your text file in MS Excel.
Step #1: Open Windows Explorer
Locate the text file to import.
Step #2: Open a new spreadsheet
Click on the MS Excel icon to start the application.
Click on New to create a new spreadsheet.
From the Windows Explorer window, click on the .txt file
While holding the mouse button down, drag the file onto the Excel window.
Release the mouse button.
Excel opens and displays the data in a new workbook.
Step #3: Save the file
Click Save As, to save the file as an Excel file.
Conclusion
Importing text files into MS Excel becomes a necessity for efficient analysis. Knowing how to do this is important. You often receive .csv files as exports from banking applications and other software.
The Text Import Wizard makes the process quite simple, and even better is the drag-and-drop function.
You can use one of the four methods described in this tutorial to ease the process of importing. After importing, you can edit the files to match your needs.