Import 20+ .csv files into one table

[kcaenj]

I wanted to know if there was a way to streamline the process of importing 20+ CSV files all formated the same into one table in access. The first file imported would be the one that set up the rest of the imports. The files are comma delimited i believe.

Any suggestions?

KC

 

[ezakovich_35]

importing multiple .csv files

is this something you are only doing once or have to do monthly? i have something similar i do monthly but it's only 9 files. i have a macro set up to import the files as tables. Since both the filenames and table names are static, i don't have to change this every month. I also have VB code that runs appends each table to the first one using a runtime append query (otherwise, you'd have to have 20 append queries too). It works and not so labor intensive. if you need help with the VB or the SQL, let me know. Not sure of your programming level.

 

[kcaenj]

Most of my programming for VB has been done in Excel... this is a one time deal with the CSV files all in one folder. I need the turn those files into one combined table in Access.

Anyway to get this done without having to import each one individually would be great!

If you need more info let me know

KC

 

[Norie]

KC

Like ezakovich has suggested you probably want to use code.

Unfortunately there is no macro recorder in Access, but what you could do is create a macro that imports one of these spreadsheets.

You could then convert that to VBA and then alter the VBA to work with all the files.

The first step here might be to import one of the files manually and using the Advanced... option to create an Import Specification.

This could then be used in the macro/code.

 

[Perry_Gill]

What I have done in the past:

Create a table that has the fields and field sizes you want. (This is important because it you use a macro or make table query, I don't know of the way to control field size.)

Create a delete query to delete all the records in your newly created table. (I know there won't be any in there yet.

Select [File], [Get External Data], and [Link Tables]. Set up a linked table for each of your 20 csv files.

Create an append query for each one of your CSV linked tables that will append to your master table.

I assume that you want this to be a repeatable process, so create a macro in the macro builder to run the delete query and run all of the append queries.