Removing quotes from a test file

Hi,

is there a function or simlpe way I can parse a value and strip the quotes
from it?
Example. I'm reading a CSV file, for some text fields (like a Display Name
with spaces in it) the whole field is surrounded with quotes (").

so when I get the value, the quotes are part of the string itself:
varName = "John Doe"

I can imagine some ways of removing the quotes (like using SPLIT or MID) but
is there a specific function for doing this?

Tks in advance

Anthony
Wed Oct 11 07:00:57 CDT 2006


"Diego Galindez" <Diego Galindez@discussions.microsoft.com> wrote in message
news:91CB9427-43AC-492A-AABD-305A843DC897@microsoft.com...
> Hi,
>
> is there a function or simlpe way I can parse a value and strip the quotes
> from it?
> Example. I'm reading a CSV file, for some text fields (like a Display Name
> with spaces in it) the whole field is surrounded with quotes (").
>
> so when I get the value, the quotes are part of the string itself:
> varName = "John Doe"
>
> I can imagine some ways of removing the quotes (like using SPLIT or MID)
but
> is there a specific function for doing this?
>
> Tks in advance

varName = Replace(varName, """", "")

If you need to preserve " that have been doubled up in the encode like:-

"John ""Dead man"" Doe"

Use:-

varName = Replace(varName, """""", "+~+")
varName = Replace(varName, """", "")
varName = Replace(varName, "+~+", """")

Results in :-

John "Dead man" Doe

Richard
Wed Oct 11 07:31:25 CDT 2006


"Anthony Jones" <Ant@yadayadayada.com> wrote in message
news:%23%23AUqzS7GHA.3620@TK2MSFTNGP04.phx.gbl...
>
> "Diego Galindez" <Diego Galindez@discussions.microsoft.com> wrote in
> message
> news:91CB9427-43AC-492A-AABD-305A843DC897@microsoft.com...
>> Hi,
>>
>> is there a function or simlpe way I can parse a value and strip the
>> quotes
>> from it?
>> Example. I'm reading a CSV file, for some text fields (like a Display
>> Name
>> with spaces in it) the whole field is surrounded with quotes (").
>>
>> so when I get the value, the quotes are part of the string itself:
>> varName = "John Doe"
>>
>> I can imagine some ways of removing the quotes (like using SPLIT or MID)
> but
>> is there a specific function for doing this?
>>
>> Tks in advance
>
> varName = Replace(varName, """", "")
>
> If you need to preserve " that have been doubled up in the encode like:-
>
> "John ""Dead man"" Doe"
>
> Use:-
>
> varName = Replace(varName, """""", "+~+")
> varName = Replace(varName, """", "")
> varName = Replace(varName, "+~+", """")
>
> Results in :-
>
> John "Dead man" Doe
>

That's good. However, if you have embedded commas, you will have problems
parsing the file with the quotes removed. If you are parsing csv files, I
would suggest using ADO to read the file.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

Tom
Wed Oct 11 07:36:20 CDT 2006

Note that Mr. Jones' approach does not need to be applied line by line
in the file. Rather, it can be applied to the whole file (assuming
it's a reasonable size - that is, < 1 -10 MB - though it's only limited
by your system's available memory), something like this ...

' warning - aircode
with createobject("scripting.filesystemobject")
varText = .opentextfile("your.csv", 1).readall
varText = Replace(varName, """", "")
.opentextfile("output.csv", 2,true).write varText
end with

This approach is almost universally a lot faster (and easier to code)
than creating a line-by-line implementation.

Tom Lavedas
=============
http://members.cox.net/tglbatch/wsh

Anthony Jones wrote:
> "Diego Galindez" <Diego Galindez@discussions.microsoft.com> wrote in message
> news:91CB9427-43AC-492A-AABD-305A843DC897@microsoft.com...
> > Hi,
> >
> > is there a function or simlpe way I can parse a value and strip the quotes
> > from it?
> > Example. I'm reading a CSV file, for some text fields (like a Display Name
> > with spaces in it) the whole field is surrounded with quotes (").
> >
> > so when I get the value, the quotes are part of the string itself:
> > varName = "John Doe"
> >
> > I can imagine some ways of removing the quotes (like using SPLIT or MID)
> but
> > is there a specific function for doing this?
> >
> > Tks in advance
>
> varName = Replace(varName, """", "")
>
> If you need to preserve " that have been doubled up in the encode like:-
>
> "John ""Dead man"" Doe"
>
> Use:-
>
> varName = Replace(varName, """""", "+~+")
> varName = Replace(varName, """", "")
> varName = Replace(varName, "+~+", """")
>
> Results in :-
>
> John "Dead man" Doe

Richard
Wed Oct 11 07:59:12 CDT 2006

Here is a VBScript program by Michael Harris that parses a comma delimited
line from a csv file into an array of field values. If someone has a simpler
way, that works and does not use VB or ADO, great. Watch line wrapping:
==========
Option Explicit

Dim TestString
Dim strItem

TestString = "0000-000000-00000,SURNAME,FIRSTNAME,W,DATA FIELD
1,8/03/2006,DATA FIELD 2,DATA FIELD 3,AAAAAA/BBBBBB,DATA FIELD 5,52.81,0"

For Each strItem In CSVParse(TestString)
Wscript.Echo strItem
Next

TestString = """last, first"",flast,""another value"",3,still
another,""String, """"sample"""" is simple"",""String
""""Example,1"""""",Final"

For Each strItem In CSVParse(TestString)
Wscript.Echo strItem
Next

Function CSVParse(ByVal strLine)
' Function to parse comma delimited line and return array
' of field values.

Dim arrFields
Dim blnIgnore
Dim intFieldCount
Dim intCursor
Dim intStart
Dim strChar
Dim strValue

Const QUOTE = """"
Const QUOTE2 = """"""

' Check for empty string and return empty array.
If (Len(Trim(strLine)) = 0) then
CSVParse = Array()
Exit Function
End If

' Initialize.
blnIgnore = False
intFieldCount = 0
intStart = 1
arrFields = Array()

' Add "," to delimit the last field.
strLine = strLine & ","

' Walk the string.
For intCursor = 1 To Len(strLine)
' Get a character.
strChar = Mid(strLine, intCursor, 1)
Select Case strChar
Case QUOTE
' Toggle the ignore flag.
blnIgnore = Not blnIgnore
Case ","
If Not blnIgnore Then
' Add element to the array.
ReDim Preserve arrFields(intFieldCount)
' Makes sure the "field" has a non-zero length.
If (intCursor - intStart > 0) Then
' Extract the field value.
strValue = Mid(strLine, intStart, intCursor -
intStart)
' If it's a quoted string, use Mid to
' remove outer quotes and replace inner
' doubled quotes with single.
If (Left(strValue, 1) = QUOTE) Then
arrFields(intFieldCount) = Replace(Mid(strValue,
2, Len(strValue) - 2), QUOTE2, QUOTE)
Else
arrFields(intFieldCount) = strValue
End If
Else
' An empty field is an empty array element.
arrFields(intFieldCount) = Empty
End If
' increment for next field.
intFieldCount = intFieldCount + 1
intStart = intCursor + 1
End If
End Select
Next
' Return the array.
CSVParse = arrFields
End Function

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

DiegoGalindez
Wed Oct 11 08:01:02 CDT 2006

Tks everyone !

"Tom Lavedas" wrote:

> Note that Mr. Jones' approach does not need to be applied line by line
> in the file. Rather, it can be applied to the whole file (assuming
> it's a reasonable size - that is, < 1 -10 MB - though it's only limited
> by your system's available memory), something like this ...
>
> ' warning - aircode
> with createobject("scripting.filesystemobject")
> varText = .opentextfile("your.csv", 1).readall
> varText = Replace(varName, """", "")
> .opentextfile("output.csv", 2,true).write varText
> end with
>
> This approach is almost universally a lot faster (and easier to code)
> than creating a line-by-line implementation.
>
> Tom Lavedas
> =============
> http://members.cox.net/tglbatch/wsh
>
> Anthony Jones wrote:
> > "Diego Galindez" <Diego Galindez@discussions.microsoft.com> wrote in message
> > news:91CB9427-43AC-492A-AABD-305A843DC897@microsoft.com...
> > > Hi,
> > >
> > > is there a function or simlpe way I can parse a value and strip the quotes
> > > from it?
> > > Example. I'm reading a CSV file, for some text fields (like a Display Name
> > > with spaces in it) the whole field is surrounded with quotes (").
> > >
> > > so when I get the value, the quotes are part of the string itself:
> > > varName = "John Doe"
> > >
> > > I can imagine some ways of removing the quotes (like using SPLIT or MID)
> > but
> > > is there a specific function for doing this?
> > >
> > > Tks in advance
> >
> > varName = Replace(varName, """", "")
> >
> > If you need to preserve " that have been doubled up in the encode like:-
> >
> > "John ""Dead man"" Doe"
> >
> > Use:-
> >
> > varName = Replace(varName, """""", "+~+")
> > varName = Replace(varName, """", "")
> > varName = Replace(varName, "+~+", """")
> >
> > Results in :-
> >
> > John "Dead man" Doe
>
>

Michael
Wed Oct 11 19:18:57 CDT 2006

Richard Mueller wrote:
> Here is a VBScript program by Michael Harris that parses a comma
> delimited line from a csv file into an array of field values. ...

Here's a more recent version of that script the also handles the
quoted-string-with-double-embedded-qoutes case by using eval() instead of
the original that used mid() to remove only the outer quotes...

function csvparse(byval vstring)
'=================================================================
'Walks a string in CSV format where fields are
'separated by commas. In CSV format, fields containing commas
'or embedded double quotes are enclosed in double quotes.
'Embedded double quotes are themselves doubled. When parsed,
'the enclosing double quotes are stripped and doubled embedded
'double qoutes are replaced with single double quotes.
'
'Null fields (2 consequtive commas) are given the value Empty.
'
'The degenerative case of a null line returns an empty array,
'i.e., an array with ubound = -1. It is also assumed that any
'terminating carriage-return/line-feed characters have been removed,
'otherwise they are treated as part of the last field.
'
'Each field is placed in a dynamic array which
'becomes the return value of the function.
'=================================================================

'Check for empty string and return empty array...
if len(trim(vstring)) = 0 then
csvparse = array()
exit function
end if


dim arwork 'work array
dim ignore 'flag to ignore commas
dim fieldcount 'field count
dim currpos 'pointer to start of field
const qt = """" 'literal double quote


'initialize...
ignore = false
fieldcount = 0
startpos = 1
arwork = array()


' add "," to delimit the last field
vstring = vstring&","


' walk the string
for currpos = 1 to len(vstring)
' get a character...
char = mid(vstring,currpos,1)
select case char
' if it's a " then toggle the ignore flag...
case qt: ignore = not ignore
' if it's a ,
case ","
' and we're not ignoring commas,
' then it's a field delimiter,
' otherwise just move on.
if not ignore then
' grow the array by one element
redim preserve arwork(fieldcount)
' if the "field" has a non-zero length...
if currpos-startpos > 0 then
' extract the field value
data = mid(vstring,startpos,currpos-startpos)
' if it's a quoted string, use eval to
' remove outer quotes and reduced inner
' doubled quotes
if left(data,1) = qt then
arwork(fieldcount) = eval(data)
else
arwork(fieldcount) = data
end if
else
' an empty field is an empty array element
arwork(fieldcount) = empty
end if
' get ready for next field
fieldcount = fieldcount + 1
startpos = currpos+1
end if
end select
next
' return the array
csvparse = arwork
end function


--
Michael Harris
Microsoft MVP Scripting