Converting Strings into Excel Friendly Format

Question

I have a string of text. The string has multiple newline characters in it. I want to create a csv that includes this string so I can import it into excel. However because of this I believe I have to convert all the new lines to carriage returns and wrap the text in quotes.

However when trying to convert a small amount of text I get the follow results:

Before

>>> abc = "\nA\nB\nC\nD\nE\nF\nG\n\n"
>>> print abc

A
B
C
D
E
F
G

After

>>> xyz = abc.replace('\n','\r')
>>> xyz
'\rA\rB\rC\rD\rE\rF\rG\r\r'
>>> print xyz
G

Any ideas what I am doing wrong ?

Answer 1

Use the Python csv module, which already knows how to do this.

import csv
with open('xyz.csv', 'wb') as outfile:
  w = csv.writer(outfile)
  w.writerow(['a\nb\nc'])

After running this, you can inspect the output (the -A, on GNU cat, shows a representation of nonprintable characters):

$ cat -A xyz.csv 
"a$
b$
c"^M$

Note that you can also use cat -A to see why your prior implementation appeared to be doing the wrong thing.

Answer 2

Try writing that string to a file and looking at it in a text editor.

Your console is interpreting a carriage return as "return to beginning of line", so the A prints, then the B prints where the A was, etc, until G is printed where the F was.