Excel adds double quotes on csv export

Question

I am experiencing a weird unknown behavior in excel. The sheet I want to export to a csv file consists of 4 columns with data like this:

site.aspx|de|lblChanges.Text|some text that will be used somewhere

Now what happens is, if the last column containg the text has doublequotes in it, Excel adds another double quote to it for every double quote already in it.

Example:

site.aspx|de|lblChanges.Text|some text that will used somewhere <a href="/clickety.aspx">here</a>

Gets transformed into

site.aspx|de|lblChanges.Text|"some text that will used somewhere <a href="/clickety.aspx">here</a>"

Notice the extra doublequotes at the beginning and the end, which clearly should not be there. this data gets inserted in a database and used as text resources for globalization. If I render a literal control with those extra double quotes the functionality breaks.

How can I supress this behavior in Excel?

Answer 1

Quotes are standard for CSV when fields include spaces (as in your case).

Normally, you would write all your CSV with quotes (and escape the quotes you want to keep with TWO quotes ""):

"field 1", "field 2", "field with ""escaped"" quotes"

Here is a complete explanation including specific references to Excel handling: http://www.csvreader.com/csv_format.php

But it might be difficult to get what you want if you don't control Excel exporting, and you don't control the importing program. You might have to tweak the text file in the middle of the process.

Answer 2

This is well documented and is actually expected behaviour. However, getting round it seems tricky, and I can think of only work arounds.

The work around appears to be open the exported file and Find and Replace the quote marks with nothing (to remove the quote marks).

If you need more control (eg, you may want the quote marks in some occaisons) then you have to do it manually, or hack it - add a unique keyword where you want the " to exist (such as not using " but instead qwertquote (since this string is going to be unique you can find and replace it with a quote mark) or, write a utility app to do it for you since you have programming experience.

There is some code from http://www.mcgimpsey.com/excel/textfiles.html#csvwithquotes

Text files with no modification

This macro will output a text file without surrounding cells which have commas in quotation marks, or doubling quotation marks in the text:

Public Sub TextNoModification()
    Const DELIMITER As String = "," 'or "|", vbTab, etc. 
    Dim myRecord As Range
    Dim myField As Range
    Dim nFileNum As Long
    Dim sOut As String
nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
        Range("A" & Rows.Count).End(xlUp).Row)
    With myRecord
        For Each myField In Range(.Cells(1), _
                Cells(.Row, Columns.Count).End(xlToLeft))
            sOut = sOut & DELIMITER & myField.Text
        Next myField
        Print #nFileNum, Mid(sOut, 2)
        sOut = Empty
    End With
Next myRecord
Close #nFileNum

End Sub

Answer 3

From Wikipedia: http://en.wikipedia.org/wiki/Comma-separated_values

Fields with embedded commas or double-quote characters must be quoted.

1997, Ford, E350, "Super, luxurious truck"

and

Each of the embedded double-quote characters must be represented by a pair of double-quote characters.

1997, Ford, E350, "Super, ""luxurious"" truck"

So a csv file needs those double quotes to be escaped (by using another set of double quotes), because the double quote by itself denotes the boundaries of a field.