Import a CSV file into Access using VBA

0
votes

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

1
vbaexcelcsvms-access
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

1 Answers

0
votes

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