1

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.

Wain
  • 118,658
  • 15
  • 128
  • 151
Dmitri K.
  • 197
  • 1
  • 13

2 Answers2

1

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

dgorti
  • 1,130
  • 2
  • 12
  • 22
  • Thanks for the solution. I understand how this might work in theory but in Swift at least it seems that the hex characters are translated before writing to file, meaning that at the time i am writing the string to file the string is already translated to newlines in file, returning me to square 1. – Dmitri K. Dec 26 '16 at 09:31
  • I actually compiled this code and tried to insert into excel for mac and it didnt work. :( – Dmitri K. Dec 26 '16 at 16:33
0

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
hoss24
  • 556
  • 9
  • 22