In the MS Access database projects that I work on, I often have to either import from or export to text files, also referred to as ASCII files or flat files. Let's start with a look at the menu methods available to you.

Under the File menu, there is an entry named "Get External Data", and then "Import...". The extra "..." indicates that when you choose that entry, a dialogue box will appear on the screen to ask you further details.

The next step is to change the "Files of type:" entry to show "Text Files (*.txt;*.csv;*.tab;*.asc)". It is my habit to use the .txt extension for all my text files. Thus, they will appear under that choice and I will then look for the appropriate file in my folder structure, highlight it and then click the import button.

Generally speaking, your text files should either have their "fields" separated by a delimiter (usually a comma or a tab, but can also be a space, semicolon, or any other chosen character), or they may be in fixed widths where each field always take the exact same space in each record.

Let's look at the delimited style first. The fields will be automatically sensed as per the delimiters. Subsequent dialogue box screens will ask you to

• indicate if the first row in the text file has field names
• pick a table for the data (if you do not have one predesigned, Access will build it for you)
• designate field names if needed
• adjust field types (Access tries to predetermine the types)
• indicate if the field should be indexed
• tell MS Access to skip a field
• add a primary key if it is creating a new table for your data
• and finally name the table if you chose to create a new one

As I am almost always importing temporarily, and will be deleting the new table later, I use the defaults for most of the choices.

If your data is in fixed format, the main difference in the above is that you must verify the field boundaries in the data. If your data is all filled in (ie. no spaces) Access will not be able to guess where the fields end. Note that the dialogue box that you get in this case has instructions on how to create, delete, or move a field break line. A little experimentation will get you sucessfully on your way quickly.

Finally, if Access encountered any problems importing your data, it will create an Import Errors table. For example, if your desired imported data table name is "ABC", then the error table (if needed) will be "ABC_ImportErrors". Simply open this table and use it to determine what went wrong. Then delete the two new table and try the import again. The most common problem is when you use defaults, and Access decides the field is numeric, but some of the data has alphabetic characters in that field. Just over ride the numeric choice with a string choice and the import will succeed.

Part 2 of this series will examine basic exporting.

(article continues after sponsor spot)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved.