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.

2 Responses to “Comma Separated Values and Quoted Commas (in VBScript)”

1. Tod says:

   July 19, 2011 at 9:36 am

   Exactly what I was quickly looking for and worked like a charm. Thanks for sharing!

2. Trad says:

   December 11, 2018 at 12:47 pm

   This is really helpful! (Yes, it’s 10 years later)
   Thank you, Kyle!