I want to write text with comma into a cell in CSV file.
Input
'1,2,3,Hello'
Output in CSV should be
'1,2,3','Hello'
Asked
Active
Viewed 5.5k times
3 Answers
28
Use the proper CSV writers:
>>> import csv
>>> spamWriter = csv.writer(open('eggs.csv', 'wb'))
>>> spamWriter.writerow(['Spam', 'Lovely, Spam'])
Outputs:
Spam,"Lovely, Spam"
Dominic Rodger
- 97,747
- 36
- 197
- 212
-
I got the next error : AttributeError: '_csv.writer' object has no attribute 'writer'. is there a way to do it without CSV module? – ariel Aug 13 '10 at 10:24
-
-1 Not opening csv output file in binary mode. Output on Windows: `'Spam,"Lovely, Spam"\r\r\n'` – John Machin Aug 13 '10 at 11:54
-
@John - errr - yeah, per the Python documentation - http://docs.python.org/release/2.6.4/library/csv.html#csv.writer – Dominic Rodger Aug 13 '10 at 11:56
-
@John - fair enough, changed. – Dominic Rodger Aug 13 '10 at 12:04
-
@Dominic: Presumably you are referring to the example. You haven't noticed the somewhat vague remark earlier: """If csvfile is a file object, it must be opened with the ‘b’ flag on platforms where that makes a difference.""". Windows is the platform where it does make a difference. Sound practice is to always use binary mode on both input and output files -- this makes the script or advice-to-newbie platform-independent. – John Machin Aug 13 '10 at 12:07
-
@John - am I right in thinking this doesn't apply in Python 3? (per http://stackoverflow.com/questions/1020053/writing-with-pythons-built-in-csv-module) – Dominic Rodger Aug 13 '10 at 12:34
-
@Dominic: Python 3.1: Reading: docs say must open file with `newline=''`. Writing: docs say nothing. Actuality is that Windows needs `newline=''` otherwise you get extra `\r`. – John Machin Aug 13 '10 at 21:11
9
This is not Python specific, but is to do with the CSV "standard".
If you want to write a control character as part of your value, you'll need to escape the value by surrounding it in double-quotes:
f.write('1,2,3,45,"The Next comma I want to write and not separate to another cell, so this sentence will be whole",6,7,8')
Edit: Although in practice, it will be a better idea to use the CSV writer interfaces as suggested by others. It's never a good idea to embroil yourself in the implementation details when there's a ready-rolled library that abstracts this away for you.
tripleee
- 175,061
- 34
- 275
- 318
Andrzej Doyle
- 102,507
- 33
- 189
- 228
-
You'll want to add a line terminator at the end; unlike `print`, the `write` method does not supply one for you. – tripleee Nov 04 '19 at 09:41
5
Each line of the file is a data record. Each record consists of one or more fields, separated by commas.
The basic idea of separating fields with a comma is clear, but that idea gets complicated when the field data may also contain commas or even embedded line-breaks.
CSV implementations may not handle such field data, or they may use quotation marks to surround the field. -- From https://en.wikipedia.org/wiki/Comma-separated_values
So you can use quotation marks to surround the each field text.
Suppose the input is ['1,2,3', 'Hello'], the output to CSV should be "1,2,3", "Hello", you can use codes below to achieve this.
>>> ",".join('"{0}"'.format(s) for s in ['1,2,3', 'Hello'])
'"1,2,3","Hello"'
But you will encounter problems when there are some special symbols such as ", \n and etc in text.
The python csv library has handled all the edge cases for you.
Write to file
Can use @Dominic Rodger answer.
>>> import csv
>>> spamWriter = csv.writer(open('eggs.csv', 'wb'))
>>> spamWriter.writerow(['Spam', 'Lovely, Spam'])
Write to string
From https://stackoverflow.com/a/9157370/5238892.
In Python 3:
>>> import io
>>> import csv
>>> output = io.StringIO()
>>> csvdata = [1,2,'a','He said "what do you mean?"',"Whoa!\nNewlines!"]
>>> writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
>>> writer.writerow(csvdata)
59
>>> output.getvalue()
'1,2,"a","He said ""what do you mean?""","Whoa!\nNewlines!"\r\n'
Some details need to be changed a bit for Python 2:
>>> output = io.BytesIO()
>>> writer = csv.writer(output)
>>> writer.writerow(csvdata)
57L
>>> output.getvalue()
'1,2,a,"He said ""what do you mean?""","Whoa!\nNewlines!"\r\n'
