data conundrum

[kathleen.leonard]

I had about 100 csv files to import into 1 table in my access data
base. I was able to merge the csv files into one table first and then
import the large file into MS access. Upon finishing the import, I
noticed one thing about the data; the number of columns in each of my
csv files is inconsistent, so the data does not line up properly.
Column 9 in one table correlates to column 18 in another table. As a
result, the mega table is mostly useless gibberish.


There is no consistency within the 100 initial csv files. The data is
just feeds from various systems. Is there a way to sort this
efficiently? I need each field to align with the proper values. How
do I tell access to find the correct value for a given field when that
value could be in one of 40 or so columns?

If anyone has a clue to help solve this data puzzle I would appreciate
the tips. I am not very strong in VBA so ideally I need a viable work
around.

Thank you!!

 

[John Nurick]

Hi Kathleen,

You'll need to import the CSV files individually (or in groups if
you're lucky).

First, assess the data in the files. Does each file have a first row
with field names? (If not, how do you know which column in the file
contains what information?)

Are the field names consistent across all the files, or do they vary
(e.g. LastName in one file, LNAME in another and Surname in a third)?
If possible, standardise them.

Next create a table in Access that contains the fields you need, each
with the correct data type.

Then import the data file by file. If you have been able to
standardise the field names in the CSV files, the CSV import routine
in DoCmd.TransferText will pick them up and assign the data to the
correct fields in your Access table. Otherwise you'll need to use the
text import wizard and click the Advanced button to specify which
field goes with which.

If you have multiple CSV files with the same structure (same fields in
same order) you can concatenate them into a single file for quicker
importing - but only if they have the same structure.