By David Ringstrom, CPA
Some time ago, I explained how to use Excel's Text to Columns Wizard
for separating text within a spreadsheet into columns. Although this approach is helpful for data that's in a spreadsheet, in other cases, you may wish to link spreadsheets to text files that change periodically. In this article, I'll walk you through the steps of automating this process.
A common method of accessing text files is by way of the Open dialog box. As shown in Figure 1, you can choose from a variety of file types, including text files. Depending on the file type, Excel may automatically separate the data into columns, as shown in Figure 2, or leave all the data in a single column, as shown in Figure 3. Data, such as that shown in Figure 3, can then be separated into columns manually by way of the Text to Columns Wizard.
Figure 1: You can choose from various file types in the Open dialog box.
Figure 2: Excel automatically separated the data into columns.
Figure 3: Excel left all the data in a single column.
However, many users don't realize there's an alternate way to access text files that can minimize steps, and perhaps even automate integrating text file-based data into your spreadsheets. To do so:
- Excel 2007 and later: Choose From Text in the Get External Data section of the Data tab.
- Excel 2003 and earlier: Choose Data, Import External Data, and then Import Data.
At this point, in any version of Excel, you'll be presented with a variation of the Open dialog box:
- Excel 2007 and later: The dialog box is labeled Import Text Files.
- In Excel 2003 and earlier: The dialog box is labeled Select Data Source.
Regardless of your Excel version, browse to your text file and then click the Import button. A new dialog box labeled Text Import Wizard will appear. This wizard is identical to the Text to Columns Wizard, but I'll walk through the steps for anyone unfamiliar with either:
On the first screen of the wizard, you signify whether your text file is delimited for fixed width:
- Delimited: Signifies there's a separator between each field, such as a tab, comma, space, semi-colon, or perhaps the | character, which is referred to as the pipe symbol.
- Fixed width: Signifies that each field is allocated a specific number of characters, meaning the data lines up evenly in columns.
As shown in Figure 4, a data preview window shows you the first few rows of the text file as an aid in determining the file type. Once you choose delimited or fixed width, click Next to proceed to the next step in the wizard.
Figure 4: Excel provides a data preview window where you can view the first few rows of your text file.
If you choose delimited, the second step in the wizard will look like Figure 5, from which you can choose from standard delimiters or enter a delimiter of your choice, such as the pipe symbol in the Other field. Despite the misleading width of the Other field, you can only place a single character in this field.
Figure 5: If you selected Delimited in Step 1 of the Text Import Wizard, you select your delimiter in Step 2.
Alternatively, if you choose fixed width, the second screen in the wizard will look like Figure 6. Excel will attempt to automatically position breaks between each column. You can reposition these column breaks by dragging them left or right. Double-click on a break to remove it or click once between two fields to insert a column break.
Figure 6: If you selected fixed width in Step 1 of the Text Import Wizard, you can set field widths in Step 2.
Once you've specified a delimiter or confirmed the column breaks, click Next to move to the third step of the wizard. This step allows you to apply various settings to columns within your text file. By default, every field is marked as General, which means it will be imported into your spreadsheet with no particular format applied. This means that leading zeros will be dropped, and dates in formats such as yyyy-mm-dd will be treated as text within the spreadsheet. Rather than accepting the default of General, you can choose from three other options:
- Text: This option instructs Excel to treat a column as text, meaning leading zeros will be preserved in columns that contain numeric values, such as zip codes.
- Date: This option instructs Excel to convert a column to a date. When you choose this option, you must then specify the date format used within your text file, such as yyyy-mm-dd. Excel will convert these dates to the standard date format for Excel spreadsheets.
- Do Not Import (Skip): This option instructs Excel to in effect throw away unneeded columns within the text file. The original text file will remain intact, but any columns that you mark as Do Not Import will not appear in your spreadsheet.
Once you've changed any settings in Step 3 of the wizard and clicked Finish, the dialog box shown in Figure 7 will appear. Once you confirm the location where the data from the text file should reside, click the Properties button. The dialog box shown in Figure to 8 reveals the settings I use frequently to automate connections to text files:
Figure 7: The Import Data dialog box appears once you've clicked Finish in the Text Import Wizard.
- Prompt for File Name on Refresh: Turn this option off if you're establishing a permanent link to a specific text file, or leave it on if you'll be bringing a different text file in each time you refresh.
- Refresh Data on File Open: This option ensures that data from the text file is imported into your spreadsheet automatically. This is a significant automation opportunity, as it establishes a set-and-forget link to text files. Alternatively, you leave this option off and manually refresh the data, as I'll describe in a moment.
- Fill Down Formulas in Columns Adjacent to Data: One of my favorite aspects of Excel is this ability to have any formulas I place to the right of data from a text file to get copied down additional rows, or removed from unneeded rows, whenever I refresh data from a text file. This allows me to establish set-and-forget connections to text file-based data.
Figure 8: The External Data Range Properties dialog box allows you to automate connections to text files.
Click OK twice to complete the import process. Going forward, you can manually refresh the data by right-clicking on any cell within the current data and choosing Refresh, as shown in Figure 9. To return to the Properties dialog box shown in Figure 8, right-click on the data and choose Data Range Properties. To replace the current data with that from another text file, choose Edit Text Import from the right-click menu. You'll be prompted to walk through the Text Import Wizard again, as described above.
Figure 9: Right-click on a cell and select Refresh to manually refresh your data.