Help for a Newbie: CSV data to be used in string manipulation
David Marks
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
Al Dunbar
below). If you should ever add more fields to the csv (phone number,
department, etc.) this will require less drastic changes.
/Al
"Tom Hingston" <t...@xtra.co.nz> wrote in message
news:O0iFGaX...@cppssbbsa02.microsoft.com...
> 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.
>
>
> "David Marks" <dma...@nt4hire.com> wrote in message
> news:MPG.145137bcc...@enews.newsguy.com...
Michael Harris
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
--
Dominic Marks
Could you post your code?
Dominic
Tom Hingston
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.
Tom Hingston
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.
"Al Dunbar" <Al_D...@HoTMaiL.com> wrote in message
news:sufbmdj...@corp.supernews.com...
> 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
>
> "Tom Hingston" <t...@xtra.co.nz> wrote in message
> news:O0iFGaX...@cppssbbsa02.microsoft.com...
> > 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 )
> > '''''
Al Dunbar
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
"Michael Harris" <nom...@nomail.nomail> wrote in message
news:u$#DPnaNAHA.276@cppssbbsa05...
Michael Harris
<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
--
"Al Dunbar" <Al_D...@HoTMaiL.com> wrote in message
news:suh1pb9...@corp.supernews.com...
David Marks
You've more than solved my problem, and I really appreciate the help!
-dmm