Help importing data from .csv to multiple tables!

[phagan]

I am trying to create a button or a simple form for a few research assistants to quickly import survey data (.csv) into an Access DB.

I’ve figured out how to import the csv file as far as a table, but I need to then copy the data into multiple (like…25) tables throughout the database.

So far all I can think of would be to create 25 separate append queries for each destination table and then set up a macro to run all of them onclick. Is that even possible?

I’m completely in over my head here, any tips?



Thanks a lot

[June7]

Why duplicating data?

Yes, would require multiple INSERT sql actions

[bigroo]

Hi June7,

Can you describe more on this?
I had the same issue to solve last night. All I did was create 3 append queries to populate my 3 tables in the correct order from my text file import!!

If there is another easier way to do this I am all ears?

Thanks

[phagan]

Thanks for the help!

I definitely want to avoid duplicating data, if possible. It was my understanding that I could only import a csv file into one "dummy" table, then I would have to insert them into their original tables from there. Is that true?

What would multiple insert functions look like? Could running so many functions at once cause database issues?

[June7]

I don't routinely do csv imports, just somewhat familiar with the process. The import wizard does allow an Append option for text file import. Whether or not the import will go 'cleanly' depends on the source file.

Code can programmatically accomplish imports and/or SQL actions. You would do 25 imports or 1 import to temp table and then 25 INSERT SELECT sql actions. Again, why duplicating data?

The SQL actions can be saved Access objects and then code (macro or VBA) can execute them from button Click event. I use only VBA so I would probably code the sql action entirely in VBA. Would include line like:

CurrentDb.Execute "INSERT INTO tablename SELECT ..."