Automating Mass Imports

[Guest]

I am using Access for a personal project, and have run into a bugger of a
roadblock. I must assume I need some code to pull this off, and that's the
biggest hole in my Access knowledge. It may also be entirely impossible, but
that will depress me horribly...

OK. I have 84 unique .csv files (comma separated values) that I need to
bring into Access as 84 separate tables. These 84 .csv's will periodically be
replaced with new ones...same structure, but new data. (these .csv files will
NOT be updated, but OVERWRITTEN, although using the same filenames and 1st
row headings)

I can manually initiate the mass import each time it is necessary, I just
need to instruct Access to, upon request: Look in a specific folder and then
import all the files within. (Or all having filenames found in a field
somewhere, I can make a list)

It would be ideal if no further interaction is necessary during the import.

Any ideas? Surely this is possible...

 

[Guest]

There are a couple of ways you can do this. The simplest to implement if you
have no VBA experience would be to use macros. The problem with this it
would take more interaction to run. The other would be to use VBA to write a
routine that would import all the .csv files in a specific directory.

The first question I would have is will the .csv file names always be the
same? Also, when you do the import of new data, will it replace the data in
Access or will it add to it?

If file names change, then the Macro approach may not be that useable. It
the files are alway the same name in the same directory, then using Macros
will work okay.

Let me know, and we will get this going.

 

[Guest]

An admirably prompt and confident reply! I can feel my data getting closer...

The .csv files will have identical names each time, and ADD to the database.
Actually, the trailing two characters of the filenames may change
periodically, but I'll worry about that later, these csv's are pilin' up.

Thanks a bunch.

 

[Guest]

Okay, let's go the fast way first with Macros. It is possible to put all the
imports in one Macro. The limit of the number of actions in a macro is 999.

Use the TransferText action for each .csv file you want to import. You can
get the details on it in Help. One thing you may want to do is create an
Import specification for each file structure you have. That is, if more than
one file has the same field names and data types, you wont have to do all 84.
What this will do for you is ensure you get the correct data types and
names, etc. You do that by going through the import once manually. When you
get to the import wizard, click on Advanced. you can then specify how you
want the data to look. Once you have it set up, click on Save As. You then
give it a name and use that name in the File Spec argument of the TranferText.

So basically, you just create a macro that imports the .csv files using the
TransferText action. One action for each file. If a file name changes, you
will have to edit the Macro to change the file name argument.

 

[Guest]

Klatuu... I'm hoping you are still around to answer a follow up to this
post....

I have a similar problem to Rob's... multiple csv's downloaded from the
mainframe each week... structure and file names stay the same each week, but
the data is new. In my case, the new data needs to replace the old data, not
append to it.

I know some VBScript, so I think I would rather handle this that way so that
I'll have more control than a macro. Can you give me a starting point for how
to script this out?

Thanks!

Scott

 

[Guest]

Sure. One thing I don't know is whether your .csv files are all in the same
directory. For example purposes, I will assume they are. What you can do is
use the Dir function to loop through all the files and import each one. If
you are going to replace the existing data, all you need to do is delete the
existing data before you import. Be aware this is only untested "air code",
so it may take some tweaking and debugging. Also, hopefully, the table names
you will use are the same as the file names you are importing. If that is
not the case, it will be a little more complicated. In this example, I will
use the file name as the table name.

Dim strPath as String
Dim strFileName as String
Dim strTableName as String
Dim dbf As Database

Set dbf = CurrentDb
' Set the path to the directory where the files will be.
strPath = "c:\Downloads\"
strFileName = Dir(strPath & ".csv") ' Retrieve the first entry.
Do While strFileName <> "" ' Start the loop.
strTableName = Left(strFileName, Instr(strFileName, ".csv")-1)
dbf.Execute("DELETE * FROM " & strTableName & ";"), dbFailOnError
DoCmd.TransferText acImport, ,strTableName, strPath & strFileName, True
strFileName = Dir ' Get next entry.
Loop

Now, the only other issue you may have would be to define Specification
Names for the files. If you need them, you can set them up by going throught
the import manually. When you get to the import dialog, click on Advanced.
You can define the data types and names for fields and other import
parameters. Once you have completed that, click on Save As and give it a
name. This is the name you would use in the TransferText method. Again, if
you can use the name that is the same as the table name, it will simplify the
import process above.

If your table names have to be different, let me know and I can show you how
to set up a table you can use to translate the file names into table and
specification names.

 

[Guest]

Klatuu,

Thanks very much! This is a great start and I appreciate your suggestion.
I'll give it a try right now.

Scott

 

[Guest]

Klatuu


I have a similiar problem like Scott - multiple .csv files (mass importation
the most viable solution).

I tried out you import code - and it works.

In your response to Scott you stated that if the file neames were different
that you had a method for creating a mapping table with the table names and
associated specification(s).

Could you please show me this method as I am in urgent need of it.

Thanks in advance.

Regards

sysAccountant

 

[Guest]

Dear Klatuu,
I am also in same like the others. but the difference is my file names are
incremental. and i need to append the data in the access table hourly
autometic. can you give me any sample script

 

[Guest]

I'm doing something similar, but with conditional import.

INSERT INTO table1
SELECT *
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].xxx.csv
WHERE ([field1]="London");

I'm not to good with VBA. I'm trying to tweak the vba to work with the above.

 

[sharon_hutchison]

I have a related question, I have a macro with 500+ actions, the
actions export the results of different queries into the same folder
but each as a separate spreadsheet. I now want to relocate where all
of these spreadsheets are located. I thought it would be as simple as
a find/replace but that doesn't seem to work.....I'm really keen to
avoid updating the Action Arguments of all 500+ actions
individually....is there a way to do this en masse without converting
the macro to VBA?

Thanks in advance.