Using MS Excel 2002 SP3 (on WinXP) I entered this formula in cell B1: I went through these steps to remove carriage return (CR, char(13)) and line feeds (LF, char(10)) in column A of an Excel file, replacing them with blanks. Maybe such a problem should ideally be solved from inside Excel ? Many thanks to pietzcker who posted all 3 expressions which I have just combined here into 1 OR expression.
The line-feeds in the CSV file without a preceding carriage return are displayed with this configuration setting as rectangles.
Using the second expression in the Perl OR expression is therefore normally enough to delete all line breaks in fields when the CSV file was opened with the configuration settings Never prompt to convert files to DOS format and Only recognize DOS terminated lines (CR/LF) as new lines for editing enabled at Advanced - Configuration - File Handling - DOS/UNIX/MAC Handling. "row 2 column 1 text,"row 2 column 2 text in line 1 LFtext in line 2",row 2 column 3 value CRLF "row 1 column 1 text in line 1 LFtext in line 2 LFtext in line 3",row 1 column 2 text,row 1 column 3 value CRLF Normally when a line break in a field in Excel is entered with ALT+RETURN and the Excel file is saved as CSV file, Excel will double quote this field and use a line-feed without preceding carriage return character as line break, whereas the row itself is terminated with carriage return + line-feed.
Please read also WikiPedia - Comma-separated values very carefully to understand the CSV file format. Row 2 column 1,row 2 column 2,row 2 column 3,row 2 column 4 Code: Select all row 1 column 1,"row 1 column 2 with a ""double quoted string""Īnd a line break",row 1 column 3,"row 1 column 4 containing also a ""double quoted string"" but no line break"
0 kommentar(er)
