Importing text files into Excel comes up very often in engineering because there are many different types of engineering software that export text files. Often, you’ll need to bring data from data acquisition, remote measurement, CAD programs, or finite element software into Excel for further examination. So it’s really good to be able to know how to get that data into Excel without a lot of hassle so you can quickly perform your analysis.
In this particular example, I have some data in a text file called Flow_Pressure_Data.txt.
Elevate Your Engineering With Excel
Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.
Copy-and-Paste
The simplest way to get this data into Excel, although it may not be the best, is just to highlight it, type Ctrl+C to copy it, then select a cell in the workbook, and type Ctrl+V to paste it.
If the text data contains tab-separated-values, meaning that the columns of data are separated by a tab-stop character, then the data will automatically split into columns in Excel.
In this case, the data was separated into tabs, and everything came into columns like we’d want.
However, once the data has been pasted into Excel, there is really no good way to refresh it later on if the source file changes. The only way to update it is to re-paste the data. And maybe that’s ok for some situations. But if it’s not, we can use a text data import in Excel to create a refreshable table of data from a text file.
Import a Text File to Excel
You can find this command under the Data tab in the “Get External Data” group.
By clicking on that button, we’re given the option to select a text file. And once I select the text file, the Text Import Wizard opens.
There are more details to Text Import Wizard that I’ll cover in another post, but for now click Next twice and then Finish.
Now Excel is asking where to put the data. The cell that is selected is going to be the top left corner of the imported data.
If I select cell F2, that selection will be represented in the window. You can also choose to import the data to a new sheet.
So once the location for the data is selected, I can click Ok and the data is added to the worksheet starting at cell F2.
Modifying Text Import Properties
As I mentioned above, if you copy-and-paste data into Excel there’s no way to update it without copying and pasting again. But if you import a text file into Excel, there are many more options. We can see what those options are by right-clicking anywhere on the imported data.
Now there are three extra commands to select in the context menu that were not available before:
- Edit Text Import
- Data Range Properties
- Refresh
By selecting Edit Text Import, we can select a new file to import – which will take us through the whole text import wizard again.
The next command allows us to set the Data Range Properties. By clicking on that, the Properties window opens. From this window we can change things like the name or whether or not the query definition should be saved.
Refreshing Imported Text Data
You can also control how the text data is refreshed. You could choose to update the data on a fixed schedule or whenever the workbook is opened. You can also choose whether or not to prompt for the file name every time the data is refreshed. If I know that the file name will be the same each time I like to unselect the box, so I’ll do that here.
I’m going to click OK in the Data Range Properties window and open up the text file again. I’ll change the first flow value to 5.0 and the first pressure value to 20.0, and then save it.
Next I’ll go back to Excel, right-click on the import data, and select “Refresh” way at the bottom.
Excel will then go back out to the text file and re-query the data and pull in the updated values.
Wrap-Up
Hopefully you can see how importing a text file into Excel can be so much more powerful than simply copying and pasting the data. I use this feature quite often when I need to update the data in a spreadsheet from software sources outside of Excel.
