Stripping out nested quotes

I have a script that processes a csv file using the filesystemobject
method - at the moment, I'm reading in a csv, doing a replace function
on certain specific characters, then writing the result back to another
file.

However, I have a problem in that the csv uses quotes as text
delimiters, and some fields contain nested quotes - which I need to
strip out.

The nested quotes are not necessarily at the beginning and end of the
strings (such as ""NAME"") - in theory, they could be anywhere (e.g.
"NA"ME").

Obviously each field is separated by a comma, and starts and ends with
a quote, which is all as expected - I just need to strip out any other
quotes within the fields.

Does anyone have a script that would do this?

mj
Mon May 08 05:11:50 CDT 2006

I think I've cracked this, for anyone interested. This will cope with
nested commas and quotes.


Dim fso, fsoFolder, fsoFile, sFolder, sFileName, sCreateFile,
sDeleteFile
Dim readdata, writedata, dataarray, arraycount, writestring

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)

Set fso = CreateObject("Scripting.FileSystemObject")
Set sOpenFile = fso.OpenTextFile("folder\file.csv", 1)

set sCreateFile = fso.CreateTextFile("temp\temp.csv")


do while not sOpenFile.atendofstream

writestring = ""
readdata = sOpenFile.ReadLine
dataarray = split(readdata, ",")

for arraycount = 0 to ubound(dataarray)

if (left(dataarray(arraycount), 1) = """" or
right(dataarray(arraycount), 1) = """") and len(dataarray(arraycount))
> 2 then
if left(dataarray(arraycount), 1) = """" then
writestring = writestring & """"
else
writestring = writestring & left(dataarray(arraycount),1)
end if
writestring = writestring &
replace(mid(dataarray(arraycount),2,len(dataarray(arraycount))-2),
chr(34), "'")
if right(dataarray(arraycount), 1) = """" then
writestring = writestring & """"
else
writestring = writestring & right(dataarray(arraycount),1)
end if
elseif dataarray(arraycount) = """""" then
writestring = writestring & dataarray(arraycount)
else
writestring = writestring & replace(dataarray(arraycount), chr(34),
"'")
end if

if arraycount < ubound(dataarray) then writestring = writestring & ","

next
sCreateFile.writeline(writestring)

loop

Michael
Mon May 08 19:47:19 CDT 2006

mj.redfox.mj@gmail.com wrote:
> I think I've cracked this, for anyone interested. This will cope with
> nested commas and quotes.
>
>

I first posted a csvparse function (vbscript code) 5 years ago and many
people use it - feel free.

csvparse group:*.scripting - Google Groups
http://groups.google.com/groups?q=csvparse group:*.scripting


> Dim fso, fsoFolder, fsoFile, sFolder, sFileName, sCreateFile,
> sDeleteFile
> Dim readdata, writedata, dataarray, arraycount, writestring
>
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoFolder = fso.GetFolder(sFolder)
>
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set sOpenFile = fso.OpenTextFile("folder\file.csv", 1)
>
> set sCreateFile = fso.CreateTextFile("temp\temp.csv")
>
>
> do while not sOpenFile.atendofstream
>
> writestring = ""
> readdata = sOpenFile.ReadLine
> dataarray = split(readdata, ",")
>
> for arraycount = 0 to ubound(dataarray)
>
> if (left(dataarray(arraycount), 1) = """" or
> right(dataarray(arraycount), 1) = """") and len(dataarray(arraycount))
>> 2 then
> if left(dataarray(arraycount), 1) = """" then
> writestring = writestring & """"
> else
> writestring = writestring & left(dataarray(arraycount),1)
> end if
> writestring = writestring &
> replace(mid(dataarray(arraycount),2,len(dataarray(arraycount))-2),
> chr(34), "'")
> if right(dataarray(arraycount), 1) = """" then
> writestring = writestring & """"
> else
> writestring = writestring & right(dataarray(arraycount),1)
> end if
> elseif dataarray(arraycount) = """""" then
> writestring = writestring & dataarray(arraycount)
> else
> writestring = writestring & replace(dataarray(arraycount), chr(34),
> "'")
> end if
>
> if arraycount < ubound(dataarray) then writestring = writestring & ","
>
> next
> sCreateFile.writeline(writestring)
>
> loop

--
Michael Harris
Microsoft MVP Scripting