Microsoft Excel has an import wizard, but if you structure the text correctly, you can bypass the wizard altogether.
We don’t open foreign files with Microsoft Excel—or do we? When importing a .txt or .csv file, that’s exactly what you should do. If the source file delimits the data correctly, Excel will make quick work of setting up the data in .xlsx format. It’s so easy, I’m surprised users don’t do this all the time.
In this tutorial, we’ll discuss delimiting data, and then I’ll show you how to open a .txt and .csv file in Microsoft Excel.
SEE: 98 Excel tips every user should master (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can user earlier versions of Excel.
About opening a text file into Microsoft Excel
Microsoft Excel supports two ways to import data from a text file. You can open the file in Excel, or you can import the data using an Excel wizard. The two most common text file formats you’ll work with are .txt and .csv, where .csv stands for comma separated values.
You can’t import just any .txt file and expect Excel to take care of everything. A bit of setup in the text file will facilitate the process. If the content doesn’t contain delimiters, open the text file and insert them yourself, which is a lot of work. If you’re lucky, you can run a replace task to insert the commas. A .csv file is easier because the software automatically inserts a delimiter, usually a comma, between fields of text when you save the file.
The structure of the data is extremely important. The file must delimit the fields and the record (Figure A). To see the special character symbols, click Show/Hide in the Paragraph group on Word’s Home tab. This file contains several records. A tab character separates the fields, and a paragraph mark at the end of each record marks the record. The next record begins on the next line. Similarly, the .cvs file contains the same data and uses a comma delimiter.
Figure A
How to open a .txt file in Excel
The easiest way to import a delimited text file into Excel is to open that file in Excel. It’s possible and it’s quick. Simply open the text file as you would any other file:
Close the file you plan to import, and open a blank Excel workbook.
Click the File tab and choose Open. Or, if Open is on the Quick Access Toolbar (QAT), click it. You can also press Ctrl + O to start the Open task.
If the text file is available in the recently used files list (Figure B), click it, and you’re done, and you can skip the following steps.
Figure B
If you don’t see the file, click This PC and browse through the folders until you find it.
Use the File Type dropdown to select the text file formats (Figure C). Doing so updates the list of files you can open, including text files.
Figure C
Double-click the text file, or select it and click Open.
Excel’s import wizard does a good job of interpreting the data if the structure is correct. You don’t need to make any changes in the first pane shown in Figure D. We’re importing a delimited file, and the data doesn’t have a header row. Click Next.
Figure D
The second pane (Figure E) sets the tab character as the delimiter, which is correct. Check the preview pane at the bottom. The wizard correctly parses the data into fields. At this point, click Finish. You can make other changes when importing data, but in our case, we’re done.
Figure E
As you can see in Figure F, Excel opened the text file perfectly. This is only possible because the data is delimited in a way Excel can interpret and work with.
Figure F
How to open a .csv file in Excel
Opening a .csv file is as easy as opening a .txt file, and usually, you won’t have to clean up the data before doing so because the save task inserts the delimiter. Repeat the process in the previous section, except for the following:
- When searching for the file you want to open, remember that you’re looking for a .csv file, not a .txt file.
- Make sure to change the file type in the Open dialog box to .csv instead of .txt.
- In step 2 of the Text Import Wizard, uncheck Tab and check Comma to identify the delimiter.
If done correctly, Microsoft Excel will open the .csv file, and the results will be the same as with the .txt file process.
By the way, you can also quickly export data from Excel to a text file by saving the .xlsx file as a .txt file. Try it—the results might surprise you.
Stay tuned
If a text file is property structured, you can open the file with Excel and go straight to work. A .csv file is the same way. You won’t always get that lucky though, so we’ll discuss more complex import issues in a future article.
