0

I have a csv file with delimiter as , (comma) and few of the data column of same file has comma in it .

Hence while linking / importing the file, data is getting jumbled in next column.

I have tried all possible means like skip column etc , but not getting any fruitful results.

Please let me know if this can be handled through VBA function in ms-access.

Rishabh Kumar
  • 2,342
  • 3
  • 13
  • 23
balwinder
  • 1

1 Answers1

2

If the CSV file contains text fields that contain commas and are not surrounded by a text qualifier (usually ") then the file is malformed and cannot be parsed in a bulletproof way. That is,

1,Hello world!,1.414
2,"Goodbye, cruel world!",3.142

can be reliably parsed, but

1,Hello world!,1.414
2,Goodbye, cruel world!,3.142

cannot. However, if you have additional information about the file, e.g., that it should contain three columns

  1. a Long Integer column,
  2. a Short Text column, and
  3. a Double column

then your VBA code could read the file line-by-line and split the string on commas into an array. The first array element would be the Long Integer, the last array element would be the Double value, and the remaining "columns" in between could be concatenated together to reconstruct the string.

As you can imagine, that approach could easily be confounded (e.g., if there was more than one text field that might contain commas). Therefore it is not particularly appealing.

(Also worth noting is that the CSV parser in Access has never been able to properly handle text fields that contain line breaks, but at least we can import those CSV files into Excel and then import into Access from the Excel file.)

TL;DR - If the CSV file contains unqualified text containing commas then the system that produced it is broken and should be fixed.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418