CODEWITHSUNDEEP
excelcsvnewlinecell
Dmitri K.
Dmitri K.

Reputation: 197

Creating a .csv file with newlines in single cell in a swift app

I have an issue which i have been at for two days now. I have read tons of documents online but maybe someone can help me here. So in short for those who dont want to read further is i require to import .csv documents to excel with lots of data which includes some cells with two or more lines in it.

So here is my first .csv file for example using utf16 little endian encoding:

Id;Order Date;First Name (Billing);Optional message;
106119;"2016-11-01 \r\n 22:34:00";John;;
94540;"2016-11-01 \n 22:37:33";John;;
94540;"2016-11-01 \r 22:37:33";John;;
94540;"2016-11-01 \n\r 22:37:33";John;;
94540;"2016-11-01
22:37:33";Damien;;

From what i read the last line or event the \n lines should be printed in single cell in two lines, but instead excel 15 on OS X recognises \n and goes to next row.

Here is pretty much the same file which also doesnt work:

"Id","Order Date","First Name (Billing)","Optional message",
"106119","2016-11-01 \r\n 22:34:00","John","",
"94540","2016-11-01 \n 22:37:33","John","",
"94540,"2016-11-01 \r 22:37:33","John","",
"94540,"2016-11-01 \n\r 22:37:33","John","",
"94540,"2016-11-01
22:37:33","Damien","",

I add ,\r\n to end of each line.

Can someone tell me what am i doing wrong, i am already out of ideas. Again using Office 15 on mac. Ask me an additional questions. I have tried the swift generated .csv file as well as a woocommerce exported file from wordpress which i edited manually to include newlines for cell. Neither work. I dont know what to do. If you do please send me the correct way by editing one of my examples. Thank you in advance.

Upvotes: 1

Views: 1202

Answers (2)

hoss24
hoss24

Reputation: 556

Add double quotes to string using \" at the beginning and end and use \n for each new line

var checklist = "\"first line" 
for item in category.items{
     //add new line
     checklist += "\n additional line"
}
checklist += "\"" //adds " at end

Upvotes: 0

dgorti
dgorti

Reputation: 1240

Dont have a mac but tried this on Windows. Excel can add a "new line" in a cell by using ALT+ENTER while using excel. In a Hex editor I saw that translate to a 0x0A character, which is Line Feed/or LF.

So when you generate a CSV file, you need to put an LF 0x0A character, not \r or \n because there is nothing that interprets those escape sequences.

To test this I wrote a simple C# code snippet

char lf = (char)0x0A;
System.IO.StreamWriter sw = System.IO.File.CreateText(@"x.csv");
        sw.WriteLine("Id,Name,DateTime,Message");
        sw.WriteLine("\"343\",\"34343\",\"12/12/2014{0}10:30\",\"dfsdfsfsdfsdf\"", lf);
        sw.WriteLine("\"343\",\"34343\",\"12/12/2014{0}10:30\",\"dfsdfsfsdfsdf\"", lf);
        sw.WriteLine("\"343\",\"34343\",\"12/12/2014{0}10:30\",\"dfsdfsfsdfsdf\"", lf);
        sw.WriteLine("\"343\",\"34343\",\"12/12/2014{0}10:30\",\"dfsdfsfsdfsdf\"", lf);
        sw.Close();

Then I opened the generated CSV file in Excel and I was able to get the "multi line" cell to appear. Hope this gives you some guidance on how to can get similar stuff to work on a Mac

enter image description here

Upvotes: 1

Related Questions