Import and Match Text Files Automatically

[ashley25]

I am looking to create a macro that will import 6 text files into my database and save them as tables. The import will run once a week, with the new files copying over the previous weeks.

However, as the new files are brouight into the database, I need them to be compared the old files. If any customer IDs are missing, they need to be copied into a seperate table.

 

[pwbrown]

I wouldn't create a macro for that, just code it in a button.
Example for importing a text file:
DoCmd.TransferText acExportDelim, , "TableName", "c:\.......\TextFile.txt", True
Change the parts in red to match your info. Tablename is the name of the table the data will be imported to.
True is if the file has the same headings as the table. Change to false if not.

Are all the text files getting imported to the same table?

 

[ashley25]

Thanks for the code, I understand that part now.

In answer to your question, the 6 text files will create 6 individual tables.

The issue now is when the fresh data comes in, it needs to be matched against the old data to check whether or not any changes have occured since the last run. If any data has dropped out, then the customer IDs need to be dropped into a seperate table.

 

[pwbrown]

Ok, assuming all records have a customer ID, query the two tables checking for IDs not in the new table. It should be an append query to copy those records into that seperate table.

 

[ashley25]

There wont be two tables, as one comes in, it writes over the previous version. Before it does this it needs to check against the previous table for ommissions.

 

[pwbrown]

That's not possible, well not practical at least.
You should create a dummy table to hold the imported data, check the ID, copy the records which are new to the other table you have.
Move all the imported data to the original table and delete the dummy table or leave it?