Import txt file using VBA (not DoCmd.TransferText)

[sumdumgai]

Can someone please point me to an example where using VBA, a comma delimited txt file is imported into a table? I can't use a 'canned' routine becasue I need to be able to examine each record before it is imported.

Thanks.

 

[David R]

Might it be easier to import the whole thing and then throw out the ones you don't want? Importing by VBA is going to be perilous if you have a shaky network...

 

[jdraw]

I agree with David, import to a table. Consider the table just a Holding Tank from which you build final table(s).Run queries/edit routines against table records. You can move vetted/adjusted data to other table(s) as necessary.

 

[sumdumgai]

That's a bummer. My experience is in Excel VBA and I heard so many good things about Access. Exactly what makes sense about importing garbage and then getting rid of the garbage? Sorry. Bad night. Maybe you're right.

Thanks for the input.

 

[Galaxiom]

Exactly what makes sense about importing garbage and then getting rid of the garbage?

If the file is linked to a table then it isn't ever really imported into the database as such until you insert to the permanent tables.

 

[jdraw]

If you are more comfortable cleaning the "garbage" in Excel, and getting it in pristine shape, then do so.

We are familiar with Access (and try to keep all the manipulations inside one piece of software). We'd put the"garbage" in a holding tank, and un a series of queries/functions to cleanse the data and move it into final Tables. This is a common technique in Access.

 

[Galaxiom]

Importing by VBA is going to be perilous if you have a shaky network...

My system has done this several times a day for several years. It has imported text files with many thousands of lines using FileSystemObject.OpenTextFile and never once glitched.

 

[sumdumgai]

Thanks for the input. The system was written around imports into Excel, preparing data there, and then importing clean data into Access using 'Saved Imports'. I was looking for a way, besides using 'Saved Imports within Access, to examine a record field to get its value as it was being imported.

 

[David R]

My system has done this several times a day for several years. It has imported text files with many thousands of lines using FileSystemObject.OpenTextFile and never once glitched.

Sorry, our network goes out if it rains (wish I was kidding), so I'm paranoid.

sumdumgai, use what you're comfortable with. We're just offering advice.

 

[Frothingslosh]

Assuming your csv file is broken down into multiple lines, you can build a routine that opens the source file, reads one line into an array, cycles through each element and cleans the data, appends the data to a table, and then moves to the next line to repeat the process. You can even build in checks, if you want, to make sure that the data being imported doesn't already exist in the system as a sort of fail-safe in case of network interruption. (If it's not broken into separate lines, you can use the Split function to break it into lines for processing.)

Is that what you're asking for? Because trust me, it's a LOT slower than just using TransferText to dump the data into a holding table and THEN cleaning it.

 

[namliam]

"importing garbage" and cleaning it is called "staging" a table, you can then process your data inside the database

You can either import your text file into a table, then load the table in code and distribute from there....
Or load the file in memory and chop it up in memory and work the file....

The advantage of staging the text file into a staging table is that you can do a lot of batch processing on the now "text table" using update, delete or append queries quite easily. If you want you can also manage the "text table" record by record.... The use of a staging table is very accepted way of doing things, even Big Data or huge systems like SAP use and ab-use staging tables, so why wouldnt you use it?

If you have existing code in excel to manage your text file(s) you can probably "borrow" a lot of the existing code and re-use it in the database.

It may help to see the code and a (dummy) text file

 

[sumdumgai]

Thanks. I'll give it a try (but I'll need some help). I'll load into a temp table, bounce the data there off of my main append table to make sure I'm not loading any files more than once, and then use that 'temp' table to append the new records to the main table. Where can I see an example of loading a 'temp' table? Thanks.

 

[jdraw]

You could add a field to your staging table eg ImportFileName and update that field in you table with the name or date of the import file.
That will help keep things organized, and could prevent reimports of the same data, if you add a little logic..

 

[sumdumgai]

