Import txt file using VBA (not DoCmd.TransferText)

[nanscombe]

For a reporting application I had a separate database on the local drive as a repository for any "temporary" results tables.

I used a piece of code to check its date when the application started and, if it was not created today or didn't exist, I'd create a fresh one.

If it's binned at the beginning of each day its size shouldn't become too much of an issue.

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

If the success, or failure, of the import can be ascertained that could be added to the table as well.

 

[namliam]

I prefer temporary data to be held in a completely separate database.

Agreed, seperate backend or even a database copied to your local C:\Temp folder or something.... Though in the (too) many processes like this, I have found none where multiple people world be "driving" a central Access DB and importing data at the same time...
Another way about it is to have a "working staging backend" and a "empty Staging backend", instead of a "painfull" compact, simply kill and replace the bloated "working staging backend" with the "empty staging backend". So many different roads that lead to the same solution

I regularly have different backends for staging or "simply" have the frontend act as a staging with Compact on close...
Yes I know the potential issue with the Compact on close

Edit:

If the success, or failure, of the import can be ascertained that could be added to the table as well.

As well as many other things, like how many records (of which type) are contained. If there is money involved, how much euro/pound/dollar, if the import process is lengthy, start and end time and some other simular information pertaining/paricular to the file.

Key element being you shouldnt store the filename in the record like the OP was suggesting/doing.

 

[gemma-the-husky]

back to the OP.

You need your data tables to have suitable (unique) keys to prevent duplication of entries, so it won't matter if you inadvertently use the same data again.

Basically you have a "real" table with your data in it. load (or link) the new data into your staging table (or similar)

now you need to add new items from the staging table to the real table, and maybe update changes to existing items. I use two queries, but someone demonstrated a join that would append/update in one process.

 

[nanscombe]

That might have been me with an Update query that uses an Outer join to either add records where a datestamp on the record was non existent or update if it were earlier.

 

[sumdumgai]

Wow, that's a lot of information for a guy who Excel's and doesn't Access. First, if I create a staging table, whether it is in the main database or in another database, and link the 'C:\...fname.txt' to it, I cannot update the items in the staging table. I got an error (something about ISAM). I figured that might make sense because I would be expecting Access to update the .txt file with an update query. Second, it would be okay if I could select only certain records (based on filenames embedded in the .txt record not found in the filenames embedded in the main database table records). But I can't seem to come up with a query that works, and works without taking all day.

Is my first point a fact of life or am I doing some wrong regarding linking?
About my second point, can someone helo me with an SQL statement that searches the main table and only appends records in the staging table that don't match on a file name field?

Or, should I just import the records (not link) into a staging table and edit there before appending?

Thanks to everyone for helping and being patient with me.

 

[namliam]

It will help if you post what you have, if need by dummy up a text file and if possible whatever you have done in access....

As I explained earlier checking your staging table against your main table is a BAD idea.... Mainly because this tends to result in SLOW processing, in particular if you are processing more and more records.
Double back and read one of my previous posts.

I hardly ever link files, so I cant help you really with updating acros a link... If you use a staging table you are free to update/delete/etc whatever you like for sure...

 

[nanscombe]

A file with the headings and a couple of lines of dummy data would at least let us know what you are starting with.

Even an empty file, with just the headings, and an explanation of the data types would help, from there we could construct some data.

 

[sumdumgai]

Without sending the entire system, which is lterally hundreds of lines of VBA, and the database, (which I am not allowed to do), I'm not sure what I can send that would be more helpful than what I have already explained. I've attached a small .csv file that is similar to what I am importing (exept that the imported file is comma delimted text). This file is exactly the same, as far as field layout, and the database table. By the time this file is ready for Access to import via the Saved Import routine, the data is formatted correctly and meets all other integrity checks. Notice in the file that there is a field called 'Source File'. This is the name of the SFTP'd .txt file from which the record comes. Next to this field is a field called 'TPF', or transactions per file. This means that in the SFTP'd file, there are 'TPF' number of records (lines of text). These two fields are imported with the record into the database.

What started this post was a question from me on how to bypass a canned VBA routine like Do.cmd and use pure VBA so that I could examine each record as it was being imported to check the 'Source File' field to see if this 'Source File' had already been imported. My problem is that I do not want to import the same 'Source File' more than once. Due to glitches, interruptions in processing, user error, etc. it has happened before where a file was imported twice, resulting in duplicate transaction in the database.

Now, because there are multiple records per Source File, I realize that the staging table is the way to go. Just prior to Access import is the last and best opportunity to prevent duplicate imports. I just can't get the staging table to work, or work efficiently.

The following SQL would be okay if the tempTable only had non-duplicate records:

INSERT INTO permTable
SELECT tempTable.*
FROM tempTable;

But I am looking for a way to remove duplicate records from the tempTable before doing the append.

Attached is a sample input file. Extension is .txt but it is really .csv as upload would not allow csv type. Again notice there are many records in many txt files being imported.

 

[namliam]

Take a look at the attached database, sure it aint perfect.... I threw it together in 2 minutes or less.... but it should give you the general idea.

On top of this you can then check that the files mentioned in the staging table actually match the numbers in the file.... with a query along the lines of:

SELECT tblCSVStaging.[Source File]
, Min(tblCSVStaging.TPF) AS MinofTPF
, Max(tblCSVStaging.TPF) AS MaxofTPF
, Count(tblCSVStaging.TPF) AS CountofTPF
FROM tblCSVStaging
GROUP BY tblCSVStaging.[Source File];

Which you then need to check in VBA that MinofTPF = MaxofTPF = CountofTPF

 

[sumdumgai]

So let me see if I understand this. You've created another table that keeps only the names of the files that have already been imported and the date they were imported. Purpose of this table is to provide a smaller search table when new files are being imported. The import process is then: 1) Import the data into the staging table, 2) using the new table, purge records from the staging table where the file name can be found in the new table, 3) update the new table with the remaining file names that are about to be appended, 4) append the staging table data to the permanent table, 5) empty the staging table. That right?

Sounds great. Thank you very much.

 

[Galaxiom]

Agreed, seperate backend or even a database copied to your local C:\Temp folder or something....

A more suitable location for a temporary database or anything else temporary that is associated with the application is the users' AppData folder.

This location has the appropriate permissions.

 

[namliam]

A more suitable location for a temporary database or anything else temporary that is associated with the application is the users' AppData folder.

Which would be the "or something" part I mentioned

You've created another table that keeps only the names of the files that have already been imported and the date they were imported.

Yes as I explained in post #19, that is the accepted way of doing things "properly"

And Yes, the steps 1 thru 5 are correct furthermore between the steps you can insert any type of validation or mutation, filtering, splitting, etc that you can think of before you send it to your perminant table.