VBA reading in text file and parsing by line breaks (carriage returns)

[kkmoslephour]

Hi guys, I am a little stuck here. I am trying to read in a text file and parse it by line breaks however I cannot read the content of the parsed line breaks, it just shows up as 'Empty' when I watch them.

Option Explicit

Sub ParseText()

Dim myFile As String, text As String, textline As String, Lastrow As Integer, i As Integer, Dim data() As String

myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")


If myFile <> "False" Then
    MsgBox "Opening " & myFile
Else
    MsgBox "Invalid File Path!", vbCritical, vbNullString
    Exit Sub
End If

Open myFile For Input As #1

Do While Not EOF(1)
    Line Input #1, textline
    data = Split(textline, vbCrLf)
    text = text & textline
    MsgBox textline
Loop

However when I parse by space " " using

[COLOR=#000000][FONT=Arial][B]data = Split(textline, " ")[/B][/FONT][/COLOR]

I can sucessfully see the contents of each array in my console window. So i am wondering why I cannot parse by line breaks.

 

[diddi]

Line Input # parses by CrLf already using those chars as the delimiter for the input.

its like inputting Tab delimited and asking where the tabs are.

 

[kkmoslephour]

Ok, but when I delete

[COLOR=#333333]data = Split(textline, vbCrLf)[/COLOR]

and output it to a MsgBox I don't get the first line of my text file, but I get the whole text file outputted into my "textline" variable. Do you know why?

 

[kkmoslephour]

The weird thing is when I open the txt file in notepad++ everything is nicely formatted by the line breaks, however when I open the file in notepad there are no line breaks and everything is just one whole line, so I am suspecting that the "Do While Not EOF(1)" reads the whole file as one line? how would I fix this?

 

[kkmoslephour]

anyone?

 

[John_w]

Ok, but when I delete

[COLOR=#333333]data = Split(textline, vbCrLf)[/COLOR]

and output it to a MsgBox I don't get the first line of my text file, but I get the whole text file outputted into my "textline" variable. Do you know why?

Maybe the lines aren't delimited by vbCrLf, but by vbCr (or vbLf) in which case try:

    Open myFile For Input As #1
    Line Input #1, textline
    data = Split(textline, vbLf)
    Close #1

 

[kkmoslephour]

When I do that and output 'data' to MsgBox it gives me an error saying that type mismatch one that line. Am I declaring data as the wrong type?

 

[kkmoslephour]

Oh nvm it works now, I had to output to msgbox as data(0) to see the first line in the array

 

[erdwaters]

But what if the file is 50M with over a 130,000 lines of text that are confirmed to end with LF (notepad++) rather than CR or CR+LF?

 

[Rick Rothstein]

But what if the file is 50M with over a 130,000 lines of text that are confirmed to end with LF (notepad++) rather than CR or CR+LF?

I am not sure if the Transpose function will choke on that number of lines of text or not, but give this a try and see...

Sub LoadLineFeedDelimitedFile()
  Dim FileNum As Long, TotalFile As String, Lines() As String
  FileNum = FreeFile
  Open "[COLOR="#FF0000"][B]C:\Temp\BigTextFile.txt[/B][/COLOR]" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Lines = Split(TotalFile, vbLf)
  Range("A1").Resize(UBound(Lines) + 1) = Application.Transpose(Lines)
End Sub

Change the red text to your actual path-filename.