Writing to csv puts quotes around quotes and quotes around entire cell

user_2382321 0
Viewed 1139

I am trying to write an array to a csv but if the array cell has double quotes in it, it adds quotes like so:

example data in the array: The quick brown "fox" jumps

that same data in the csv: "The quick brown ""fox"" jumps"

If the first part is in quotes like so: "The quick" brown fox jumps, it does the same thing: """The quick"" brown fox jumps". So it is adding quotes around the part that has quotes and then adds quotes around the entire cell contents.

Here is my code that writes to the csv:

matrix = populateArray()
with open('phrases.csv', 'w', encoding='utf-8', newline='') as f:
    thewriter = csv.writer(f)
    for x in range(0,1):
        print(matrix[x])
        thewriter.writerow(matrix[x])

The output of print(matrix[x]) is:

['The "quick" brown fox jumps', 'Lorem ipsum']

So you can see the data is formatted properly directly before it gets written to the csv, but at some point during the actual writing of the csv, it adds the extra quotes as described above.

Any help is much appreciated. Thanks

csv python
2 Answers

This should do the trick:

with open('phrases.csv', 'w', encoding='utf-8', newline='') as f:
    thewriter = csv.writer(f, quoting=csv.QUOTE_NONE, escapechar='\\')
    for x in range(0,1):
        print(matrix[x])
        thewriter.writerow(matrix[x])

You need to specify that the writer shouldn't generate additional quotes (quoting=csv.QUOTE_NONE):

Dialect.quoting

Controls when quotes should be generated by the writer and recognised by the reader. It can take on any of the QUOTE_* constants (see section Module Contents) and defaults to QUOTE_MINIMAL.

And will probably need to specify escaprechar too:

Dialect.escapechar

A one-character string used by the writer to escape the delimiter if quoting is set to QUOTE_NONE and the quotechar if doublequote is False. On reading, the escapechar removes any special meaning from the following character. It defaults to None, which disables escaping.

user_71317570

This is correct as per the RFC-4180 standard: https://www.rfc-editor.org/rfc/rfc4180#page-2

To explain a bit: quotes are placed around fields that contain delimiters. A field that contains a comma would have quotes around it so indicate that the entire quoted text is the field, and the included comma is part of the value, and shouldn't be interpreted as a field delimiter. However, that means that quotes also become problematic, as how would you represent a field that started with a quote? You have to escape the quotes, which is done by doubling them up.

This processing is necessarily to be able to unambiguously read the input values back out with their embedded commas and quotes.

A correctly implemented CSV library should reproduce the original values that you wrote in, even though the representation within the CSV looks different.

user_1903710
Related