Hi everyone!
I am trying to import a text file into excel (2007). The file was exported from a C# text box and it contains linebreaks. Although when I import it (with the text import wizard that comes with excel), the linebreaks disappears completely. I would prefer not to have to write a VBA file and place in an excel file to run but instead change this with a neat method in C#, before it turns the text box data into a txt file. Is this possible in any way?
In excel a line break within a cell is encoded as ascii code 10 (i.e. \n) (determined through the handy use of the macro recorder and inspection of the generated VBA). I think the 'disappearance' of new lines probably is a result of you're C# emitting \n\r, so you might try doing a global replacement of "\r" with "" in your C# code before outputting it.
I figured this out. If you put quotes around text any embedded line feeds (ASCII 010) will be imported into Excel as embedded line feeds. In other words, these line feeds will not cause the text to split across Excel rows.
Try it. Create two files in Notepad.exe. In the first terminate the first line by pressing Alt-0010:
Test line 1 terminated with alt-0010
Test line 2
In the second, begin lines with " and terminate with ". For the first line insert an Alt-0010 just before the ":
"Test line 1 terminated with alt-0010 prior to the quote"
"Test line 2"
Now import both into Excel and see the difference.
See IETF RFC 4180 for more information
Thanks for the help! I tried starting the text with \" and finish with \" but when I go to excel, I get each line in a separate cell and my hopeful plan was to get all the text in one single excel cell.