I have a database consisting of 3 tables. I would like to import a single csv file and split it across the 3 tables. The first field of the csv file contains a letter (R, P or H) that designates which table the row of data belongs to.
The number of ‘H’ rows varies. In general I’ll have 10 R and P rows throughout the csv file, though this can vary too. I have several hundred csv files that need importing.
So, how do you do it? This is my first attempt at a database in Access (or anywhere), am I in over my head?
The Mail-Merge Wizard in Word does allow you to enter criteria for which rows you want and which you don’t, but I don’t think Access has anything comparable when importing a table. It’s either the whole table or nothing.
Alternate solution is to import the whole thing three times, and then delete the unwanted records from each of the three tables. You can do that manually on-screen, or a simple Delete Query can do it for you.
Wow. I don’t know of any way to do a “conditional” data import (that is to say IF row x stars with “R” import that row into table “R”).
The only thing I can think of would be to import all of the data into 1 table and use queries to split that table out into the 3 tables you’re looking for.
Several hundred csv files would make this a tedious process though. Perhaps you could append all of the csv files into a single large file - I’ve seen utilities which do this, I’ll see if I can find one.
Good luck!
Hopefully I’m understanding your issue correctly. Unless there are compelling reasons that you need to segregate the records during the import, I would simply import all of the records into a “temp” table and then use the append queries to move the data from the temp table to its ultimate destination. The whole process (import, run three queries, clear temp table) can be automated by constructing the appropriate macro.
Thanks for the ideas, folks. I am using three tables because the db will get quite large. Still, if having only one table will make life easier I could go that route.
You can use basic DOS commands to append many files together.
The command: type file1.txt >> file2.txt
will take the contents of file1.txt and append them into file2.txt (file2.txt will be created if it doesn’t already exist).
By putting all the files into a distinct folder and then using wildcards, you can append all files at one time:
type *.csv >> finalfile.csv
[On preview I see Bob’s question: The one table we are suggesting is only temporary and can be deleted once the data is moved out of the temp table and into its final destination.]
