Csv data into vb script array

Cuau · Joined: 04 Oct 2007 Posts: 1

Hi everyone

I need to know how to input a csv data into an array in visual basic
script.

Thanks.

Archived from group: microsoft>public>vb>syntax

cmaubert · Joined: 04 Oct 2007 Posts: 1

no pues ni idea

Cuau ha escrito:

> Hi everyone
>
> I need to know how to input a csv data into an array in visual basic
> script.
>
> Thanks.

Jeff Johnson · Joined: 04 Oct 2007 Posts: 1327

"Cuau" wrote in message @m7g2000cwm.googlegroups.com...

> I need to know how to input a csv data into an array in visual basic
> script.

microsoft.public.scripting.vbscript.

Richard Mueller · Joined: 04 Oct 2007 Posts: 15

Cuau wrote:

> I need to know how to input a csv data into an array in visual basic
> script.

I've used code similar to below in VB6:
============
' Specify comma delimited file.
strFile = "c:\MyFolder\input.csv"

' Declare array.
Dim arrRows() As String

' Open the file.
Open strFile For Input As #1

' Read the file.
k = 0
Do Until EOF(1)
Input #1, strField0, strField1, strField2, strField3
' Do what I want with the values.
ReDim Preserve arrRows(3, k)
arrRows(0, k) = strField0
arrRows(1, k) = strField1
arrRows(2, k) = strField2
arrRows(3, k) = strField3
k = k + 1
Loop

Close #1
===========
Look up help on Open, EOF, and Input. VB is very good at reading csv files
(unlike VBScript). You need to know how many fields are in the file. I
sometimes read the values into a disconnected recordset, so I can sort the
data. For example:
============
' Specify comma delimited file.
strFile = "c:\MyFolder\input.csv"

' Setup disconnected recordset. This requires reference to ADO.
Dim adoImport As ADODB.Recordset
Set adoImport = New ADODB.Recordset
adoImport.CursorType = adOpenKeyset
adoImport.LockType = adLockOptimistic
adoImport.Fields.Append "Field0", adVarChar, 50
adoImport.Fields.Append "Field1", adVarChar, 80
adoImport.Fields.Append "Field2", adVarChar, 100
adoImport.Fields.Append "Field3", adVarChar, 10
adoImport.Open

' Open the file.
Open strFile For Input As #1

' Read the file.
Do Until EOF(1)
Input #1, strField0, strField1, strField2, strField3
' Do what I want with the values.
adoImport.AddNew
adoImport("Field0").Value = strField0
adoImport("Field1").Value = strField1
adoImport("Field2").Value = strField2
adoImport("Field3").Value = strField3
adoImport.Update
Loop

Close #1

adoImport.Sort = "Field0"

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

Richard Mueller · Joined: 04 Oct 2007 Posts: 15

Sorry, I just realized that you are in VBScript, so my VB example doesn't
help. This is very difficult in VBScript, but Michael Harris came up with a
solution that I find works great. Here is an example (watch out for line
wrapping). Note that the hard coded strings have all quotes doubled, because
VBScript requires it, but the csv file will not:
==================
' Based on program by Michael Harris.

Option Explicit

Dim TestString
Dim strItem

TestString = "000-0000-0000,SURNAME,FIRSTNAME,W,FIELD 1,8/03/2006,FIELD
2,DATA FIELD 3,AAA/BBB,DATA FIELD 5,52.81,0"
TestString = """last, first"",flast,""another value"",3,still
another,""String, """"sample"""" is simple"",""String
""""Example,1"""""",Final"
Wscript.Echo TestString

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

Can't Assign Array to Variant in Array Hi, I want to assign an array to another array's element like: Dim ar1(2) Dim ar2$(2) ar1(0)=2 ar1(1)=ar2 ' *** But I get a Type Mismatch error for the last line of code. Can someone please enlighten me on how to perform

script I'm trying to run a script but need admin rights to do it, is there anyway I can give the user temp admin rights or use an account with admin rights to do so? it looks sort of like this Set objShell = objShell.Run "\\

PMT function in vb script Hi Does anyone know in VB script if there is a function for PMT or anything close to it? Thanks!

VB script question i am looking 4 a way to read a text file and look for a line in it by comapering to a syntax, , extract a word from the matching line and write it to a differant text file doing all this in a loop untill the end of the file hope that one of you can help

vbok msgbox script how do i change the msgbox "vbok" button caption to something other than "OK"? URGENT!