How to replace new line character with csv newline character

Question

I need to import a multi-line string into a single cell of CSV

string message = "This is 1st string.
                  This is 2nd string.
                  This is 3rd string."

When I try to import string into CSV it splits up in multiple rows. If I remove new line chars from "message" the string is added as single line. I need to add message as it is into a single cell (multi-line into single cell).

I tried following codes:

var regex = new Regex(@"\r\n?|\t", RegexOptions.Compiled);    
message= regex.Replace(message, String.Empty);
char lf = (char)10;
message= message.Replace("\n", lf.ToString());

But the message is added to multiple rows in CSV.

When you say "CSV newline character", it makes it sound as if you think there's some dedicated newline character used for CSV files, to make a distinction from the regular system newline character(s)? Such a character doesn't exist. Decent CSV readers will recognise that a text field starts with " and be able to detect newlines within the boundaries of the cell and treat them as part of the cell. — ProgrammingLlama, Mar 03 '20 at 07:34
Well, "CSV" stands for "comma separated values". Since your text does not contain commas, it should end up in the same cell if you choose e.g. a comma as the divider. To be more concrete: You do not specify how you determine " message is added to multiple rows in CSV". Presumably you import it in Excel? Then take a look at the options in Excel's import dialogue. Alternatively, you could explore how to create *a true Excel file* from within your program; then you can split your data any way you like. .net interacts well with MS Office. — Peter - Reinstate Monica, Mar 03 '20 at 07:34
[Here](https://www.c-sharpcorner.com/UploadFile/bd6c67/how-to-create-excel-file-using-C-Sharp/) is an example if you have MS Office installed. [Here](https://stackoverflow.com/questions/151005/how-do-i-create-an-excel-xls-and-xlsx-file-in-c-sharp-without-installing-mic) is an example to create an Excel file with an open source library. If you want to create an Open Document Format file, which is probably a good idea for simple spreadsheets, take a look at AODL. [Here](https://wiki.openoffice.org/wiki/AODL_example_1) is a simple example. — Peter - Reinstate Monica, Mar 03 '20 at 07:51
In order to receive more helpful answers I suggest you specify your workflow. In particular: 1. How can the data look? Which whitespace and other special characters, commas, semicolons etc. can it contain? 2. How do you process or open the resulting CSV file? — Peter - Reinstate Monica, Mar 03 '20 at 07:55
Try the reverse to see what is wanted. Create an Excel sheet containing one cell which has those three lines. Export that sheet as a CSV. Close Excel. Open that exported CSV in Excel and check that it loads as required. Then build your new CSV in the same style. — AdrianHHH, Mar 03 '20 at 08:24

score 0 · Answer 1 · answered Mar 03 '20 at 08:02

You should try to remove the br tag (with <> ) in case it contains html, and maybe you can replace like:

.Replace(br tag between <>,"");
.Replace(Enviroment.NewLine,"");
And I´m not sure if the regex would replace one by one, but try this:
.Replace("\r","")
ant then:
.Replace("\n","")

Hope it works

score -3 · Answer 2 · answered Mar 03 '20 at 07:37

Using \r\n?|\t, you replace each carriage return, possibly followed by a line feed, but also every tab (the |\t means 'or a tab')

I guess you probably want to replace \r\n?\t*, i.e. a carriage return, possibly followed by a line feed, followed by an undetermined number of tabs, and then replace this whole block.

How to replace new line character with csv newline character

2 Answers2