Import text file (fixed width)

[Guest]

I am trying to write a procedure to import a text file with fixed widths. I
have the starting position and number of characters for each field.
Also, I intend to update the table monthly, so I need the new table to
overwirte the existing one.

Thanks.

 

[Allen Browne]

Assuming that it is only the data that changes each month (not the field
widths), you can create a import specification, and then reuse it each
month.

1. Begin the import process manually, i.e. File | Import, and select the
file so the Text Import Wizard begins.

2. Click the Advanced button (lower left of the wizard dialog).
Another dialog opens. Define the columns and their widths.

3. Click the Save As button, and name this specification.
You can now cancel your way out of the dialogs.

Your monthly import code can now clear out the old table, and use the
specification to import the new:
dbEngine(0)(0).Execute "DELETE FROM Table1;", dbFailOnError
DoCmd.TransferText acImportDelim, "MySpec", "Table1", "C:\MyFile.txt"