Often, it’s necessary to import data into Excel from a text file. Engineering software such data acquisition, remote measurement, and CAD programs often export data into text files, and you may need to import that data into Excel for further analysis.
The simplest way import data into Excel is to copy it from the text file and paste it into your spreadsheet. The file Flow_Pressure_Data.txt contains example data. Open it with a text editor, highlight the data, and type Ctrl-C. Choose a destination cell in Excel and use Ctrl-V to paste the data.
If the text file contains tab-separated values, columns of data that are separated by a tab stop character, then the data will automatically split into columns in Excel:
However, once the data have been pasted into Excel, there’s no way to refresh the data later on if the source file changes. The only way to update it is to re-paste the data.
That may be fine in some situations, but if your source data may change, there’s another method that will create a refreshable table of data from a text file. This is called a “text data import.” To use it, go to the Data tab. In the Get External Data group, choose From Text.
This option may appear as a larger icon on your screen.
When you click From Text, you’ll be prompted to select a text file. Find Flow_Pressure_Data.txt. The Text Import Wizard will open. There’s more information about the Text Import Wizard in the next section. For this example, the default selections are fine, so click Next twice, then Finish.
A window will appear so you can specify where to place the data:
The cell that you specify will be the top-left corner of the imported data. There’s also the option of importing the data to a new sheet. Click OK.
This method of importing data will give you additional options that the first method did not. Right-click anywhere within the data table and you’ll see three new options: Edit Text Import, Data Range Properties, and Refresh.
By selecting Edit Text Import, you can select a new file to import – which will take you through the Text Import Wizard again.
Data Range Properties allows you to modify things like the name or whether the query definition should be saved. Usually it’s not necessary to modify these two options. You can also control how the data is refreshed. Uncheck the box next to “Prompt for file name on refresh.”
You could also choose to update the data on a fixed schedule or whenever the workbook is opened. The remaining options are for formatting or tell Excel what to do if there is a different number of rows in the next data set.
Click OK. Open the text file again, and change some of the values. Save the file. Return to Excel, right-click the imported data, and select Refresh. Excel will re-query the data (without prompting for a file name).
The next section will cover some of the options that are available to help you get text data into Excel cleanly without importing unnecessary data
