fso.OpenTextFile / ts.Readline - Dealing with embedded Line Feeds

I am reading text files line by line using the fso.OpenTextFile / Textstream.Readline method to get row counts, make some line amendments and then write the lines back out to a new file.

The issue I have is that I often get text files with embedded linefeeds in some rows which is causing .Readline to end at that point and then start reading the rest of the line as a new line.

Is there any change or addition can add to this method so it will ignore any embedded linefeeds and correctly read until the end of the line?

The other method I was experimenting with was ADODB.stream, and with that .ReadText(adReadLine) works fine and seems to ignore the embedded linefeeds, but I've found ADODB to be slower the larger the files get.

Unable to upload a workbook at the moment, but hopefully that shouldn't matter in this case.

Hi, if you're reading the file line by line in a Loop then you could add a check to either remove linefeeds or carriage returns.
Can you upload a text file?

in vba you can also use Chr(10) or Chr(13)

Originally Posted by JEC.

Is the array in variable "a" how you expect it to be? I just used carriage return instead of linefeed.

Please Login or Register  to view this content.

Unfortunately, due to some potentially very large files I have to read in line by line rather than all at once to split. I may add add in a file size check for more manageable files and use this approach.

Originally Posted by Keebellah

Hi, if you're reading the file line by line in a Loop then you could add a check to either remove linefeeds or carriage returns.
Can you upload a text file?

Originally Posted by Keebellah

in vba you can also use Chr(10) or Chr(13)

That would be ideal (potential performance issues aside) if there is a way to do that, but I am finding that the line is split at the point of reading (ts.ReadLine). So I can't check it once it's been read as it then exists as two separate lines.


I've tried wrapping the .readline in a CLEAN / SUBSTITUTE functions but that has no effect. So it seems that it just reads until it hits just a single LF, then treats the rest of the line as a new line. I am assuming that ADODB.Stream must read until the CR/LF at the real end of the line. Sample text file attached - 3 lines, 2nd line has the LF. These are usually created when someone enters multiple lines within a single Excel cell (Alt+Enter). Running the below code will result in 4 lines within the collection.

Please Login or Register  to view this content.

Did you put the quotes around the line with the line feed?

And like this?

Please Login or Register  to view this content.

I forgot in posts #7 & 10 : quotes & doubles quotes are very not a concern …

If post #10 Binary way loading all in a Byte array is too heavy for the computer then the classic VBA way works as expected
if only the End Of Line of the source text file is CrLf :

PHP Code: 

Sub Demo00()
    Dim V, R%, L&, W%, S$
        V = Application.GetOpenFilename("Text files,*.csv", , "File to convert"):  If V = False Then Exit Sub
        R = FreeFile
        Open V For Input As #R
        L = InStrRev(V, "\")
        W = FreeFile
        Open Left(V, L) & "New " & Mid(V, L + 1) For Output As #W
    While Not EOF(R)
        Line Input #R, S
        Print #W, Replace(S, vbLf, " ")
    Wend
        Close #R, #W
        Application.Speech.Speak "Done!", True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by JEC.

This will be slow on large datasets because there is sheet interaction in every loop.

Not if you disable events, then the time loss is minimal

Originally Posted by EchoPassenger

Unfortunately, due to some potentially very large files I have to read in line by line rather than all at once to split.

So you choosed the slow way …

According to tests achieved years ago the fast way as described in post #10 uses the same post #3 way
to load all data in a Byte array in order to overwrite only single Line Feed chars with spaces in Binary mode,
25 times faster than the classic full VBA Binary way …

Originally Posted by Marc L

I forgot in posts #7 & 10 : quotes & doubles quotes are very not a concern …

If post #10 Binary way loading all in a Byte array is to heavy for the computer then the classic slow VBA way works as expected
if only the End Of Line of the source text file is CrLf :

PHP Code: 

Sub Demo00()
    Dim V, R%, L&, W%, S$
        V = Application.GetOpenFilename("Text files,*.csv", , "File to convert"):  If V = False Then Exit Sub
        R = FreeFile
        Open V For Input As #R
        L = InStrRev(V, "\")
        W = FreeFile
        Open Left(V, L) & "New " & Mid(V, L + 1) For Output As #W
    While Not EOF(R)
        Line Input #R, S
        Print #W, Replace(S, vbLf, " ")
    Wend
        Close #R, #W
        Application.Speech.Speak "Done!", True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by Marc L

So you choosed the slow way …

According to tests achieved years ago the fast way as described in post #10 uses the same post #3 way
to load all data in a Byte array in order to overwrite only single Line Feed chars with spaces in Binary mode,
25 times faster than the classic full VBA Binary way …

Thank you. That way is definitely a bit faster, however, I am finding that it leaves a 0kb copy of the file?

Also, related to a previous thread of mine, is there anyway to specify the file encoding as UTF-8 with this method?

Which way ?
If you are stating about my VBA demonstration Demo00 (Edit : maybe not the fastest but fast enough) :
not on my side as this procedure just creates a same size new file with space in place of single Line Feed character …

For the fast way according to posts #3 & 10 and for the UTF8 encoding it depends on the missing big text file attachment
like a crystal clear explanation of what you really mean with « embedded linefeeds » so
does that mean any single Line Feed within the text - single meaning not with a previous Carriage Return character - or
only Line Feed character within a string between double quotes or … ?

Edit : as in Binary mode the encoding stays the same or
if you need to convert an ASCII text file to UTF8 so yes ADODB.Stream is a slower way to go …
As guessing can't be coding !