using Multiple text files (csv) to create one Single table 1

[sol1]

What is the best solution in MS Access where I have multiple text files (csv) that change on a monthly basis and I wish to place them in one table. I have been advised a UNION query may be best. To date, I have NOT used a Union query

All the text files have the same fields

Thanks

 

[blom0344]

A union query is not graphically supported by Access, but you can write it yourselves using the SQL window. However, such a query would have another query or tables as a source, not a csv file (AFAIK) I suppose using a linked table approach using the .csv file as an outside table will work.
It is then a matter of writing the UNION SQL over the linked sources. If you need help on the syntax let us know....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[sol1]

I appreciate your response.
This is by first attempt at placing a question on the forum.

My specific application is

(1) I receive a number of text files with the same fields at a "fixed" width
(2) The files will be updated on a monthly basis
(3) The previous data is to be overwritten with the new data

It was suggested I use a Union Query to create one table
I have not used Unions previously, however,the comments appeared feasible.

My previous experience was to import the files using a Link (to the text files)to create the tables
I would then create one table from the number of "linked" files.
This was achieved by having one table, deleting the records and then appending the files independently

Once I had the table, I can achieve my objectives

This appeared complex, however, it was well within my MS Access ability. I have no problems using a relational database and writing quries, forms or reports

Perhaps you could comment on my methodology, and, where possible suggest why I should use one method over the other.

Once I have my "front end" achieved, the rest is all menu driven as an application.
It is a matter of what process do I achieve one table from the number of text files I receive on a monthly basis

Once again, I appreciate your feedback

Regards

 

[blom0344]

Answer send directly to sol1, if you value this, sol please post my answer here. Forgot to keep a copy............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[sol1]

Presume this is where I post it ??

Hello Kevin,

I would try the following:

1. Do not import the files, but link them to the database (file --> get
external data --> link tables, files of type : Text files)
2 Create a union query over the different files through SQL:
SELECT * FROM FILE1
UNION
SELECT * FROM FILE2
UNION
SELECT * FROM FILE3
............................
3. Create a make-table query that writes to a table taking the union-query
as a source.
4. Create a macro with the following steps:

- Drop the existing table
- Run union query
- Run make-table query

This should be more efficient than doing append action one for one, since
the union query will do that for you.

Remenber, the union query can not be graphically displayed in the query
wizard, but it is supported. You have to write the SQL yourselves..........


yours truly,

Ties Blom
Information Analist Shimano Europe
0031-(0)341272329

 

[entaroadun]

UNION will automatically de-dupe records in your files. If there are duplicate records and you want to keep them, use UNION ALL instead. This runs faster as well.