I needed to parse a csv file for my project. Could not find any so I
put together this code. It works for general cases. Use it and let me
know. Just
Cut and paste the 2 functions in a new Form and the csv text in
test.csv in the same directory. Watch for the line breaks!
'------ Parse CSV + Demo ----------------------
' (c) Shahid Mahmood (mahmood@alum.r pi.edu)
' ---------------------------------------------
' Parses a csv file and prints results in a debug window
("immediate" ).
' If you know how many fields per line are coming, you can diemnsion
the
' 'strTemp() array accordingly and lost the ReDim ..()
' Handles the case of quoted and un-quoted fields, (only double quoted
"")
' Also handles commas inside a quoted field. DOES not handle embedded
double
' quotes or single quoted fields.
' Enjoy!
Private Sub Form_Load()
Dim fieldArray() As String
Dim i As Integer
Dim lineRead As String
'MsgBox App.Path ' to see where VB is reading from
Open App.Path + "\" + "test.csv" For Input As #1
While Not EOF(1)
Line Input #1, lineRead
fieldArray = parseCsv(lineRe ad)
' Do whatever you want with the array of fields, e.g.
If lineRead <> "" Then For i = 0 To UBound(fieldArr ay):_
Debug.Print fieldArray(i): Next
Wend
End
End Sub
Function parseCsv(lineIn As String) As String()
Dim s As String
Dim local_s As String
Dim n1 As Integer
Dim n2 As Integer
Dim str2 As String
Dim i As Integer
Dim strTemp() As String
i = 0
s = Trim(lineIn) ' remove spaces, if any
While Len(s)
ReDim Preserve strTemp(i) ' Didnt want to have fixed fields,
' dont know how to dynamically allocate, hence ...
If Mid$(s, 1, 1) = """" Then ' if already in quotes ...
strTemp(i) = Mid$(s, 2, InStr(2, s, """") - 2)'find 2nd ""
Else
If InStr(s, ",") Then strTemp(i) = Mid$(s, 1, InStr(s, ",") -
1)
End If
's = Mid$(s, Len(strTemp(i)) + 1)
n2 = InStr(Len(strTe mp(i)) Or 1, s, ",")
If n2 Then s = Trim(Mid$(s, n2 + 1)) Else s = "" ' clip till next
i = i + 1
Wend
parseCsv = strTemp
End Function
'---- END -------
Sample test.csv
---------------
"Hello, World", wow, " asdasd asdasd"
"Hello1, World1", wow1, " asdasd1 asdasd1"
"Hello2, World2", wow2, " asdasd2 asdasd2"
"This is Field1", "Field2",Field3 ,"Field4",field 5,field6, "Field7,8,9 "
put together this code. It works for general cases. Use it and let me
know. Just
Cut and paste the 2 functions in a new Form and the csv text in
test.csv in the same directory. Watch for the line breaks!
'------ Parse CSV + Demo ----------------------
' (c) Shahid Mahmood (mahmood@alum.r pi.edu)
' ---------------------------------------------
' Parses a csv file and prints results in a debug window
("immediate" ).
' If you know how many fields per line are coming, you can diemnsion
the
' 'strTemp() array accordingly and lost the ReDim ..()
' Handles the case of quoted and un-quoted fields, (only double quoted
"")
' Also handles commas inside a quoted field. DOES not handle embedded
double
' quotes or single quoted fields.
' Enjoy!
Private Sub Form_Load()
Dim fieldArray() As String
Dim i As Integer
Dim lineRead As String
'MsgBox App.Path ' to see where VB is reading from
Open App.Path + "\" + "test.csv" For Input As #1
While Not EOF(1)
Line Input #1, lineRead
fieldArray = parseCsv(lineRe ad)
' Do whatever you want with the array of fields, e.g.
If lineRead <> "" Then For i = 0 To UBound(fieldArr ay):_
Debug.Print fieldArray(i): Next
Wend
End
End Sub
Function parseCsv(lineIn As String) As String()
Dim s As String
Dim local_s As String
Dim n1 As Integer
Dim n2 As Integer
Dim str2 As String
Dim i As Integer
Dim strTemp() As String
i = 0
s = Trim(lineIn) ' remove spaces, if any
While Len(s)
ReDim Preserve strTemp(i) ' Didnt want to have fixed fields,
' dont know how to dynamically allocate, hence ...
If Mid$(s, 1, 1) = """" Then ' if already in quotes ...
strTemp(i) = Mid$(s, 2, InStr(2, s, """") - 2)'find 2nd ""
Else
If InStr(s, ",") Then strTemp(i) = Mid$(s, 1, InStr(s, ",") -
1)
End If
's = Mid$(s, Len(strTemp(i)) + 1)
n2 = InStr(Len(strTe mp(i)) Or 1, s, ",")
If n2 Then s = Trim(Mid$(s, n2 + 1)) Else s = "" ' clip till next
i = i + 1
Wend
parseCsv = strTemp
End Function
'---- END -------
Sample test.csv
---------------
"Hello, World", wow, " asdasd asdasd"
"Hello1, World1", wow1, " asdasd1 asdasd1"
"Hello2, World2", wow2, " asdasd2 asdasd2"
"This is Field1", "Field2",Field3 ,"Field4",field 5,field6, "Field7,8,9 "