data conundrum

[work in progress]

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!!

 

[Guest]

Are your 100 files consistent?

in other words, you have file1, file2, file3.....

let's say file1 looks like this:

column1: fieldA - date field
column2: fieldB - text field

and let's say file2 looks like this:

column1: fieldB - text field
column2: fieldA - date field

Now you have your master table that for convenience sake is structured like
file1. That is an easy append because everything lines up. However, for
file two you will have an issue.

If your 100 files will be consistent (that is file1 will always be the same
layout and file2 will always be the same layout - even if they are not the
same layout), then you will probably have to create import specs for all 100
of your files. That is the long, boring, tedious part. From there you can
create a loop to import and append everything into your main table.

I am guessing that you are manually importing the Excel file (that you
create from the .csv files) as opposed to using a form, based on the your vba
comment. Also, for the import specs, you only have to create the spec for
the data you want. So if a file has 40 columns, but you only need 4, only
create the import for those four fields. That will save time.

If the above is a poissibility and you are willing to create the import
specs, I can easily show you how to loop through everything and add to your
table. At that point, this task will be automated.

Roger

 

[Guest]

Also, in your spens you'll want to keep field names consistent. So if in
file1 and file2 you have a field name strat_date, regardless of where that
column is in file you'll want to keep the field name consistent. That will
make for the easiest loop appending the data into your master table. Again,
the specs will be the most tedious, but once theyr're done once they're done.
This method would be a lot easier than manually moving columns around in
Excel for 100 files.