How To Remove Line Breaks in CSV

Hi,

The line feed is already in your text file.

I've pasted row 322:323 in A1.

In A2: =FIND("production line.    Throttle",A1)

In A3: =CODE(MID(A1,A5-1,1)) => 10

Looking into your file with Crimson editor:

And you see also the line feed character.

It gets even worse when you scroll down to row 1061:

I am afraid there is not very much we can do.

JP Ronse wrote:

The line feed is already in your text file. [....] I am afraid there is not very much we can do.

Well, it is not difficult to write a VBA procedure that replaces all LF characters with spaces.

Download "fileReplaceLF.bas" (click here) [1] and import it into an instance of Excel.  If the original path name is c:\...\foobar.txt, the VBA procedure creates a modified copy with the new path name c:\...\foobar NEW.txt.

It would be better if "foobar NEW.txt" retained the LF characters, and the VBA procedure "simply" added double-quotes around the tab-separated text, if necessary.  Actually, it is not so simple; but it is doable.

However, it is not worth the effort because the Excel Text Import Wizard [2] violates the principle of idempotence:  it does not interpret cells of that form correctly, even though that is how cells with LFs are output when Excel creates a tab-separated text file.

Alternatively, it would be possible (but more difficult) to rejoin lines in a copy of the CSV file, adding double-quotes around the joined text.

Excel does interpret that form as intended when we open the CSV file directly using Excel.  (The Text Import Wizard still misinterprets it.  Sigh.)

But the implementation would need to be specific to the form and/or content of the example CSV file.  It would have to be carefully customized for other errant CSV file.  So again, it does not seem worth the effort.

-----

[1] https://app.box.com/s/j8clmdj2dppw9lxe255t18x5029us0je

[2] I am using Excel 2007 now.  If someone tests a later version of Excel, and the Text Import Wizard is idempotent, I might consider developing the appropriate replacement procedure, if no one else does.  To test idempotence, put the formula ="line1" & CHAR(10) & "line2" into A1, save as a tab-separated text file, then import the file into a new worksheet.  In Excel 2007, the result is line1 [sic] in A1 [3] and line2" [sic] in A2.

[3] The Text Import Wizard displays "line1 [sic] initially.

joeu2004 wrote:

Well, it is not difficult to write a VBA procedure that replaces all LF characters with spaces.

Your code works if it is a DOS based file with 0D0A (CR-LF) as EOL but will fail on Unix/Linux based files that have only LF as EOL. Have saying that, I want also to state that there is nothing wrong with work-arounds as long as one is aware of the limitations but imho it is always better to correct it at the source if possible.

About your test, idem with XL 2010.

[2] I am using Excel 2007 now.  If someone tests a later version of Excel, and the Text Import Wizard is idempotent, I might consider developing the appropriate replacement procedure, if no one else does.  To test idempotence, put the formula ="line1" & CHAR(10) & "line2" into A1, save as a tab-separated text file, then import the file into a new worksheet.  In Excel 2007, the result is line1 [sic] in A1 [3] and line2" [sic] in A2.

[3] The Text Import Wizard displays "line1 [sic] initially.

I'm using Excel 2013 and followed your above instructions. I got "line1" in A1 and "line2" in A2.