Which is better?
1) Create a temporary database with a staging table, load the data it, clean the data, import the clean data into the main database, and then delete the temporary database, or
2) Create a staging table in the main database once, load the data into it, clean the data, import the clean data inot the main database, and then empty (not delete) the staging table?

 

[Galaxiom]

3) Link to the csv. The raw data is never actually loaded into any database.

So long as the linked table is not in use in the database at the time, the source file can be simply be substituted with another. No relinking required.

 

[gemma-the-husky]

however you do it, you can only automate tidying so far, I think.

ultimately some of the data will be down to inspection, so if its a big file its a big job.

If possible, I always like to import/use raw csv files rather than, say a spreadsheet. Hopefully, they came from another system, and are generally more reliable. you definitely get more control over the import, than you do with a spreadsheet.

 

[sumdumgai]

OK, I've linked a new table to the .txt file and created an Append query to add the linked file records in the new table to the permanent table. How do I now prevent appending the same linked txt file more than once? I tried to delete the records in the linked table but that wasn't premitted. Should I 'Kill' the .txt file after the Append?

 

[sumdumgai]

I've tried different SQL statements with no luck. Why doesn't this work? 'Source file' is the field in both tables that need to be compared. If the value of 'Source fille' in a record in table Temp cannot be found in any record in tablePerm, I want to select that record.

select * from tableTemp
where [source file] not in (select [source file] from tablePerm)

Thanks.

 

[namliam]

In "real" staging tables you just have one table "tblSomenameStaging", you would import the text file to that table which then preferably is stored in a backend DB so you can compact the BE on demand.

You NEVER make a temporary table, always have a perminant table that acts as your staging table.... By using a temporary table you lose the control you so despirately want/need.

Another "way" is to use a linked CSV, and you move the files in and out of "use" on the filesystem... Though I dont like that solution personaly since I believe you should "mess" with source files as little as possible.

If you use a "proper" staging table you run a delete query on it after the full process so you start fresh again the next file. If you use the linked file process, yes ... Assuming you created a copy and still have the sourcefile, kill it.
Or you can rename it 2 times, Original name to "Import name", run import process, rename it back to the original... The later is ill advised IMHO, in case the import process is aborted for some reason, you are left with a mess.

The "standard" way of keeping track of process files is to have a table with only ID, Filename, Processdate in it. That way you can use a simple DLookup or a simple select to check if a file has been processed rather than check a (potential) large table for multiple values.

 

[Galaxiom]

In "real" staging tables you just have one table "tblSomenameStaging", you would import the text file to that table which then preferably is stored in a backend DB so you can compact the BE on demand.

There are significant problems with a staging table in the BE. Firstly is scope. Every user gets the same table. This doesn't matter if imports are only from a single user but could be a mess if not managed in multiuser environment.

Moreover it requires the back end to be compacted much more frequently. Remember a back end cannot be compacted while in use so that inevitably means increased interruptions.

I prefer temporary data to be held in a completely separate database. It can be compacted or even thrown away if you have an easy way to make a new one. Make a copy of an empty database or construct one from sql or dao commands if you prefer to keep it in the one file. Indeed one could arguably keep the spare file in an Attachment or binary record in the database.

You NEVER make a temporary table, always have a perminant table that acts as your staging table.... By using a temporary table you lose the control you so despirately want/need.

No so sure. As you intimated and I will reinforce, the staging table should have all fields forced as Text so that no helpful Microsoft monkey business happens on the way in. This can be done in the import spec.

Another "way" is to use a linked CSV, and you move the files in and out of "use" on the filesystem... Though I dont like that solution personaly since I believe you should "mess" with source files as little as possible.

Basically I agree. However it can work when a file by the same name is regularly imported. The downside is that changes to that name so it is best stored somewhere with a pretty form to edit it.

With more varied imports it can be better to have a file selection dialog.

The "standard" way of keeping track of process files is to have a table with only ID, Filename, Processdate in it.

Definitely