RE: data conundrum

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

"work in progress" wrote:

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



.

Relevant Pages

  • RE: data conundrum
    ... in your spens you'll want to keep field names consistent. ... the specs will be the most tedious, but once theyr're done once they're done. ... in other words, you have file1, file2, file3..... ...
    (microsoft.public.access.externaldata)
  • Re: Comparing two CSV files
    ... Matt wrote: ... Your approach processes every line of file2 ... against every line of file1. ... assumes the CSV files are not huge and you don't mind holding them both ...
    (perl.beginners)
  • Re: simple ln question
    ... that makes it so the contents of file1 are the same as of file2, and if you change the contents of 1, the other ... prints the inode number for the file foobar. ...
    (Fedora)
  • Re: Need Help
    ... eg: file1, file2, file3 etc ... into, max 5-6, chunk files and then loading these chunk files into ...
    (comp.unix.shell)
  • Re: egrep question
    ... I personally would not use grep but awk instead, especially if file1 and file2 ... If you use egrep, ... Tom Sawyer in file2. ...
    (AIX-L)