Importing (and Formatting) Text File to Access

[Eric Ramey]

I have been reading through the other posts, and have yet
to find something that may address my problem. I am trying
to import a text file to Access, then once imported I
would like to run a Macro to change the data types in the
table. As part of the script, I would like to allow the
user who is importing this file to select the file
manually through an 'Open File'menu, as the file name
changes on a weekly basis. I am not familiar with VB code,
and I have a feeling this can only be done through VB.In
any case, the text file fields are as follows:

F1 - Location Code
F2 - Date (this only lists the day and month i.e. 03/31)
F3 - Amount 1 (this field should be skipped upon import)
F4 - Amount 2
F5 - Amount 3

The text file is not delimited, but it has a uniform fixed
width every time at the following intervals:

F1 - 4
F2 - 11
F3 - 29
F4 - 29
F5 - 46
F6 - 113

This will need to be imported to a table
titled 'PDIRawData1', as it is part of additional Macros.

Once this file is imported, it will need to be formatted
as follows:

F1 - Number, Single
F2 - Date, Short Date
F4 - Number, Single

I have tried formatting the file upon import, but this
causes most of my data to be lost. If I import the file
seperately, then manually update the format, there are no
problems. I would really like to automate this process
however.

Any help would be greatly appreciated!!!!

Thanks,
Eric

 

[Alex Ivanov]

You need to create an "Import Specification".
Click File/GetExternal Data, then select a sample file you want to import;
Import Text Wizard will appear.
Click Advanced tab and specify field names as you wish, data types, start
position and length of every field, etc.
Then click Save As and give the specification some meaningful name.
Next time you import another file having the same format you can click specs
button and select the specification you want to use.

From VB you can also use this specification, for example
DoCmd.TransferText acExportFixed, "test import specification", "xxx_test",
"C:\test.txt"


HTH