In the last section, you saw how to do a basic text import. The Text Import Wizard offers a lot of options to customize this type of import. You can specify how you’d your text data to be imported, minimizing the amount of cleanup that you need to do on the data.
In this section, we’ll use Ideal_Gas_Property_Data.txt as the data source. Go to Data > Get External Data > From Text, and open that file. The first window of the Text Import Wizard will open.
- Mar 07, 2006 I often open textfiles in Excel and stores them as sheets in one workbook. I have a macro for this which I have to edit almost everytime because the column sizes the textfiles vary from time to time. What I want to do is to open the 'Text Import Wizard' from within the macro.
- Choose the File menu’s Open command or click the Data tab’s Get External Data from Text button.
To force Excel to run the Import Text Wizard, you can change the file name extension from.csv to.txt before you open it, or you can import a text file by connecting to it (for more information, see the following section). Newer versions Office 2016-2010 Import a text file by connecting to it (Power Query). The Text Import Wizard dialog box opens. Make sure the Delimited radio button is selected, and press 'Next.' On the next dialog, check the 'Comma' check box, and uncheck the other check boxes in the 'Delimiters' frame. Leave the Text Qualifier with the default (') entry in it. Excel 2016 course, also suitable for earlier versions. In this demo Peter Kalmstrom shows two different ways of reaching the Text Import Wizard to add data i.
There are two main types of data files that you’ll import into Excel. Delimited files have a character separating the columns (i.e. a tab stop). Fixed width files have a specific number of characters in each column.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
First, we’ll see how this data looks with the Delimited option. Choose it, then click Next.
Delimiters can be virtually any character. Tab, comma and space are the most common. The tab character is selected by default. However, in the preview window, there are no black lines separating the column – this tells us that Excel hasn’t found any tab delimiters. Unselect Tab, and choose Space. Now, black lines will appear between the columns:
Excel recognizes that the columns of data are separated by spaces. It automatically checks the box for “Treat consecutive delimiters as one.” In this text file, there are multiple spaces between the columns of data. If this box was not checked, each space would indicate a new column.
Click Next, then Finish. Choose a location on the worksheet to store the data and click OK.
Viewing the data in the spreadsheet exposes a flaw in selecting the space delimiting option. Gas names such as “carbon dioxide” and “carbon monoxide” are composed of two words separated by a space. Excel interprets this space as a column separator, so those rows have an extra column and all of the data is shifted:
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
Although we could manually clean up the data, those changes would be lost if the data were refreshed.
Instead, we’ll choose better options in the Text Import Wizard. Right-click on the data and select Edit Text Import.
Next, double-click on the file Ideal_Gas_Property_Data.txt.
This time, instead of the Delimited option, choose Fixed width. Click Next. Excel will automatically determine where the columns should be, and places break lines as separators between them. To move a break line, click and drag it to a new position.
Text Import Wizard Excel 2016 Version
You can also add or remove break lines. To add one, click at the top of the preview window, below the ruler (where the arrowheads are showing for the existing break lines). To delete one, double-click on it, or drag it away from the preview window.
Don’t worry about any excess spaces before or after an entry. Excel will clean those up. Just adjust the break lines so that each column includes all of the intended data. In this case, the defaults are fine. Click Next.
The third window of the Text Import Wizard contains options to set a data format for each column. The General format is appropriate for most data. It treats numeric values as numbers, date values as dates, and everything else as text. You can also specify a date or text format. Click each column in the preview window and set its type.
You also have the option of omitting columns. Sometimes, data files contain more information than you need. You can select any of these columns and choose Do not import column (skip) to remove it from the data table. For example, if you only need the columns containing the gas name and the constant pressure specific heat, you can remove the other columns. Select the Formula column, then click Do not import column (skip). The header will change from “General” to “Skip Column” (see the headers in the image below). Repeat for the other unnecessary columns.
Text Import Wizard Excel 2016 Shortcut
Click Finish, and you’ll have a table containing only the pertinent data, with no additional cleanup required.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]