Excel adds extra quotes on CSV export

Question

I've recently created an application which adds items to a Database by CSV. After adding items I realized that lots of my values had extra quotes (") that weren't needed and this was messing up my ordering.

The problem is that when exporting to a CSV from Excel, Excel adds extra quotes to all of my values that already have a quote in them. I've shown the difference below:

Original Item: Drill Electric Reversible 1/2" 6.3A

Exported Item: "Drill Electric Reversible 1/2"" 6.3"

Note: the CSV export is adding three (3) extra quotes ("). Two on the ends, and one after the original intended quote.

Is there a setting I can change, or a formatting property I can set on the Excel File/Column? Or do I have to live with it and remove these quotes in my back-end code before adding them to the Database?

Solution 1

This is entirely normal. The outer quotes are added because this is a string. The inner quote is doubled to escape it. Same kind of thing you'd see in a SQL query for example. Use the TextFieldParser class to have tried and true framework code care of the parsing of this for you automatically.

Solution 2

That's standard.

The values within a CSV file should have quotes around them (otherwise commas and linebreaks inside a field may be misinterpreted).

The way to escape a quote within a field is to double it, just as you are seeing.

I suggest you read about the basic rules of CSV:

CSV is a delimited data format that has fields/columns separated by the comma character and records/rows terminated by newlines. Fields that contain a special character (comma, newline, or double quote), must be enclosed in double quotes. If a line contains a single entry which is the empty string, it may be enclosed in double quotes. If a field's value contains a double quote character it is escaped by placing another double quote character next to it. The CSV file format does not require a specific character encoding, byte order, or line terminator format.

(emphasis mine)

Comments

[chmullig]

The surrounding quotes are standard for a CSV, so I wouldn't worry about them. Just make sure your importer is accounting for enclosing quotes. The inside quote is Excel's way of escaping the double quotes. A bit annoying, I'll grant you but it's part of the standard.

[Wolf]

The problem is that when exporting to a CSV from Excel, Excel adds extra quotes to all of my values that already have a quote in them - without trying to import it, how do you know that this is a problem?

[Rick]

python3 with csv.reader is able to handle the extra double quotes well while plain open can't.

[Lando]

Hmm. This only happens in fields which already contain a quote. So if I do a replace " with "" in my code behind it should fix the problem? or would I have to replace the " with a """? EDIT: Wait, I'd have to replace it in the Excel file.

[Oded]

@Lando - use a CSV parsing library and make your life easy.

[Rick]

@Oded Any idea why Excel not double quoting single quote?