Merging Files

[Browser]

We are doing a study for the SOA and we have recieved some data from one of the participants but the data is comprised of 3,000 sepearate daily files. Is there a way to merge all of these daily files into one file in an Access database?

[Jack]

If all the files are the same format, why not write a simple DOS macro to merge them?

[Minerva]

First - make sure that all of the daily files are in the same format (column order, names, etc.)

Import the first one into Access.

Import the rest and append them to the first table.

If there is additional data you might need to add (e.g., year or group), import them into a "temporary" or holding table, and then append them to your master table using an append query with variable data [for the field "Year", in the heading, type Year:[inputyear] - when you run the append query, you'll be asked to input the year at the time you run, and it will automatically use that year in every record for that run.

This is still a pain (3000 iterations!), but it works. When I've done this type of thing, I've usually used the intermediate table approach, and checked it out periodically to make sure I haven't skipped a week or brought one file in twice (the type of check will, of course depend on the nature of the data).

Depending on the format of the original files, the import wizard can do a couple of cool things - like setting up a generic format so you don't have to go through column by column.

[GadgetGeek]

If all files are in same format, it should be relatively easy to write a short module to read them in iteratively using the TransferText method. There is an example in Access help.

[Browser]

Thanks for all the help, I will try some of these suggestions and see if I can get them to work.

[Gates Is Antichrist]

I agree to use DOS to cut down the work on the front end, if practical. In particular, a single powerful concatenating COPY can be used if the filenames and formats permit.

BUT if you do this there is (or was, if not currently changed in DOS) a "nifty" and vicious gotcha:

COPY F*.* myfile seems to niftily copy your 100 female files to the one file myfile. But you would get a different result from COPY /B F*.* myfile if there were any Control-Z's anywhere. (COPY of wildcards to concatenate assumes[or assumed] /A, which treats Ctl-Z as EOF).

It's not a bug, it's a feature. I paid dearly before I found that part out!

[Cho Da]

~/>cat data* > combinedFile