O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Importing Data with the FileSystemObject

Problem

How can I import data using the FileSystemObject?

Solution

You learned how to export information using the FileSystemObject in Exporting Data with the FileSystemObject, but the import process is a bit more challenging. When importing data, you don't just have to read the file, you have to disassemble the file into each piece that goes into the database.

To keep this example simple, I'll assume that the table into which the data will be imported exists and is empty. I'll also assume that the data is properly formatted, and that there's no need for error checking. You can easily add data-formatting and error-checking features if and when you need them.

Let's assume that the data is structured as shown in Figure 7-13. The first line of the data contains the name of the table to be used. In this case, we will import the data into Table7-4.

Some data to be imported into Access

Figure 7-13. Some data to be imported into Access

The second line of the text file contains the names of the fields to be imported. The field names are separated by commas. Note that there is no comma following the last field name.

The data type for each field is found in the third line of the text file. These values don't have to match up with the data types available in Access, as only the import program uses this information.

Following these three lines of information is the data. Each line of data represents ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required