How to import csv file to Access with VBA code?

Question

I have a csv file, that I need to import to Access using VBA.

I'm using the following code :

Call DoCmd.TransferText(acImportDelim, , TableName, SourceFile, HasFieldNames)

Where TableName, SourceFile and HasFieldNames are variables that store information about the file.

The import works but I have only one column imported in the table.

Does anyone have an idea please ?

The file is a csv with the separator ";" and has multiple columns.

Thank you.

Johnny Bones · Accepted Answer · 2016-02-11 16:09:03Z

11

"CSV" stands for "Comma Separated Values". Hence, the default import functionality looks for commas.

If you're using anything but commas, try this:

Start importing the file manually.
When you have specified the settings, just before clicking Finish, click Advanced...
Click "Save As" and specify a name (or accept the one proposed by Access).
Make note of the name you choose, let's say "Data Import Specification".
Click OK twice, then cancel the import.
Now use the import specification that you created in the code:

DoCmd.TransferText acImportDelim, "Data Import Specification", "Table1", "D:\Data.csv", False

answered Feb 11, 2016 at 16:09

Johnny Bones

8,4927 gold badges61 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:16:33Z

4

You need to create (and use in .TransferText) an Import Specification where you specify the separator string. See e.g. these answers:

CommunityBot

11 silver badge

answered Feb 11, 2016 at 15:30

Andre

27.7k7 gold badges43 silver badges85 bronze badges