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...

It's not a reality—yet—but accounting software is poised to eliminate accountants. We are at a tipping point for many similar professions: online education replacing professors, legal software replacing...
Whenever I speak to accountants about creating a cloud practice, the most common question is, “How do I charge my clients?” Ten years ago, maybe even five years ago, if I would’ve posed this question...
While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...

Upcoming CPE Webinars

Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.
Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.