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 ?

Charles Duffy · Accepted Answer · 2013-06-24 14:40:53Z

4

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.

answered Jun 24, 2013 at 14:40

Charles Duffy

299k43 gold badges441 silver badges497 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

felix001 Over a year ago

Thanks, when I use the above code to create a CSV it still places all the text into seperate cells (??)

John Y Over a year ago

@felix001: This works. You are probably not following it carefully enough. Note that the writerow method accepts a sequence, which it splits up into separate cells. Here, Charles has given it a sequence with only one element, and that one element is a string which happens to include embedded newlines. If he had passed in a string directly (not within a list), then the string itself would have been the sequence that gets split up into cells (each containing one character).

Eric Finn · Accepted Answer · 2013-06-24 14:39:37Z

3

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.

answered Jun 24, 2013 at 14:39

Eric Finn

9,0853 gold badges36 silver badges43 bronze badges

Comments

flaviotruzzi · Accepted Answer · 2013-06-24 14:55:04Z

0

Use:

xyz = abc.replace('\n','\r\n')

answered Jun 24, 2013 at 14:55

flaviotruzzi

5474 silver badges13 bronze badges

Collectives™ on Stack Overflow

Converting Strings into Excel Friendly Format

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related