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

kkmoslephour

New Member
Joined
May 8, 2014
Messages
27
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.


Code:
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
Code:
[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.
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
Ok, but when I delete
Code:
[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?
 
Upvote 0
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?
 
Upvote 0
Ok, but when I delete
Code:
[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:
Rich (BB code):
    Open myFile For Input As #1
    Line Input #1, textline
    data = Split(textline, vbLf)
    Close #1
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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...

Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top