gmulhall
01-13-2007, 04:17 AM
Hi,
Could anyone point me to an Excel VBA routine or function that I can use in my own VBA code to split a CSV file where the file may have comma's imbedded in a field delimited by " " as well as fields not containing commas and so not surrounded by " ".
eg "ACRUX LIMITED",ACR,"Pharmaceuticals, Biotechnology & Life Sciences"
I've written the routine below but it is slow and I'd expect there is a better way - perhaps using an inbuilt Excel function.
Thanks,
Geoff
Sub ParseCSVRec(strInput As String)
Dim i As Long
Dim j As Long
Dim Char As String
i = 1
j = 0
While i <= Len(strInput)
Char = Mid(strInput, i, 1)
If Char = Chr(34) Then ' Double Quotes
i = i + 1
Char = Mid(strInput, i, 1)
j = j + 1
strWorkArray(j) = ""
While Char <> Chr(34)
strWorkArray(j) = strWorkArray(j) & Char
i = i + 1
Char = Mid(strInput, i, 1)
Wend
ElseIf Char <> Chr(44) Then
j = j + 1
strWorkArray(j) = ""
While Char <> Chr(44) ' Comma
strWorkArray(j) = strWorkArray(j) & Char
i = i + 1
Char = Mid(strInput, i, 1)
Wend
End If
i = i + 1
Wend
End Sub
Could anyone point me to an Excel VBA routine or function that I can use in my own VBA code to split a CSV file where the file may have comma's imbedded in a field delimited by " " as well as fields not containing commas and so not surrounded by " ".
eg "ACRUX LIMITED",ACR,"Pharmaceuticals, Biotechnology & Life Sciences"
I've written the routine below but it is slow and I'd expect there is a better way - perhaps using an inbuilt Excel function.
Thanks,
Geoff
Sub ParseCSVRec(strInput As String)
Dim i As Long
Dim j As Long
Dim Char As String
i = 1
j = 0
While i <= Len(strInput)
Char = Mid(strInput, i, 1)
If Char = Chr(34) Then ' Double Quotes
i = i + 1
Char = Mid(strInput, i, 1)
j = j + 1
strWorkArray(j) = ""
While Char <> Chr(34)
strWorkArray(j) = strWorkArray(j) & Char
i = i + 1
Char = Mid(strInput, i, 1)
Wend
ElseIf Char <> Chr(44) Then
j = j + 1
strWorkArray(j) = ""
While Char <> Chr(44) ' Comma
strWorkArray(j) = strWorkArray(j) & Char
i = i + 1
Char = Mid(strInput, i, 1)
Wend
End If
i = i + 1
Wend
End Sub