' ReadCSV.vbs
' VBScript program to read a comma delimited file and convert the
' fields in each line into an array of values.
' Based on program by Michael Harris (a Microsoft MVP).
'
' ----------------------------------------------------------------------
' Copyright (c) 2007 Richard L. Mueller
' Hilltop Lab web site - http://www.rlmueller.net
' Version 1.0 - January 2, 2007
'
' Demonstrates how to read a Comma delimited file in VBScript. Uses a
' function that converts each line of the comma delimited file into
' an array of field values. Handles quoted strings and quotes and
' commas embedded in quoted strings.
'
' You have a royalty-free right to use, modify, reproduce, and
' distribute this script file in any way you find useful, provided that
' you agree that the copyright owner above has no warranty, obligations,
' or liability for such use.

Option Explicit

Dim TestString
Dim strItem

' Comma delimited line without quotes.
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

' Comma delimited line with quoted strings
' and embedded quotes and commas.
' In this VBScript program, embedded quotes must be doubled, but
' this will not be the case when lines are read from a csv file.
TestString = """last, first"",flast,""another value"",3," _
    & "still another,""String, """"sample"""" is simple""," _
    & """String """"Example,1"""""",Final"

Wscript.Echo "---"
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