automated import of text file errors

[Richardson]

I have run across a situation that I am not very familiar with, so I am
hoping someone has a suggestion for this problem.

I have a folder containing many .csv files that I need to import into one
table, tblResults, in an existing database. These files are all semicolon
delimited. I am able to manually use the import wizard and bring in the
data.
However, I need to turn this program over to some individuals that need the
process automated.
In macros, I found that I can use the Transfer Text command to automate the
process.
I have two problems though. First, since it is semicolon delimited, Access
rejects the data and doesn't recognize the semicolon as a delimiter, so the
field names are all run together.
Second, I would like a macro or code that will not only import the data from
one file, but will look for all files in a given folder and import each file
into this table.
Also, I am running Office XP Developer on a Windows XP Pro machine.


I am grateful for any assistance you can provide.



Lori

 

[John Nurick]

Hi Lori,

On a point of terminology: CSV stands for "comma separated values", so
if your fields are separated by semicolons they are by definition not
CSV files<g>.

You can't do what you want in a macro, but the code below is a skeleton
VBA procedure from Joe Fallon for importing all the files in a folder.

Import one of the files manually, and as you do so, click the
Advanced... button in the text import wizard. This will let you save an
import specification with the semicolon separator and any other
customisation you need. Then in the code, substitute the name of your
import spec for the ImportSpecName in Joe's code.



Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", _
"AccessTableName", "c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir()
Loop
End Sub