Help for a Newbie: CSV data to be used in string manipulation
| Author |
Message |
|
David Mark
#1 / 9
|
 Help for a Newbie: CSV data to be used in string manipulation Hi folks! I'm starting to use WSH + VBScript for system administration functions,
and I can't quite get something working ...
I've got a CSV file of usernames in the format:
FirstName,LastName
and I'd like to bring those values in to a script that will perform some
simple manipulations and output the results to another CSV file ...
basically, it's an email alias generator.
I can read in the records and write out an alias, but I can't get the
field values stuffed into variables that I can use to do string
operations.
Thanks in advance for your time,
-dmm
|
| Tue, 01 Apr 2003 03:00:00 GMT |
|
 |
|
Al Dunba
#2 / 9
|
Help for a Newbie: CSV data to be used in string manipulation I'd suggest using split instead of instr, left, and right (see edit to code below). If you should ever add more fields to the csv (phone number, department, etc.) this will require less drastic changes. /Al
Quote: > Hi David > Try creating a script with the following in it, which reads the names from
a
> file called names.csv
> myfile = "C:\names.csv"
> set fso = CreateObject("Scripting.FileSystemObject")
> set textstream = fso.OpenTextFile(myFile,1,true)
> Do until textstream.AtEndOfStream 'writes existing text to temp file
> strName = textstream.ReadLine
> if strName = "" then wscript.quit
>''' commapos = instr(strname, "," )
>''' strFirst = left(strName, commapos - 1)
>''' strLast = right(strName, len(strName) - commapos)
> ''''' use split instead...
> strNameArray = split( strName, "," )
> strFirst = strName( 0 )
> strLast = strName( 1 )
> '''''
> msgbox strFirst & " " & strLast
> Loop
> textstream.close
> set fso = nothing
> wscript.quit
> --
> Tom
> -------------------------------------
> http://engines2go.com - Fast access to all the major search engines.
> No Ads, Pictures or Banners.
> > Hi folks!
> > I'm starting to use WSH + VBScript for system administration functions,
> > and I can't quite get something working ...
> > I've got a CSV file of usernames in the format:
> > FirstName,LastName
> > and I'd like to bring those values in to a script that will perform some
> > simple manipulations and output the results to another CSV file ...
> > basically, it's an email alias generator.
> > I can read in the records and write out an alias, but I can't get the
> > field values stuffed into variables that I can use to do string
> > operations.
> > Thanks in advance for your time,
> > -dmm
|
| Tue, 01 Apr 2003 03:00:00 GMT |
|
|
|
Michael Harri
#3 / 9
|
Help for a Newbie: CSV data to be used in string manipulation Here's a generic function for parsing CSV format srtrings. The little
example prompts for CSV format input. In real use you would pass lines read
from a CSV file...
vstring = inputbox("Enter a valid CSV formatted string:")
fields = csvparse(vstring)
s = ""
s = ubound(fields)+1 & " fields:" & vbcrlf & "=====" & vbcrlf
for each f in fields
s = s & f & vbcrlf
next
msgbox s
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 reduce 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
--
Quote: > Hi folks! > I'm starting to use WSH + VBScript for system administration functions,
> and I can't quite get something working ...
> I've got a CSV file of usernames in the format:
> FirstName,LastName
> and I'd like to bring those values in to a script that will perform some
> simple manipulations and output the results to another CSV file ...
> basically, it's an email alias generator.
> I can read in the records and write out an alias, but I can't get the
> field values stuffed into variables that I can use to do string
> operations.
> Thanks in advance for your time,
> -dmm
|
| Tue, 01 Apr 2003 03:00:00 GMT |
|
|
|
Dominic Mark
#4 / 9
|
Help for a Newbie: CSV data to be used in string manipulation Could you post your code?
Dominic
Quote: > Hi folks! > I'm starting to use WSH + VBScript for system administration functions,
> and I can't quite get something working ...
> I've got a CSV file of usernames in the format:
> FirstName,LastName
> and I'd like to bring those values in to a script that will perform some
> simple manipulations and output the results to another CSV file ...
> basically, it's an email alias generator.
> I can read in the records and write out an alias, but I can't get the
> field values stuffed into variables that I can use to do string
> operations.
> Thanks in advance for your time,
> -dmm
|
| Wed, 02 Apr 2003 07:53:02 GMT |
|
|
|
Tom Hingsto
#5 / 9
|
Help for a Newbie: CSV data to be used in string manipulation Hi David Try creating a script with the following in it, which reads the names from a
file called names.csv
myfile = "C:\names.csv"
set fso = CreateObject("Scripting.FileSystemObject")
set textstream = fso.OpenTextFile(myFile,1,true)
Do until textstream.AtEndOfStream 'writes existing text to temp file
strName = textstream.ReadLine
if strName = "" then wscript.quit
commapos = instr(strname, "," )
strFirst = left(strName, commapos - 1)
strLast = right(strName, len(strName) - commapos)
msgbox strFirst & " " & strLast
Loop
textstream.close
set fso = nothing
wscript.quit
--
Tom
-------------------------------------
http://engines2go.com - Fast access to all the major search engines.
No Ads, Pictures or Banners.
Quote: > Hi folks! > I'm starting to use WSH + VBScript for system administration functions,
> and I can't quite get something working ...
> I've got a CSV file of usernames in the format:
> FirstName,LastName
> and I'd like to bring those values in to a script that will perform some
> simple manipulations and output the results to another CSV file ...
> basically, it's an email alias generator.
> I can read in the records and write out an alias, but I can't get the
> field values stuffed into variables that I can use to do string
> operations.
> Thanks in advance for your time,
> -dmm
|
| Wed, 02 Apr 2003 08:33:49 GMT |
|
|
|
Tom Hingsto
#6 / 9
|
Help for a Newbie: CSV data to be used in string manipulation Al
yes I forgot about split - but yes much more versatile, thanks
--
Tom
-------------------------------------
http://engines2go.com - Fast access to all the major search engines.
No Ads, Pictures or Banners.
Quote: > I'd suggest using split instead of instr, left, and right (see edit to code > below). If you should ever add more fields to the csv (phone number, > department, etc.) this will require less drastic changes. > /Al
> > Hi David
> > Try creating a script with the following in it, which reads the names
from
> a
> > file called names.csv
> > myfile = "C:\names.csv"
> > set fso = CreateObject("Scripting.FileSystemObject")
> > set textstream = fso.OpenTextFile(myFile,1,true)
> > Do until textstream.AtEndOfStream 'writes existing text to temp file
> > strName = textstream.ReadLine
> > if strName = "" then wscript.quit
> >''' commapos = instr(strname, "," )
> >''' strFirst = left(strName, commapos - 1)
> >''' strLast = right(strName, len(strName) - commapos)
> > ''''' use split instead...
> > strNameArray = split( strName, "," )
> > strFirst = strName( 0 )
> > strLast = strName( 1 )
> > '''''
> > msgbox strFirst & " " & strLast
> > Loop
> > textstream.close
> > set fso = nothing
> > wscript.quit
> > --
> > Tom
> > -------------------------------------
> > http://engines2go.com - Fast access to all the major search engines.
> > No Ads, Pictures or Banners.
> > > Hi folks!
> > > I'm starting to use WSH + VBScript for system administration
functions,
> > > and I can't quite get something working ...
> > > I've got a CSV file of usernames in the format:
> > > FirstName,LastName
> > > and I'd like to bring those values in to a script that will perform
some
> > > simple manipulations and output the results to another CSV file ...
> > > basically, it's an email alias generator.
> > > I can read in the records and write out an alias, but I can't get the
> > > field values stuffed into variables that I can use to do string
> > > operations.
> > > Thanks in advance for your time,
> > > -dmm
|
| Wed, 02 Apr 2003 09:26:52 GMT |
|
|
|
Al Dunba
#7 / 9
|
Help for a Newbie: CSV data to be used in string manipulation Cool, Michael. I thought at first you were making a simple problem more difficult than necessary until I realized your solution handled quoted fields and fields containing field delimiters. Another way to deal with syntactical ambiguities such as these and process
the .csv format string according to how various other programs deal with the
.csv file format would be have the VBScript code launch Excel to open the
.csv file, and then extract the content of the cells. Your solution is
tighter, smaller, and probably faster, but if a program were already using
excel anyway, using it to parse a .csv file as will might make sense.
/Al
Quote: > Here's a generic function for parsing CSV format srtrings. The little
> example prompts for CSV format input. In real use you would pass lines
read
> from a CSV file...
> vstring = inputbox("Enter a valid CSV formatted string:")
> fields = csvparse(vstring)
> s = ""
> s = ubound(fields)+1 & " fields:" & vbcrlf & "=====" & vbcrlf
> for each f in fields
> s = s & f & vbcrlf
> next
> msgbox s
> 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 reduce 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
> --
> > Hi folks!
> > I'm starting to use WSH + VBScript for system administration functions,
> > and I can't quite get something working ...
> > I've got a CSV file of usernames in the format:
> > FirstName,LastName
> > and I'd like to bring those values in to a script that will perform some
> > simple manipulations and output the results to another CSV file ...
> > basically, it's an email alias generator.
> > I can read in the records and write out an alias, but I can't get the
> > field values stuffed into variables that I can use to do string
> > operations.
> > Thanks in advance for your time,
> > -dmm
|
| Wed, 02 Apr 2003 03:00:00 GMT |
|
|
|
Michael Harri
#8 / 9
|
Help for a Newbie: CSV data to be used in string manipulation <reposted from the ...scripting.wsh newsgroup>
It's even easier to use the Microsoft Text Driver and just open the CSV file
directly as an ADO recordset.
'==============================================
'Here's an example vbscript:
set rs = createobject("ador.recordset")
strConnect = _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DefaultDir=c:\;"
Const adOpenStatic = 3
rs.open "select * from test.csv", strConnect, adOpenStatic
msgbox "Recordcount: " & rs.recordcount
msgbox "Fields per record: " & rs.fields.count
s = "Field names: "
dl = ""
for each f in rs.fields
s = s & dl & f.name: dl = "; "
next
msgbox s
do until rs.eof
s = ""
for each f in rs.fields
s = s & f.name & "=" & f.value & vbcrlf
next
msgbox s
rs.movenext
loop
msgbox "done..."
'==============================================
'Here's the test.csv file I used to test this:
'
'header1,header2,header3
'r1c1,r1c2,r1c3
'r2,c1",r2c2,r2c3
'r3c1,"r""3,c""2",r3c3
'r4c1,r4c2,"r4"",""c3"
'
'==============================================
'Here's the schema.ini file I used:
'
'[test.csv]
'ColNameHeader=True
'Format=CSVDELIMITED
'
'==============================================
'Here's where you'll find the documentation:
'
'Text File Driver Programming Considerations
' http://msdn.microsoft.com/library/psdk/dasdk/odbc4g6r.htm
'
'Schema.ini File
' http://msdn.microsoft.com/library/psdk/dasdk/odbc8t45.htm
--
Michael Harris
Microsoft.MVP.Scripting
--
Quote: > Cool, Michael. I thought at first you were making a simple problem more > difficult than necessary until I realized your solution handled quoted > fields and fields containing field delimiters. > Another way to deal with syntactical ambiguities such as these and process
> the .csv format string according to how various other programs deal with
the
> .csv file format would be have the VBScript code launch Excel to open the
> .csv file, and then extract the content of the cells. Your solution is
> tighter, smaller, and probably faster, but if a program were already using
> excel anyway, using it to parse a .csv file as will might make sense.
> /Al
> > Here's a generic function for parsing CSV format srtrings. The little
> > example prompts for CSV format input. In real use you would pass lines
> read
> > from a CSV file...
> > vstring = inputbox("Enter a valid CSV formatted string:")
> > fields = csvparse(vstring)
> > s = ""
> > s = ubound(fields)+1 & " fields:" & vbcrlf & "=====" & vbcrlf
> > for each f in fields
> > s = s & f & vbcrlf
> > next
> > msgbox s
> > 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 reduce 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
> > --
> > > Hi folks!
> > > I'm starting to use WSH + VBScript for system administration
functions,
> > > and I can't quite get something working ...
> > > I've got a CSV file of usernames in the format:
> > > FirstName,LastName
> > > and I'd like to bring those values in to a script that will perform
some
> > > simple manipulations and output the results to another CSV file ...
> > > basically, it's an email alias generator.
> > > I can read in the records and write out an alias, but I can't get the
> > > field values stuffed into variables that I can use to do string
> > > operations.
> > > Thanks in advance for your time,
> > > -dmm
|
| Wed, 02 Apr 2003 03:00:00 GMT |
|
|
|
David Mark
#9 / 9
|
Help for a Newbie: CSV data to be used in string manipulation Thanks to all who contributed! You've more than solved my problem, and I really appreciate the help!
-dmm
|
| Wed, 02 Apr 2003 03:00:00 GMT |
|
| |
|
