Microsoft Excel: How to Automate Text File Links
- 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:
- 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.
- 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:
- 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.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at email@example.com  or follow him on Twitter . David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link .