Comma Separated Values and Quoted Commas (in VBScript)
Take for example the following string.
col1, col2, "col3.1, col3.2", col4, col5, "col6.1, col6.2", col7
Now understand that the values within the quotes should be considered the elements of a column. A real world example might be something like
English, "Squarepants, Spongebob", Fictional, "Bikini Bottom, Under the Sea"
Now split with commas.
In this case, the first column would contain the word English and the second column should contain Squarepants, Spongebob. The third should be Fictional and the fourth column should contain Bikini Bottom, Under the Sea.
That would be too easy.
So far I’ve not found a simple way (though it’s probably staring me in the face) to parse that first example line to retain the commas within the quotations as their own column.
So I’m just throwing that out there.
7.2 minutes later
I gave up trying with regular expressions (it ended up retaining only commas in the last quoted string) and decided to brute-force it. This may be inefficient but it gets the job done.
dim strReconstruct, strExp dim charExp dim booInQuote strExp = "col1, col2, ""col3.1, col3.2"", col4, col5, ""col6.1, col6.2"", col7" lngRowLength = Len(strexp) booInQuote = FALSE for i=1 to lngRowLength charExp = Mid(strexp,i,1) if StrComp(charExp,"""") = 0 Then if booInQuote Then booInQuote = FALSE Else booInQuote = TRUE End If End If if booInQuote Then charExp = Replace(charExp,",","|&|") End If strReconstruct = strReconstruct & charExp next strExp = strReconstruct Dim arrstrExp arrstrExp = Split(strexp,",") for each line in arrstrExp line = Replace(line,"|&|",",") '' Turn back placeholder into original comma line = Replace(line,"""","") response.write line & "<br />" next
Which outputs:
col1 col2 col3.1, col3.2 col4 col5 col6.1, col6.2 col7
It is a solution, but the search for an efficient solution continues.
Exactly what I was quickly looking for and worked like a charm. Thanks for sharing!
This is really helpful! (Yes, it’s 10 years later)
Thank you, Kyle!