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:

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
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

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

kkmoslephour

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

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,995
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
 

kkmoslephour

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

New Member
Joined
May 8, 2014
Messages
27
Oh nvm it works now, I had to output to msgbox as data(0) to see the first line in the array
 

erdwaters

New Member
Joined
Mar 27, 2015
Messages
1
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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:

Forum statistics

Threads
1,081,615
Messages
5,360,037
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top