How To Remove Line Breaks in CSV

When I try to import this ".txt" file as a CSV, I get breaks that separate a single row into two (or more) separate rows.

Link to CSV File: https://www.dropbox.com/s/64m4pdeztu77sb7/Engine%20Management%20Sensors.csv?dl=0

Link to TXT File: https://www.dropbox.com/s/ycw1qb7v0ag7f4f/Bosch%20Engine%20Management%20Sensors%20-%20PIES-PIESCustomFlat-PIES%20for%20PH%20Team_11192015_215415.txt?dl=0

Row 322 should be a single row but its records also spill down to 323 and so on.

I do know that this isn't a paragraph line break that's causing the records to break.

Please help.

|

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:

 

        04047025410236  0221504801      BBHK    7060             Bosch Coils Meets or exceeds OE performance; Benefits of Bosch original-equipment
quality ignition coils The right ignition voltage; Benefits of Bosch original-equipment
quality ignition coils :Lower fuel consumption          Ignition Coil   Bosch Ignition Coil -On- Plug   Benefits of Bosch original-equipment
quality ignition coils: The right ignition voltage Long-lasting spark 
Better engine running
 Lower fuel consumption
 Reduced exhaust emissions      Ignition Coil                                                                                                                   8.5827  2.6772  2.5984  0.5732                          511.657

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

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.


[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.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated July 30, 2021 Views 31,137 Applies to: