Importing a fixed Text file using VBA

[spectrolab]

Hi Guys,

I've had a bit of an issue with my DB, lost all of the records in a table (about 100000 records gone). Luckily, I have all the data in text files (.SIF files, standard interchange format). SInce they were generated by the DB, I though it would have been easy to re-import them, but I am a bit stumped, since access does not recognise them as text files

I have attached an example of the file as a guide, the header information is not really important (the first line of data is line 8).

Is there any way I can grab this data back easily from these files?

Is there a way to set-up a VBA script to automatically grab this data from every file in the folder?

Thanks for any help you might be able to give..

Forgot to mention, the format does not change between files, only the number of lines varies.

Forgot to mention, the format of the file does not change at all, the layout stays exactly the same. Just the number of lines changes.

 

[gemma-the-husky]

import the file manually


something like

files, external data, import, text

(changed in different access versions)

access will let you split the file into fields to suit
access will add field breaks on spaces, but you can change these to suit
(the same as when you import a fixed width text file into excel)

you can then click the ADVANCED tab, and sabe this import spec.

NOW, you can use

docmd.transfertext - applying the saved transformation
to import future tables.

 

[spectrolab]

Thanks a lot for the reply Dave. I can import it manually fairly easily, but the files are .SIF files, not .TXT files, so access does not recognise them. I guess I will have to change them all to .TXT (bit of a pain, over a thousand of them).

Is there no way to extract the data directly from the .SIF file?

To originally get the data into the database we use a VBA script which changes the original .QAN file to .TXT and extracts the data, then deletes the file, but this isn't an option in this case as we still need the .SIF file. Our customers extract the data directly from the .SIF file (using software we don't have, I believe).

Would the 'LOAD DATA INFILE' command in mySQL be an option? The backend tables are all stored in mySQL ISAM.