6

This question already has an answer here:

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?

marked as duplicate by Ƭᴇcʜιᴇ007, Dave, Excellll, dav, bwDraco Jan 21 '15 at 22:13

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Which columns do you wish to export?? – Gary's Student Jan 20 '15 at 14:18
  • All of them, I just care about the double quote issue. – Marco Jan 20 '15 at 14:21
7

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.

  • 1
    except excel doesn't escape the quotes (unless his example wasn't from excel) – ratchet freak Jan 20 '15 at 16:25
3

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
  • 1
    I just played dumb and did the most obvious. Ctrl+A, Ctrl+C, Ctrl+V. Now I have a tab delimited file, but in the correct format. I guess modifying the import routine will be quicker then looking for a solution in Excel now. – Marco Jan 20 '15 at 13:58
  • Are you sure that preserves the file as a CSV ? – Dave Jan 20 '15 at 13:58
  • My import routine (LinqPad file in C#) does only care about the delimiter and it just run without complaint. To answer your concern: I might very well be stressing the term csv, but it does the job. However it doesn't solve my problem with Excel, so I'll keep this open. – Marco Jan 20 '15 at 14:04
  • I would guess the only elegant solution is something in VBa – Dave Jan 20 '15 at 14:09
  • @Serv: I've always found the copy-and-paste as tab separated option, followed by whatever post-processing is necessary, to be cleaner and have fewer surprises than running Excel's "smart" export routines. – Derrick Turk Jan 20 '15 at 16:35
2

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.