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

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:

1. Read in the file in Binary mode with a fixed-length string that is the same length as the record.
2. Write each string to a new file that is open for sequential output.
3. 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

1. On the File menu, point to Get External Data, and then click Import.
2. In the Import dialog box, change Files Of Type to Text Files.
3. Select the text file that you want to import and click Import. This will start the Text Import Wizard.
4. In the Import Text Wizard, click the Advanced button.
5. 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
   						

6. Click Save As and save this specification as Test. Then, click OK.
7. Continue through the wizard by clicking Next and click Finish to import the file.

In Microsoft Access 2.0 and 1.x

1. From the File menu, choose Imp/Exp Setup.
2. Under Text Delimiter, change the Text Delimiter from " to {none}.
3. 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
   						

4. Choose OK, and save this specification as TEST.
5. From the File menu, choose Import.
6. Select Text (Fixed-Width), and then choose OK.
7. Select the text file you want to import, and then choose Import.
8. In the Import Text Options dialog box, select the TEST specification. Choose OK.