split function issue

I am using the split function but have run into an issue. A spreadsheet has
been saved to csv so my split function looks for seperator of ",". all works
well except to when there is a comma inside quotes. As you know if there is
a comma in a field when excel export it will place quotes around the value.

for example:
1,test,"user, test",5590, xyz

how can I overcome this issue? Is there some other function I can use?

thanks in advance.

Evertjan
Wed Feb 28 17:22:43 CST 2007

=?Utf-8?B?UmRT?= wrote on 28 feb 2007 in
microsoft.public.scripting.vbscript:

> I am using the split function but have run into an issue. A
> spreadsheet has been saved to csv so my split function looks for
> seperator of ",". all works well except to when there is a comma
> inside quotes. As you know if there is a comma in a field when excel
> export it will place quotes around the value.
>
> for example:
> 1,test,"user, test",5590, xyz
>
> how can I overcome this issue? Is there some other function I can
> use?

Quick and dirty programming:

<script language=vbs>

function splitCSV(s)
splitCSV=""
q = true
for n=1 to len(s)
z = mid(s,n,1)
if z = """" then q = not q
if z = "," and q then z = "<br>"
splitCSV = splitCSV & z
next
end function

s = "1,test,""user, test"",5590, xyz"

document.write splitCSV(s)

</script>


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Bob
Wed Feb 28 17:31:42 CST 2007

RdS wrote:
> I am using the split function but have run into an issue. A spreadsheet has
> been saved to csv so my split function looks for seperator of ",". all works
> well except to when there is a comma inside quotes. As you know if there is
> a comma in a field when excel export it will place quotes around the value.

Not just commas.
Newlines (CR/LF pairs) can also occur in quoted strings.

>
> for example:
> 1,test,"user, test",5590, xyz
>
> how can I overcome this issue? Is there some other function I can use?



I have used two approaches, though there are probably many more possible.

One is to write an actual character-based parser, handling the data in linear fashion.
The split() function is not of much use for that.

The other is to adapt the approach to the pre-chosen tools, such as split(), by recognizing
the peculiarities of quoted strings. When I take this approach, the first split() I apply
is by quote characters, not commas. With the array returned, all odd-numbered elements
need one kind of handling, and the even-numbered ones need a different kind.



Bob
Note:
If it is possible for quote characters to appear within strings, then it is
critical to know ahead of time *precisely* which notation rules will be used.

Paul
Wed Feb 28 17:43:45 CST 2007


"RdS" <RdS@nospam.nospam> wrote in message
news:B4F17864-A406-4DF1-88B1-1496EA9D3A42@microsoft.com...
>I am using the split function but have run into an issue. A spreadsheet
>has
> been saved to csv so my split function looks for seperator of ",". all
> works
> well except to when there is a comma inside quotes. As you know if there
> is
> a comma in a field when excel export it will place quotes around the
> value.
>
> for example:
> 1,test,"user, test",5590, xyz
>
> how can I overcome this issue? Is there some other function I can use?
>
> thanks in advance.

Googleing can return a sample script that uses ADO (installed by default on
most recent Windows systems), but you have to google groups, not web or
images or video, etc.

Google for the string, enclosed in quotes: "Using ADO to process a CSV file"
You should get a link to a post that included a reply by Michael Harris that
includes text of the script, sample CSV data, and the schema.ini file
required by ADO to process the CSV file. Create the three files in a
folder, run the script, and see if it works for you. If it works, try it
with your CSV file and see how it does.

-Paul Randall