Importing very large text file

[vbaInet]

I'm aware of your Split() problem, and that's not an option.

Besides indexes, what's the whole idea of copying from the raw file into a local table? Isn't it so that you can match/find missing/new records and insert those into your pre-existing table? Are you also needing to update existing records?

An import spec is XML.

 

[sumdumgai]

The local table, once it's loaded with all of the raw data (remember I decided to skip filtering out unwanted specialty codes), it will not be updated. It will only be used by users to query the data and produce reports, sometimes exporting query results to Excel.

BTW, I did find an example of creating an import spec using VBA in an Access module, and not using XML.

 

[vbaInet]

Right, that makes sense. How often will this process happen? At will by any user, daily/weekly... by one user?

The spec itself is xml based, as with most Office apps these days. If you're doing it through the system tables so be careful not to ruin any of the other specs.

 

[sumdumgai]

Most imports happen monthly, some yearly. There are also new files that come in occasionally. But any change to file layouts, or anything else that requires a table redesign always requires some amount of work, either programming or manual manipulation within Access.
I'm stepping through the code that creates the linked table by using the system tables so that I completely understand it.

 

[vbaInet]

I'm sure that you've tried to standardise the format, but in most cases these things are out of your control.

If you haven't already, open the system table to view it as well.

 

[namliam]

Instead of straight importing the file, you can also LINK the text file as a linked table.
This will allow you to detect the new records/changes and import/process only those without the need to have them in your database.

 

[vbaInet]

Instead of straight importing the file, you can also LINK the text file as a linked table.
This will allow you to detect the new records/changes and import/process only those without the need to have them in your database.

It was proposed multiple times throughout this thread. sumdumgai also justified why he needs the data to be local.

 

[namliam]

Understand he needs it local, but if you want to update the local table...

 

[vbaInet]

That's all been covered in the thread, have a read. Not unless sumdumgai wishes to recap.