Why not seperate lines? (Access 97 VBA)

Topic

New Reply

I am attempting to import this attached file using the following code:

Sub ImportTest()
    'Open the file to import
    Dim intFileNum As Integer
    intFileNum = FreeFile
    
    Open "C:ebill_R_Audit_11_20021210.txt" For Input As #intFileNum
    
    Dim strFromFile As String
    
    While Not EOF(intFileNum)
        Line Input #intFileNum, strFromFile
        Debug.Print "zzz"
        Debug.Print strFromFile
        MsgBox strFromFile
    Wend
    Close #intFileNum
End Sub

Why do I get only 1 msgbox with the full contents of the file and not 3 with 1 line each?

Reply | Quote

Replies

The problem is the text file.

The lines are not separated by carrige returns and line feeds (chr(13)+chr(10)). They are only separated by line feeds.(chr(10))

From the help on Line Input#:

---

The Line Input # statement reads from a file one character at a time until it encounters a carriage return (Chr(13)) or carriage return

Reply | Quote

I opened the data file in Word 2k and saved it back. Word replaced the linefeeds with cr/lf and the routine processed the file just fine.

Reply | Quote

That’s a great idea. I routinely use WordPad to open .txt files that Notepad cannot parse due to nonstandard line terminators. Word is better for the poster because it can be automated.

Reply | Quote

I ended up writing a work-around:

Sub ImportTest()
    'Open the file to import
    Dim intFileNum As Integer
    intFileNum = FreeFile
    
    
       Open "Myfile.txt" For Input As #intFileNum
    
    Dim strFromFile As String
    
    While Not EOF(intFileNum)
        Line Input #intFileNum, strFromFile
        Dim intLength
        For intLength = 1 To Len(strFromFile)
            Dim strIndividualRecord As String
            If Asc(Mid(strFromFile, intLength, 1)) = 10 Or _
               Asc(Mid(strFromFile, intLength, 1)) = 13 Then
                'End of the record
                If Len(strIndividualRecord) > 0 Then
                    Debug.Print "-----------"
                    Debug.Print strIndividualRecord
                End If
                
                strIndividualRecord = ""
            Else
                strIndividualRecord = strIndividualRecord + Mid(strFromFile, intLength, 1)
            End If
        Next intLength
    Wend
    Close #intFileNum
    MsgBox "Done!"
End Sub

Thanks for all the suggestions!

Reply | Quote

See my Post #138639 for some suggestions on using the FileSystemObject methods to parse text files. Of course, there is much more here in the Lounge on that…

Afterthought: Because you are using Access 97, which is VBA 5.x, you do not natively have the Split() function, which is the perfect tool for turning a blob of delimited text into a well-ordered array. But I wonder if it’s possible to tap the Split() function through the Scripting runtime? That would be sweet.

Reply | Quote