Importing CSV (in Exel format) file.

M

Mitch

Hi all,
i'm trying to code in VBA an import
routine that will write the data from an Excel csv file
into a table in Access XP. I have this code :-
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "tbldata", "D:/projects/billing/t
est.xls", True, "A:N"

but it is complaining about the field names in the Access
table. I recall there was a way to code specifically what
column writes to which field but can't remeber how this
was done. Can anyone please offer advice ?

Thanks in advance,
Mitch...
 
K

Ken Snell [MVP]

A csv file is actually a comma-delimited text file. Use TransferText
instead.
 
M

Mitch

Thanks.
OK.. given that atry but still getting the same error
message. The fieldnames in the csv file are different
from the fieldnames in the Access table. There is a way
of assigning the columns to go to certain field in the DB
but as I say i've forgotten how this was coded.

Mitch...
 
K

Ken Snell [MVP]

You don't code that. You can create an import specification and use that in
the TransferText action. Or you could just import the file into a temporary
table and then use an append query to correctly copy the data to the
permanent table.

You create an import specification by doing the import process manually
(File | Get External Data ... | Import), selecting the file to be imported,
and then clicking the Advanced button (lower left) in the wizard window that
then opens. There you can set all the spec info and then save it as a spec
with a name. That name is then used as the second argument in the
TransferText action.
 
Top