How to import csv file to Access with VBA code?

1
votes

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.

2
vbams-accessimport

2 Answers

8
votes

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

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

  1. Start importing the file manually.

  2. When you have specified the settings, just before clicking Finish, click Advanced...

  3. Click "Save As" and specify a name (or accept the one proposed by Access).

  4. Make note of the name you choose, let's say "Data Import Specification".

  5. Click OK twice, then cancel the import.

  6. Now use the import specification that you created in the code:

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

4
votes

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

https://stackoverflow.com/a/3417067/3820271

https://stackoverflow.com/a/32806065/3820271