In Part 1 of this series I briefly covered the importing of text files using the built in Import facility in MS Access. In Part 2 of this series I briefly covered the exporting of data to text files using the built in Export facility in MS Access. In part 3 let's take a look at import/export specifications.

From the MS Access 2000 Help System we learn that, "An import/export specification contains information, such as file format, date order, or number formats, that Microsoft Access uses to import or export a fixed-width or delimited text file. Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process."

Seems to me that the key reason to use import/export specifications is to help automate a common import or export process. An obvious scenario is the receipt on a daily basis of data from an external system. This data comes in a fixed format text file. That means you have to keep teaching your MS Access database where the fields start and stop, as there are no delimiters.

Enter the specification ability of the import process. Let's follow through using an example.

Here is a small piece of the file.

T6587023010005
H7662002005006
Y5524120200150

Here is the same data with delimiters inserted, so you can see where the data is. (in our fictional sample, the system supplying the data cannot be modified to insert delimiters)

T6587,023,010,005
H7662,002,005,006
Y5524,120,200,150

The process:

• via the menu, File, Get External Data, select Import
• locate and select the file and click the Import button
• on my system it assumes Fixed Width (no delimiters found)
• note the Advanced... button in the lower left? if you click it right now, it assumes there is a single field (if you do click it to see, click the Cancel button to get back to the Import Wizard)
• click the next button
• create the 3 required break lines so that the four fields are "seen" by the computer
• now click the Advanced... button
• the Import Specification function defines the four fields; in my example it says the first field is a text field and the other three fields are long integer
• change the names to match the field names in the table the data is going to end up in
• if necessary, modify the field types (you can even cause the system to skip fields)
• click the Save As... button, give it a logical name, and click the OK button
• click the OK button to return to the Import process
• finish the import as per Part 1 of this series

Now that we know that, how does it help us?

(article continues after sponsor spot)

The next time you need to import data from the same system, assuming the file format stays the same on each pass of the process, the import process is simpler.

• via the menu, File, Get External Data, select Import
• locate and select the file and click the Import button
• click the Advanced... button
• click the Specs... button
• select the correct Import/Export Specification and click the Open button
• note how it brings in the proper name and and other custom changes you made
• click the OK button
• finish the import as per Part 1 of this series

This did not save you a ton of time, but what if there were 50 fields in that import file? Also, when we get to the VBA portion of this series, you will see the power of this system.

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 Richard W. Killey. All Rights Reserved.