'********************************************************************
'*
'* Function CSVParse
'*
'* Author: NetworkAdminKB.com
'* Created: 2007-07-11
'* Modified: 2007-07-11
'*
'* Purpose: Returns the appropriate Column of the comma deliminated CSV string.
'* Accounts for quotes in the string, just in case the data has a
'* deliminator in the text that is not acting as a separator.
'*
'* Input: strCSV A comma deliminated string.
'* intCol The integer column position of the data to return
'* starting at 0.
'* strDelimiter The delimiter for the variables in the line.
'* Default = ","
'*
'* Output: Returns the data corresponding to the specified column removing any
'* quotes from the string before returning.
'* Returns Empty if intCol > then number of actual columns.
'* Returns strCSV if no delimiator is found in string.
'*
'* Notes: A string consists of all characters between two commas (eg: ,) or
'* all characters between ," and ",
'* Spaces after commas are considered part of the text and are not
'* stripped.
'*
'* Changes:
'* 2007-07-09: Corrected issue with strCSV not containing a comma.
'* 2007-07-09: Returns Empty if intCol is > then the number of columns in the
'* line.
'* 2007-07-11: Add strTextQualifier and strDelimiter constants to simply
'* updates in the future.
'********************************************************************
Function CSVParse(ByVal strCSV, ByVal intCol, ByVal strDelimiter)
'Version: 1.2 2007-07-11
Dim i, aryComma, aryResult, intQpos1, intQpos2, intLen
Dim strTextQualifier
strTextQualifier = Chr(34) ' <- Chr(34) is the ascii code for "
i = 0
aryComma = Array(i)
aryResult = Array(i)
'Set default if not specified
If Len(strDelimiter) = 0 Then strDelimiter = ","
aryComma(i) = InStr(1, strCSV, strDelimiter)
'If no comma found in string return the original string.
If aryComma(i) = 0 Then
'Wscript.Echo "no comma"
CSVParse = strCSV
Exit Function
Else
'Comma found in string
aryResult(i) = Left(strCSV, aryComma(i)-1)
If InStr(1, aryResult(i), strTextQualifier) <> 0 Then
intQpos1 = InStr(1, strCSV, strTextQualifier)
intQpos2 = InStr(intQpos1 +1 , strCSV, strTextQualifier)
aryComma(i) = InStr(intQpos2 +1, strCSV, strDelimiter)
aryResult(i) = Mid(strCSV, intQpos1+1, intQpos2-intQpos1-1)
End If
Do While aryComma(i) <> 0
i = i + 1
ReDim Preserve aryComma(i)
ReDim Preserve aryResult(i)
aryComma(i) = InStr(aryComma(i-1)+1, strCSV, strDelimiter)
If aryComma(i) <> 0 Then
aryResult(i) = Mid(strCSV, aryComma(i-1)+1, _
aryComma(i) - aryComma(i-1) -1 )
If InStr(1, aryResult(i), strTextQualifier) <> 0 Then
intQpos1 = InStr(aryComma(i-1), strCSV, strTextQualifier)
intQpos2 = InStr(intQpos1 +1 , strCSV, strTextQualifier)
aryComma(i) = InStr(intQpos2 +1, strCSV, strDelimiter)
aryResult(i) = Mid(strCSV, intQpos1+1, intQpos2-intQpos1-1)
End If 'InStr(1, aryResult(i), strTextQualifier) <> 0
End If 'aryComma(i) <> 0
Loop 'While aryComma(i) <> 0
intLen = Len(strCSV)
aryResult(i) = Right(strCSV, intLen - aryComma(i-1))
If InStr(1, aryResult(i), strTextQualifier) <> 0 Then
aryResult(i) = Replace(aryResult(i), strTextQualifier, "")
End If
End If 'aryComma(i) = 0
If intCol > UBound(aryResult) Then
'Return empty
CSVParse = Empty
Else
CSVParse = aryResult(intCol)
End If 'intCol > UBound(aryResult)
End Function 'CSVParse