Write CSV file with double quotes for particular column not working

Question

I'm trying to write a CSV file using Python's csv writer.

One of the column values is enclosed in "" [double quotes] e.g.: 'col1' 'col2' "test". When I open the file in Wordpad, the word test is expected as "test" but the actual result is """test""".

Can someone please guide for this issue?

Sample snippet of my try out:

csvReader = csv.reader(iInputFile)
writer = csv.writer(open('one_1.csv', 'wb'), delimiter=',', lineterminator='\r\n')

for row in csvReader:
     rawRow = []
     rawRow.append('31-7-2014') #Appending Date
     rawRow.append(row[0])   #Appending data
     rawRow.append('\"'+'test'+'\"') 
     writer.writerow(rawRow)

Galimatias

In case it's not obvious, that is completely correct behavior; to include the literal double quotes in CSV data, you need to quote the field (add double quotes around the value) and escape the literal double quotes in the value by doubling them. – Scopula

Answer 1

try with this one

f_writ = open('one_4.csv', 'wb')
csvReader = csv.reader(iInputFile)
writer = csv.writer(f_writ, delimiter=',',
                lineterminator='\r\n',
                quotechar = "'"
                )

for row in csvReader:

    writer.writerow(['31-7-2014',row[0],'\"text\"'])

f_writ.close()

also i find very useful this link http://pymotw.com/2/csv/, there are a lot of exemples

Struthious

Thanks for the link, really useful. Your code works exactly as I want. But can you please explain it for my better understanding. Thanks a lot. :) – Galimatias

Answer 2

More generally, if you need to save your CSV file with every column within double quotes

import csv
with open('Data/with_quotes.csv', 'wt') as fr:
    csv_writer = csv.writer(fr, quoting=csv.QUOTE_ALL)
    
    with open('without_quotes.csv', 'rt') as f:
        csv_reader = csv.reader(f, skipinitialspace=True, doublequote=False)

        for row in csv_reader:
            csv_writer.writerow(row)

If you happen to have pandas in your workflow, you could also just do as such

import pandas as pd
unquoted = pd.read_csv(my_upload)
quoted= unquoted.to_csv(quoting=csv.QUOTE_ALL, index=False)

Clouet

Answer 3

Probably you need to play with parameters quoting and escapechar.

For example, modified code

csvReader = csv.reader(iInputFile)
writer = csv.writer(open('one_1.csv', 'wb'), delimiter=',', lineterminator='\r\n', quoting=csv.QUOTE_NONE, escapechar='\\')

for row in csvReader:
     rawRow = []
     rawRow.append('31-7-2014') #Appending Date
     rawRow.append(row[0])   #Appending data
     rawRow.append('\"'+'test'+'\"') 
     writer.writerow(rawRow)

will produce output like that:

31-7-2014,'col1',\"test\"

Nomarch

Thank you for the reply, but I just want string in double quotes. No any other character,not even space. – Galimatias

Answer 4

As far as I can tell from the accepted answer from @GiovanniPi, is that the default is

quotechar= '"'

Because the expected output already has double quotes, this has to be changed to:

quotechar = "'"

I am not sure what you would do if you needed to have both single and double quotes as quotechar requires a 1-character string

Kwangchow

Answer 5

Here's an uber-simplified example using a helper function:

def quote(field):
    return f'\"{field}\"'

writer = csv.writer(csvfile, delimiter=',', quotechar="'")

Then just wrap every item you want to quote in the function

writer.writerow("_id", quote("John"), quote("Doe"), "user")

Gaberones