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
			booInQuote = TRUE
		End If
	End If

	if booInQuote Then
		charExp = Replace(charExp,",","|&|")
	End If

	strReconstruct = strReconstruct & charExp
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 />"

Which outputs:

col3.1, col3.2
col6.1, col6.2

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:

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

  2. Trad says:

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

Leave a Reply

Staypressed theme by Themocracy