Microsoft Excel: How to Automate Text File Links

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:
  1. 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.
  2. 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.
  3. 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.

You may like these other stories...

You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
For bitcoin users, the taxman cometh. And you best know how to calculate taxes owed on what the IRS calls convertible virtual currency.In March 2014, the IRS issued Notice 2014-21, which declares virtual currency will be...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 21
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.