Does anyone have any comma sense out there?

[JimMuglia]

Greetings,

Attached is a zip file containing a csv file. I am writing a program that needs to parse the file for importing its items to a database.

In toggling through the code I am using, for line input, instead of givng the whole line it sometimes gives the last word or number of a line, preceeded by a bunch of commas.

Here is what I am asking:
In parsing a .csv file, in the context of the "while not eof() loop", how can I input each "line", soas to make sure I see all the information and can parse it?

Right now, the "tmp" values are missing some stuff that, I think should be appearing? In other words, I am not capturing all the data on a line with the Line Input command.

Do While Not EOF(1)
Line Input #1, tmp
Loop

Can someone give me some direction on this (i.e. the best way to parse a .csv file, like the one attached)?

Thank you,
Jim

[MartinLiss]

Are you moving all the data to the database? If so you can let Access do it for you via VB code.

[NotLKH]

Generally, I like useing Binary, ie.. Something like:
{assuming your line seperators ar vbCrLf}

Private Type MY_DAT
        field1 As String
        field2 As Integer
        field3 As Long
        filed4 As String
    End Type
 
    Dim MyRecs() As MY_DAT
 
Private Sub Command1_Click()
    Dim MyF As Integer
    Dim MyI As Long
    Dim MyJ As Long
    Dim MatchesSoFar As Long
    
    Dim MyStr As String
    Dim MyStuff As Variant
    Dim MyStuff2 As Variant
    
    Dim MyNumberOfFields As Integer
        MyNumberOfFields = 3 '   1 less than the total, since split returns a 0 based array
        MyF = FreeFile
        Open yourfile For Binary As #MyF
            MyStr = Space(LOF(MyF))
            Get #MyF, , MyStr
        Close #MyF
        MyStuff = Split(MyStr, vbCrLf, -1, vbTextCompare)
        ReDim MyRecs(UBound(MyStuff))
        For MyI = 0 To UBound(MyStuff)
            MyStuff(MyI) = Trim(MyStuff(MyI))
            If MyStuff(MyI) <> "" Then
                If InStr(1, MyStuff(MyI), ",", vbTextCompare) <> 0 Then
                    MyStuff2 = Split(MyStuff(MyI), ",", -1, vbTextCompare)
                    If UBound(MyStuff2) = MyNumberOfFields Then
                        MyRecs(MatchesSoFar).field1 = MyStuff2(0)
                        MyRecs(MatchesSoFar).field2 = CInt(MyStuff2(1))
                        MyRecs(MatchesSoFar).field3 = CLng(MyStuff2(2))
                        MyRecs(MatchesSoFar).field4 = MyStuff2(3)
                        MatchesSoFar = MatchesSoFar + 1
                    End If
                End If
            End If
        Next MyI
        ReDim Preserve MyRecs(MatchesSoFar - 1)
 
End Sub

-Lou

[jim mcnamara]

dim tmp() as string, big as string
dim row() as string
open "myfile.csv" for input as #1
big=input(lof(1),1)
close #1
tmp=Split(big,VbCrLf)
for i = 1 to ubound(tmp)
       row=Split(tmp(i),",")
       for j=1 to ubound(row)
             .............. each row(j) is a cell
      next j
      Erase row
next i

[joltremari]

I'm not exactly sure if I'm clear on what your saying, but, if you've been viewing this file in excel you should try viewing it in notepad then you'll see where those commas followed by one entry at the end. Lines 3, 4, & 5 are like that. Because this is saved as a .csv file it defaults to excel, so therefore if you type one value in the last column all the columns before it will still get seperated by commas even though they have no values. (lines 3,4,&5 in your file, also lines 1 & 2.)

Also instead of getting each line by a while not eof loop you should maybe try loading each line into an array, like so:

Dim MyLine() As String
 
Open [I]filename[/I] for Input As #1
    MyString = Split(Input(LOF(1), 1), vbCrLf)
Close #1
 
'Then you can access each line like so:
 
MyString(0) 'Line 1
MtString(1) 'Line 2  and so on....
 
'Or you could loop through them...
 
For i = 0 to Ubound(MyLine)
        'Do something with the line here
        'Like with another array
    strTemp = MyLine(i)
    MyNewLine = Split(strTemp, ",")
        'Or what ever you need to do here
Next i