CODEWITHSUNDEEP
pythoncsv
ariel
ariel

Reputation: 211

Write text with comma into a cell in CSV file using python

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'

Upvotes: 21

Views: 60310

Answers (3)

Andrzej Doyle
Andrzej Doyle

Reputation: 103797

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.

Upvotes: 9

Cloud
Cloud

Reputation: 3229

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'

Upvotes: 6

Dominic Rodger
Dominic Rodger

Reputation: 99771

Use the proper CSV writers:

>>> import csv
>>> spamWriter = csv.writer(open('eggs.csv', 'wb'))
>>> spamWriter.writerow(['Spam', 'Lovely, Spam'])

Outputs:

Spam,"Lovely, Spam"

Upvotes: 30

Related Questions