CSV files are ubiquitous – they are the most common means of transferring data from one system to another using a text file. They do, however, have one major drawback – commas.
A string of comma separated values is no problem when all the values are numbers or text values that don’t contain commas. As soon as one of these values actually contains a comma then it gets much harder to deal with.
If you’ve worked with csv files you’ll know that there are some variations and the most common is to add quotes to the values so that each value is contained in quotes and any commas that appear between the quotes are part of the value and not a delimiting comma. This makes splitting out the date straightforward because there is a consistent approach.
Some applications, however, will only add quotes where a value contains a comma. This means when you read in a line from your csv file and use the split function with comma delimiters, you will get inconsistent results because the comma in the quote value will get treated as a delimiter.
To solve this, you have to replace the comma in any quoted string with a different character so that you can perform the split consistently and then put the commas back when working the extracted values.
Here’s a function that does just that – it replaces any commas in quoted values whilst retaining all the other commas.
Function FixCommasInCSVLine(LineString As String, CommaReplaceChar As String) As String
- 'Fixes a csv line that has quoted elements - any commas are replaced and can then
- 'returned when the elements are read
- Dim strLine As String
- Dim ary() As String
- Dim i As Integer
- 'Split string at quotes - append comma to start
- ary() = Split("," & LineString, Chr(34))
- For i = 0 To UBound(ary())
- 'Replace commas when they aren't first char and rebuild string
- If Left(Trim(ary(i)), 1) <> "," Then
- strLine = strLine & Replace(ary(i), ",", CommaReplaceChar)
- Else
- strLine = strLine & Trim(ary(i))
- End If
- Next
- 'Strip first comma
- strLine = Right(strLine, Len(strLine) - 1)
- FixCommasInCSVLine = strLine
End Function