Line Input reads entire text file as a single record

[polarisking]

I suspect it has something to do with LF vs. CRLF recognition by VBA, but I'm hoping someone can help me understand how to read this file (attached) record by record. Right now, EOF is reached after the first Line Input command and the contents of strline = to the entire file's contents. Excel and Notepad parse the records just fine; VBA's not cooperating.

Sub test()
  Dim fd  As FileDialog
  Dim strOpenPath  As String
  Dim strSavePath  As String
  Dim intOpenResult  As Integer
  Dim strFinal  As String
  Dim strLine  As String
  
  Close #1
  
  Set fd = Application.FileDialog(msoFileDialogOpen)
  
  With fd
  .Filters.Clear
  .Filters.Add "All Files", "*.txt"
  .AllowMultiSelect = False
  End With
  
  intOpenResult = fd.Show
  
  If intOpenResult <> 0 Then
  strOpenPath = fd.SelectedItems(1)
  
  Open strOpenPath For Input As #1
  
  strFinal = ""
  
  While EOF(1) = False
  Line Input #1, strLine
  
  Wend
  
  Close #1
  
  strSavePath = Application.GetSaveAsFilename(FileFilter:="Text File" & _
  "(*.txt),*.txt", Title:="Save Location")
  
  If strSavePath <> "False" Then
  Open strSavePath For Output As #1
  Print #1, strFinal
  Close #1
  End If
  
  End If
End Sub

 

[Stevie]

Hiya Polar!
Your macro indicates you know what you are doing, so i'll paste a snippet of mine which you should be able to use to solve your issue.
Basically as far as I can tell, when you read in a file like this, Excel treats it like one long string, so you need to split by vbCrLf which is a carriage return combined with a line feed.

For Each selectedFile In .SelectedItems
    strCSV = ImportTextFile(selectedFile)
    initArray = Split(strCSV, vbCrLf)
    dim1 = UBound(initArray)
    dim2 = UBound(Split(initArray(0), delim))
    ReDim finArray(dim1, dim2)
    For Idx1 = LBound(initArray) ToUBound(initArray) - 1
        For Idx2 = 0 To dim2
            finArray(Idx1, Idx2) = Split(initArray(Idx1), delim)(Idx2)
        Next
    Next
    Sheets("Sheet1").Range("A" & fileNum).Resize(UBound(finArray), UBound(Application.Transpose(finArray))) = finArray
    fileNum = fileNum + 100
Next selectedFile

The code above then splits further by a delimiter specified earlier in the code and loads the results into an array for pasting. It is designed to be used with multiple files.

I hope this helps, let me know if you need any other help.

 

[Stevie]

Ok so I decided to give this a quick go when I had a few minutes, and I found it difficult to work out your line ending character in the provided file, so I thought I would save you some time and post my working solution if you haven't got round to it yet.

Sub test()
    Dim iarr As Variant
    Dim linenum As Integer
    Open ActiveWorkbook.Path & "\For Chandoo.txt" For Input As #1
    iarr = Split(Input$(LOF(1), 1), vbLf)
    Close #1
    For linenum = 0 To UBound(iarr) - 1
        Sheets("Sheet1").Range("A" & linenum + 1) = iarr(linenum)
    Next
End Sub

It turns out your line ending character is just a vbLf (line feed).
The code above loads the file, splits it into a 1D array called 'iarr' by the vbLf character, then places each element of the array into subsequent cells down the column A, hopefully giving the required output.
I'm sure if you want to do things to each line you can tweak the code above.

 

[Marc L]

Hi !

First demonstration opening a stand alone txt workbook :​

Sub Demo1()
       TXT = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If TXT = False Then Exit Sub
    Workbooks.OpenText TXT, , , xlDelimited, xlTextQualifierNone, Other:=True, OtherChar:="|", _
                       Fieldinfo:=Array(Array(1, 2), Array(2, 1), Array(3, 5)), DecimalSeparator:="."
End Sub

Second demonstration loading data directly in main workbook' sheet1
with From Text File function from Data menu (QueryTables) :​

Sub Demo2()
       TXT = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If TXT = False Then Exit Sub

    With Sheet1
        .UsedRange.Clear

        With .QueryTables.Add("TEXT;" & TXT, .Cells(1))
                   .AdjustColumnWidth = False
                  .PreserveFormatting = False
                        .RefreshStyle = xlOverwriteCells
             .TextFileColumnDataTypes = [{2, 1, 5}]
            .TextFileDecimalSeparator = "."
              .TextFileOtherDelimiter = "|"
               .TextFileTextQualifier = xlTextQualifierNone
            .Refresh False:   .Delete
        End With
    End With
End Sub

Last but not least, same way as Stevie but using
worksheet Convert function from Data menu (TextToColumns)
and with some extra columns formating :​

Sub Demo3()
         TXT = Application.GetOpenFilename("Text Files (*.txt), *.txt")
      If TXT = False Then Exit Sub
         FF% = FreeFile
    Open TXT For Input As #FF:  SP = Split(Input(LOF(FF), #FF), vbLf):  Close #FF

    If UBound(SP) > -1 Then
        Application.ScreenUpdating = False

        With Sheet1
            .UsedRange.Clear
            .Cells(1).Resize(UBound(SP) + 1).Value = Application.Transpose(SP)

            With .Cells(1).CurrentRegion
                .TextToColumns TextQualifier:=xlTextQualifierNone, Tab:=False, Other:=True, OtherChar:="|", _
                               Fieldinfo:=Array(Array(1, 2), Array(2, 1), Array(3, 5)), DecimalSeparator:="."
                .Columns(2).AutoFit
                Union(.Columns("E:F"), .Columns(9)).NumberFormat = "#,##0.00 "
                .Columns(5).AutoFit

                With Union(.Columns(11), .Columns(13))
                    .HorizontalAlignment = xlLeft
                           .NumberFormat = "_- 0.########;- 0.########;_- 0"
                End With
            End With
        End With
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[NARAYANK991]

Hi ,

There is a lot of information in the following links :

http://msgroups.net/microsoft.public.vb.general.discussion/vba-line-input-not-rec/102340

http://stackoverflow.com/questions/...rge-files-line-by-line-in-vba-please-critique

Narayan

 

[Stevie]

Hiya Marc L!
Great post, just thought i'd point out that you leave screenupdating turned off in your 3rd option, and I have had a few issues with forgetting I had turned it off before.
Thanks for the multiple solutions!

 

[Marc L]

Thanks Stevie !

And you're right for ScreenUpdating off in particular
in case of an abnormal process ending …

I have others ways but that's enough to say VBA is cooperating ! ​