Splitting Comma Delimited files

[egstatus]

Hi all,

I have a comma delimited file that I need to read into an array. I am running into problems when one or some of the fields have an embedded comma.

For Example:
Record 1
WHCO,AM,"68,112-A",117,HOUSTON,32775,AK056008,,001,07
Record 2
WLGA,FM,19265-1,063,"COLUMBUS,GA",20000,AF012061,,001,08

In this case I want the third field of the array to be "68,112-A" for record 1, but when I use the split function I get 68 as the third field and 112-A as the fourth field.

Same problem for record 2, I want "CULUMBUS, GA" to be as the fifth field not just "COLUMBUS".

Quotes are placed on the field that contain an actual comma as part of the field.

here is my code:
varLongString = Split(LineIn(rcount), ",")

How do I solve this problem? Any help is apreciated.

Thanks

 

[gmmastros]

I suggest you use ADO to open the text file. It will correctly parse the fields for you. I took the data you had and saved it in a file on my computer named C:\sampledata.txt

Then I wrote this code...

Option Explicit

Private Sub Command1_Click()
    
    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim i As Long
    Set DB = CreateObject("ADODB.Connection")
    DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[!]c:\[/!];Extended Properties=""text;HDR=[!]No[/!];FMT=Delimited"""
    Call DB.Open
    
    Set RS = CreateObject("ADODB.Recordset")
    Call RS.Open("Select * From [!]SampleData.txt[/!]", DB)
    While Not RS.EOF
        For i = 0 To RS.Fields.Count - 1
            Debug.Print RS.Fields.Item(i).Value
        Next
        Debug.Print "------------- Next Record ----------------"
        RS.MoveNext
    Wend
    RS.Close
    Set RS = Nothing
    DB.Close
    Set DB = Nothing
   
End Sub

You will need a reference to ADO, so...

click Project -> References
Scroll to 'Microsoft Active X Data Objects 2.x Library' and select it.

If your data file has a row that contains field names, then set Hdr=Yes in the connection string.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Swi]

I would use ADO:

Private Sub Command1_Click()
' Declare variables
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim PathToTextFile As String
Dim CSVFileName As String
Dim i As Integer

' Initialize variables
PathToTextFile = "C:\"
CSVFileName = "TEST.CSV"

' Open the connection to the CSV file
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & PathToTextFile & ";" & _
        "Extended Properties=""text;HDR=YES;FMT=Delimited"""

' Read the data into a recordset from the CSV file
rs.Open "SELECT * FROM " & CSVFileName, _
    conn, adOpenStatic, adLockReadOnly, adCmdText
Do
    ' Loop through the field data
    For i = 0 To rs.Fields.Count - 1
        MsgBox "FIELD NAME: " & rs.Fields(i).Name & vbNewLine & _
        "FIELD VALUE: " & rs.Fields(i).Value
    Next
    rs.MoveNext
Loop Until rs.EOF

' Clean up objects
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

' Prompts the user of completion
MsgBox "Done!", vbInformation
End Sub

Swi

 

[Swi]

Sorry George, posted too late.

Swi

 

[HughLerwill]

egstatus,

If you'd rather stay with native vb6 string handling.

Sub Caller
Dim TextArray4Record as Variant
Dim CurrentRecordText as String

'your code to get text from current record into CurrentRecordText

TextArray4Record = SplitRecord(CurrentRecordText)


end sub

Private Function SplitRecord(text As String) As Variant

Dim InQuotes As Boolean
Dim i As Integer
Dim v As Variant

InQuotes = False
For i = 1 To Len(text)
a$ = Mid$(text, i, 1)
If a$ = Chr$(34) Then
InQuotes = Not InQuotes
End If
If InQuotes And a$ = "," Then Mid$(text, i, 1) = "*" 'where * may be equal to any character which is never likey to occur in the file
Next

v = Split(text, ",")
For i = 0 To UBound(v)
v(i) = Replace(v(i), "*", ",")
Next

SplitRecord = v

End Function

regards Hugh

 

[egstatus]

Swi
Thanks for the help everyone has provided me with.

I tryed the code, and the invoice number "68,112-A" being returned by the record set is blank for the second record, which is the fourth field. The first line contains the field names. It seems like whenever there is a '-' inside the field the field value is returned as blank, is there something I am missing?

Here are some records [7 records]:

CALLLETTER,BAND,DATE,INVOICE,EST,ADVERTISER,PROD,CODE,CODE1,AMOUNT,BATES,CDNUMBER,PAGES,MOS
KPLA,FM,073006,1000130000,52,SIX FLAGS,ST LOUIS,VSL,GEN,00000462500,AK056004,,002,07
WHCO,AM,081606,"68,112-A",117,ST LOUIS DISTR FDA,CAR,ZLD,CAR,00000032775,AK056008,,001,07
WHCO,AM,081606,"68,112-D",117,ST LOUIS DISTR FDA,CAR,ZLD,CAR,00000032775,AK056011,,001,07
WIBW,FM,082706,27165,10,BURGER KING,3 OCLOCK VALUE MEAL,BV4,OVM,00000264900,AK060005,,006,08
WIAL,FM,082706,22004,87,BURGER KING,BREAKFAST,BV4,BKF,00000286400,AK060012,,004,08
WLGA,,082706,19265-1,063,DOMINO'S PIZZA INC,"COLUMBUS,GA",DPI,CGA,00000020000,AF012061,,001,08
WLGA,,082706,19265-1,063,DOMINO'S PIZZA INC,"COLUMBUS,GA",DPI,CGA,00000020000,AF012061,,001,08

Thanks again

 

[gmmastros]

egstatus,

Using your data, I tested my code and swi's. both appeared to work fine with the data that is causing you problems. Can you show the code you are using?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[egstatus]

Here is the code I am using, strFileName contain the path for the file I am checking. and I am stopping at rec 248 which is the record that contains a blank invoice.
Is there a way that I can send the file I am using? It contains 493 recods as an attachment.

Public Sub ReadTextFileIn(strFileName As String)
' Declare variables
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim PathToTextFile As String
Dim CSVFileName As String
Dim i As Integer
Dim intRecCount As Integer
' Initialize variables
PathToTextFile = "C:\"
CSVFileName = strFileName

' Open the connection to the CSV file
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & PathToTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

' Read the data into a recordset from the CSV file
rs.Open "SELECT * FROM " & CSVFileName, _
conn, adOpenStatic, adLockReadOnly, adCmdText
Do
' Loop through the field data
intRecCount = intRecCount + 1
If intRecCount = 248 Then
For i = 0 To rs.Fields.Count - 1
MsgBox "FIELD NAME: " & rs.Fields(i).Name & vbNewLine & _
"FIELD VALUE: " & rs.Fields(i).Value
Next
End If
rs.MoveNext

Loop Until rs.EOF

' Clean up objects
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

' Prompts the user of completion
MsgBox "Done!", vbInformation
End Sub