Importing CSV data

[davesmith202]

I have 3 csv files, all in slightly different formats that need importing everyday into a new Access table.

What is the easiest and fastest way to import this data? I was thinking some vba code triggered from a command button. Anyone got an idea of what code I could use?

Thanks,

Jon

 

[The_Doc_Man]

Well, Dave, I've got a couple of questions (rhetorical in nature).

First, are the files in fixed format either by columns or by delimiters?

Second, how comfortable are you with macros and modules?

If some or all of the files are fixed in format either of the ways I described, you can manually import each file ONCE and create a named import template. After that, the TransferText method of DoCmd allows you to name a template. Therefore, it is possible to do the import once to build the template and automate it the second and subsequent times.

If the files do not meet either of my fixed-format criteria, your work is a bit harder because a template won't work. You are then facing VBA parsing.

As to how to automate this, two options.

A. Assuming there is a fixed format (by column or by delimiter) for each file such that you can do a DoCmd.TransferText via input template, you can build a macro that does each transfer.

B. If you don't like macros, build one anyway - but then convert it to VBA. You can go back and customize it once it is converted.

Then, whether you leave the result as a macro or as code, create your command button on your intended form but be sure that the control wizard is enabled. One of the options of the button wizard is to run a macro. Another is to run some code. A third option is to lie to the wizard to have it open a form. Doesn't matter as long as that form exists. Then go back into the code editor, remove the OpenForm stuff, and replace it with a subroutine call to the VBA you really wanted to run.

Or cut-paste that code in place of the OpenForm stuff and run the steps in-line if that appeals to you. Six of one, half-dozen of the other.

 

[davesmith202]

I've done a lot of Access work before an am not too bad at vba programming. Prefer to do stuff in code rather than Macros - nasty!

The data will be in a fixed format each time so setting up an import template is possible. The delimiter is a comma.

Not sure if I would have to clean the csv file each time since it starts at row 5 or so if you look at it in an Excel spreadsheet.

 

[gemma-the-husky]

if you load everything with an import spec - you may be able to get rid of the spurious rows by some delete query - you should be able to find something that will enable you to select them - a blank field somewhere, probably

 

[Noel]

additional info

along the lines of the other responses.

Build your import specs by first doing a manual import (file>get external data>import) for each csv and save the spec (click advance somewhere along the manual import procedure)

then refer to the import spec in the transfertext method

 

[The_Doc_Man]

Prefer to do stuff in code rather than Macros - nasty!

Of course you would prefer code. But the real fun is remembering that macros are your FRIENDS. You can create a macro and then CONVERT IT TO VBA! So in other words, for a cheap expenditure of skull sweat, you get the skeleton of your procedure. THEN you convert it to VBA and customize the livin' hell out of it. Let Access help you do your job.