As a learning exercise, I am trying to map several data files into a few master tables. Right now, I have a file hierarchy as follows:
<Root> - Client 1 - CustomerInfo.csv - OrderInfo.csv - Client 2 - CustomerInfo.csv - OrderInfo.csv etc...
What I am attempting to do is to put all of this information into an Access database so that I can link up my primary keys, but I am having two issues.
First, I don't know how to interface between the CSV file and the database to import the information. Second, the CSV files contain field names in alphabetical order in the first row, but only columns with data in them were extracted.
So, I am trying to create a master table called "CustomerInfo" with all of the data from the CustomerInfo.csv files spanning all folders. I can't do this via an import spec, because not all the files contain the same field names and they aren't always in the same order.
I figure the easiest approach is to create a VBA script to iterate through all folders, reference the name of the file, and append the data from that file into a table of the same name, then dynamically create SQL insert statements based on the field names in the first row.
Does this sound like the correct approach, or is there an easier way to do this with possibly less overhead?
I've scoured the net for information about how to do such a thing, but nothing seems to address this problem. I'm assuming that this is because Access probably isn't very practical in the real world. So, if anyone could point me in the direction of a good resource that will teach me about this kind of stuff, I would appreciate that as well.
Thanks for reading!