asp excel: comma in field value (split into array)

[skywalker skywalker]

Hi,

I have a concern on how to get around with reading the CSV file that
some of the fields having comma.

When I open up the csv file in notepad, i can see that it will be auto-
wrapped by double quote
for example:

Sandy,"123 Kent St 5,",New York

there is comma value in "123 Kent St 5,"

My asp code is currently using:
arrUpload = split(trim(objTextFileGet.readline),",",-1,1)
and it will read following

arrUpload(0) = Sandy
arrUpload(1) = "123 Kent St 5
arrUpload(2) = "
arrUpload(3) = New York

where in fact, I would like it to read:
arrUpload(0) = Sandy
arrUpload(1) = 123 Kent St 5,
arrUpload(2) = New York

How can I change the split function or any better idea?

Thanks a lot.

Regards,
Sky

 

[Evertjan.]

skywalker skywalker wrote on 13 apr 2010 in
microsoft.public.inetserver.asp.general:

I have a concern on how to get around with reading the CSV file that
some of the fields having comma.

When I open up the csv file in notepad, i can see that it will be auto-
wrapped by double quote
for example:

Sandy,"123 Kent St 5,",New York

there is comma value in "123 Kent St 5,"

My asp code is currently using:
arrUpload = split(trim(objTextFileGet.readline),",",-1,1)

This is not ASP code, as there is no ASP code,
ASP JUST being a platform.

This is VBscript code.

[..]

How can I change the split function or any better idea?

You cannot change the VBscript split function,
as it is an internal function.

You cannot change any better idea.

You could just parse the string character by character.
String parsing like this goes a long way back
in many flavours of Basic and other ancient script languages.

============================================
dim arr(100)
strCsv = "Sandy,""123 Kent St 5,"",New York"
n = 0
quoted = false

while not strCsv = ""
char = left(strCsv,1)
strCsv = mid(strCsv,2)
if char = "," and not quoted then
n=n+1
arr(n) = ""
elseif char = """" and not quoted then
quoted = true
elseif char = """" and quoted then
quoted = false
else
arr(n) = arr(n) & char
end if
wend

for i=0 to n
response.write "arr("&i&") = " & arr(i) & "<br>"
next
===========================================

returning:

arr(0) = Sandy
arr(1) = 123 Kent St 5,
arr(2) = New York