IMPORTING CSV FILES

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have approximately 600 .csv files in a local folder and I need to get them
all into one table in access. Manually pulling them in one by one is fun for
about five minutes, but I'd like a way of automatically pulling them all in.
I know a little bit about vba (EXCEL), or even how to go about using it, but
from what I've read elsewhere using vba is one way of solving the problem.

Would someone be able to start me off in the right direction ? My access
knowledge is somewhat limited, I can bang a form together and run basic
queries, but that's about as far as it goes.

All the csv files contain identical table rows, if that helps!?

cheers

P.S Access 97
 
If you need more info for this, look in Access Help for the FileSearch object.

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents" ' Folder where your files are
.SearchSubFolders = True
.FileName = "*.csv"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Docmd.TransferSpreadsheet......
Next i
Else
MsgBox "There were no files found."
End If
End With
 
Could you please help with the docmd part of this macro.
I thought about the do.cmd part but not sure how to complete this task.

thansk for your help
 
I have added the TransferSpreadsheet code below. There are a couple of
things you need to be aware of. First, Look up the TransferSpreadsheet
method to see what values you will need for you arguments. For example,
where you see True, that means the first row is the field names for the
table. If your csv files do not have field names as the first row, change
that to False.

Next, and this is the tricky part, assigning table names to the files you
are importing. You will have to determine how to derive the table names.
 
thanks could you tell me abit more about the Mytable part. What I have is a
table which I have called AllAreasData. Would i put that in place of My table?

I thought tranfertext was used for csv file is there a difference?

THANK YOU SO MUCH FOR YOU HELP I REALLY APPRECIATE IT
 
when i tried the code it comes up with the following error
Run-time error '2495' - The action or method requires a table name argument.

it is then highlighting the docmd part of the code.
Any ideas why this is
 
doh! Right you are, it should be the TransferText is correct. (what was i
thinking) You just need to look in Access Help to get the right arguments.
If AllAreasData is the name of the table you want the end results in, then
it makes it a little easier, because each TransferText will append the data
to the existing table.
I fixed the code below. It should do what you want.
 
Back
Top