ACC: Unable to Import Portion of Fixed-Width Text Files

ID: Q94417

The information in this article applies to:

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

SYMPTOMS

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

You are unable import any portion of a fixed-width text file.

CAUSE

Each record in a fixed-width text file must be separated from the next by a carriage return or line feed, and all the records must contain the same number of characters. The text file you are trying to import does not meet these criteria.

RESOLUTION

Modify the text file to ensure that all records meet these requirements:

Each record is on a different line.

Each record contains the same number of characters.

STATUS

This behavior is by design.

MORE INFORMATION

Microsoft Access cannot import fixed-width text records that are not delimited by a carriage return or line feed.

Mainframe users may be familiar with long strings of data where the first x number of characters are the first record, the next x number of characters are the second record, and so on. In order to import these records into Microsoft Access, you must ensure that each record is on its own line with a carriage return or line feed at the end of each record.

For example, consider the following string of characters:


   lastnamefirstnameagelastnamefirstnameagelastnamefirstnameage

You cannot import these records into Microsoft Access without modification. If you try to import this string as a fixed-width text file, you will either create an empty table, or you will be unsuccessful and may need to repair your database.

To modify the file using code, follow these steps:

Read in the file in Binary mode with a fixed-length string that is the same length as the record.

Write each string to a new file that is open for sequential output.

Repeat steps 1 and 2 until you reach the end of file.

Microsoft Access can import the following records:


   lastnamefirstnameage
   lastnamefirstnameage
   lastnamefirstnameage

To import a file containing records like these, follow these steps:

In Microsoft Access 97 and 7.0

On the File menu, point to Get External Data, and then click Import.

In the Import dialog box, change Files Of Type to Text Files.

Select the text file that you want to import and click Import. This will start the Text Import Wizard.

In the Import Text Wizard, click the Advanced button.

Enter the following settings for Field Information:


      Field Name   Data Type   Start   Width
      --------------------------------------
      Last         Text        1       8
      First        Text        9       9
      Age          Number      18      3

Click Save As and save this specification as Test. Then, click OK.

Continue through the wizard by clicking Next and click Finish to import the file.

In Microsoft Access 2.0 and 1.x

From the File menu, choose Imp/Exp Setup.

Under Text Delimiter, change the Text Delimiter from " to {none}.

Enter the following settings for Field Information:


      Field Name   Data Type   Start   Width
      --------------------------------------
      Last         Text        1       8
      First        Text        9       9
      Age          Number      18      3

Choose OK, and save this specification as TEST.

From the File menu, choose Import.

Select Text (Fixed-Width), and then choose OK.

Select the text file you want to import, and then choose Import.

In the Import Text Options dialog box, select the TEST specification. Choose OK.

REFERENCES

For more information about importing data, search the Help Index for "importing data," or ask the Microsoft Access 97 Office Assistant.

Keywords : kb3rdparty IsmTxtfx
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb