Import a CSV file into Access using VBA

Question

I need to use VBA to import a large CSV excel file into an Access table. The delimiter is "" (double quotes) except for some reason the first value is followed by " (only one quote) instead of two like every other value. The first row contains the column headers and are delimited the same way. At the bottom I have attached an example.

The CSV files are generated automatically by an accounting system daily so I cannot change the format. They are also quite large (150,000+ lines, many columns). I'm fairly new to VBA, so as much detail as is possible would be much appreciated.

Thanks in advance!

Example of format

So they're not CSV files then? It would be helpful to show a sample of the type of content you're dealing with (make something up if you can't share actual content) — Tim Williams
Probably real delimiter is invisible, string values enclosed in double quotes. In order to understand what is real delimiter, it would be great to see hex dump of 2-3 values — Sergey S.
@TimWilliams I edited the post and added a link to an example picture of what it looks like — FightOn
@sergey I edited the post and added a link to an example picture of what it looks like — FightOn
If you open the file in a text editor (eg. notepad) does it look the same ? — Tim Williams

ASH ASH · Accepted Answer · 2016-07-24T17:46:07

That doesn't sound like a CSV file. Can you open it in Excel, convert it to a true CSV, and then import that into Access? You will find many VBA-driven import options at the URL below.

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Also, take a look at these URLs.

http://www.erlandsendata.no/english/index.php?d=envbadacimportado

http://www.erlandsendata.no/english/index.php?d=envbadacimportdao

Import a CSV file into Access using VBA

1 Answers