Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
If Access is not "practical in the real world", how would you go about this with say, SQL Server? If xx contains all possible fields (columns) and yy contains a subset of those columns, INSERT INTO xx SELECT * FROM yy will work. You can transfer each customer.csv (TransferText) to a file of the same name (yy), deleting between transfers, and run the same append query. –  Remou May 11 '12 at 9:36
    
The problem is the FROM statement, where is it loading from? I have my accdb file, and I have my CSV files, but I cannot get them to communicate with each other. Are you suggesting to write a macro to TransferText into a temp DB and append the data to an existing table then delete the temp table? What if I need one of my fields to be a "Memo" field instead of the default "Text" field? –  Addikt May 11 '12 at 11:35
    
You can link csv/txt to MS Access (Transfertext ) and run queries with that. I do not see how a memo field would work in that it implies carriage returns, normally used for row delimiters. If that is not the case, then it may not be a problem. I have just tested both a link and an import for a file with a field comprising more than 800 characters (Access 2010) and it worked well. –  Remou May 11 '12 at 12:03
    
I'll try it that way, but the idea is to get this into the database programmatically. Doing it manually would be too exhaustive if I were to be working with 1200 files. –  Addikt May 11 '12 at 21:17
    
Look up DoCmd.Transfertext, you can run it via VBA. Your post seemed to suggest you were familiar with VBA. All my suggestions refer to running with VBA. Not even a single manual action included. –  Remou May 11 '12 at 21:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.