CODEWITHSUNDEEP
vbscript
jeff
jeff

Reputation: 446

How to split comma which ignores if the value is enclose to double quotes

How to split comma which ignores if the value is enclose to double quotes.

Data available:

3545,Albert,"Mathias Albert, Lars-Erik Cederman and Alexander Wendt (eds)",2010

Should be splitted in an array:

arr(0) = "3545"
arr(1) = "Mathias Albert, Lars-Erik Cederman and Alexander Wendt (eds)"
arr(2) = "2010"

Upvotes: 0

Views: 844

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

While using the ADO Text Driver would probably be the best way in the long run, you can use a specific/custom RegExp for a quick & dirty 'use just once' script:

>> Set r = New RegExp
>> r.Pattern = "^([^,]+),([^,]+),""([^""]+)"",(.+)$"
>> s = "3545,Albert,""Mathias Albert, Lars-Erik Cederman and Alexander Wendt (eds)"",2010"
>> Set m = r.Execute(s)
>> ReDim a(3)
>> For i = 0 To UBound(a)
>>     a(i) = m(0).SubMatches(i)
>> Next
>> WScript.Echo Join(a, vbCrLf)
>>
3545
Albert
Mathias Albert, Lars-Erik Cederman and Alexander Wendt (eds)
2010
>>

To cope with empty fields, the .Pattern needs some further fiddling.

If you prefer Split(), something like this:

>> s = "3545,Albert,""Mathias Albert, Lars-Erik Cederman and Alexander Wendt (eds)"",2010"
>> ReDim a(3)
>> q = Split(s, """")
>> WScript.Echo Join(q, vbCrLf)
>>
3545,Albert,
Mathias Albert, Lars-Erik Cederman and Alexander Wendt (eds)
,2010
>> c = Split(q(0), ",")
>> WScript.Echo Join(c, vbCrLf)
>>
3545
Albert

>> a(0) = c(0)
>> a(1) = c(1)
>> a(2) = q(1)
>> a(3) = Mid(q(2), 2)
>> WScript.Echo Join(a, vbCrLf)
>>
3545
Albert
Mathias Albert, Lars-Erik Cederman and Alexander Wendt (eds)
2010
>>

should get you started.

Upvotes: 2

Related Questions