Importing huge text files

[Helge]

I have several huge semicolon delimited text files that I import to tables, one table per file. The files are of size 200 - 300 kB and have more than 1,000,000 lines each. My problem is that it takes too much time to do the import. I am importing by using DoCmd.TransferText. (I am aware of my potential problem of max. database size of 2 GB.)

The tables have primary keys, have indexes, and have relations to other tables. I have seen that things are speeded up considerably if I remove keys, indexes and relations - but I do not like to do this.

I have read somewhere that things would be faster if I used transactions. BeginTrans before importing and CommitTrans after importing (because then indexing etc. is not done per record, but when everything is imported). But BeginTrans/CommitTrans does not function in connection with DoCmd.TransferText - as far as I can see.

How can I speed things up? Right now it takes hours to import data!

 

[Rabbie]

Have you checked out the possibilty of linking to the files you wish to import as Linked Tables and then using SQL to move the data into a local Access table if that is what you need.

 

[Helge]

I am familiar with Linked Tables when linking to another Access database, but how to link to a semicolon delimited text file (with no header row for field names).

 

[Rabbie]

No problem. When you enter the table linker screen change the file type to Text files then navigate to the file you want to link. Then continue as normal You will be asked to confirm that the separator is semicolon) and then you will be asked to name each column. Just try it and see. Good Luck

 

[Helge]

I have followed you method. The linking is fast, but the subsequent Append Query to move data from the linked table to my Access table takes just as much time as my original DoCmd.TransferText in VBA. So I am still looking for something that can considerably speed up things. But thank you anyway. Do you have other suggestions using VBA? ADO/DAO/Jet?

 

[Rabbie]

There is always the option to remove the indexes before moving the data and to re-add them afterwards. How many idexes are there on this table and are they needed? I believe it is only the indexes that are causing the delay. Keys and relations shouldn't have that effect on adding records. It is updating the indexes that takes the time.