ACC: How First Line of Data Is Used to Import Delimited Text

ID: Q99401

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

The first line of data is extremely important when you are importing delimited text files. Microsoft Access 2.0 and earlier uses the first line of data to determine the following:

The number of fields

The data type for each field

For this reason, it is important to verify that the first line of data correctly reflects the rest of the data in the table. This article discusses some of the common problems you may encounter when the first line of data is incorrect and how to work around these problems.

In Microsoft Access 97, the Import Text Wizard (Text Import Wizard in version 7.0) offers greater flexibility. The wizard uses more than just the first line when determining the number of fields to create in a new table and it allows you to change the data type of the fields before they are actually imported. However, if lines far down in the text file contain more fields than the first few lines, the Import Text Wizard will not see them and the additional fields will be ignored. In this situation, use the technique described in the "Other Workarounds" section below.

MORE INFORMATION

To specify the structure that Microsoft Access should use, you can use the following steps instead of relying on the first line of data:

Create a table in Microsoft Access that has the correct number of fields and the correct data type for each field.

On the File menu, click Import.

Select Text (Delimited) from the Data Source list and click OK.

Select the text file and click OK.

In the Import Text Options dialog box, click Append To Existing Table and specify the table you created in step 1.

Click OK to import the file.

Determining the Number of Fields

Microsoft Access scans the first line of data to determine the number of fields in the text file. If successive records contain more fields, these are ignored and no error message is given. For example, suppose you have a text file that looks like the following:


   5959,"John Doe","123 Main Street"
   5960, "George Brown","55 Orange Avenue","MainTown","WA","USA"

Because the first line only contains three fields, Microsoft Access creates the following table:


   Column 1     Column 2          Column 3
   -----------------------------------------------
   5959         John Doe          123 Main Street
   5960         George Brown      55 Orange Avenue

Note that the additional fields are ignored and no error message is generated.

Determining the Data Types

Microsoft Access scans the first line to determine the data type for each field. If the data type in the field is invalid, a "Type Conversion Failure" error message is generated in the Import Errors table. For example, if the following text file is imported


   5959,"John Doe","123 Main Street",8/12/90
   5960,"George Brown","55 Orange Avenue",81290
   ABCD,"Jane Bell","78 West Street",8/12/90

Microsoft Access detects four fields with the following data types:


   Field      Data Type
   --------------------
   1          Number
   2          Text
   3          Text
   4          Date/Time

The second record fails to convert because the number 81290 in the fourth field is not a Date/Time data type, as determined by the first record value (8/12/90). The third record fails because "ABCD" in the first field is not a number. As a result, Microsoft Access reports type conversion errors in each case.

Other Workarounds

In addition to import appending the text file to an existing table, it is also possible to use a text editor to edit the first line of data to correctly reflect the number of fields and data types in the file.

If a field in the first row contains a number that is supposed to belong in a text field, type double quotation marks (" ") around the number in the first row. Microsoft Access will then correctly interpret the field as a text field.

Additional query words: import

Keywords : kb3rdparty IsmTxtd
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto