2

I have a CSV file which is not properly formatted because one of the columns contains HTML code which use the same character as the Text Qualifier: the double quote.

Example:

"col1","col2","col3"
"01","text","<html><tag attrib="true">"

I decided to use another character as text qualifier, the backtick ` which is never present in my original file.

My data are like this now:

`col1`,`col2`,`col3`
`01`,`text`,`<html><tag attrib="true">`

Now my CSV is totally valid, but in Excel (2013) I cannot just change the Text Qualifier, it allows me only ", ' or {none}

Is there any way to edit this list to add a new text qualifier?

I finally found another way to import it but I am just wondering why the Text Qualifier box is not editable.

3
  • What do you mean? When you import text to columns you can specify the delimiter. Mar 26, 2014 at 17:18
  • @Raystafarian I have no problem with delimiter (between columns) it is the text qualifier I cannot change (only the three values mentioned)
    – рüффп
    Mar 26, 2014 at 17:25
  • Why not change it to text and use the text import? Or is that what you did already? I don't know of a way to change the default 3 options for text qualifiers. Mar 26, 2014 at 17:29

1 Answer 1

3

I don't think you can modify the qualifiers list. However, you can escape quotes by doubling them. Like so:

"01","text","<html><tag attrib=""true"">"

This will import correctly into Excel:

enter image description here

Even if you cannot manipulate the CSV more than changing the qualifier, you could do a mass find-replace on your existing CSV in notepad to get it to import correctly:

  1. Create the CSV with backtick text qualifiers as you have already done.
  2. Find-Replace all double quotes with double double quotes.
  3. Find-Replace all backticks with double quotes.
  4. Open with Excel.
3
  • In fact I made something very similar because the html attributes separator (name="value") can be single quote as well (my preferred way: name='value') then for me it is more a workaround than a Excel solution.
    – рüффп
    Mar 26, 2014 at 22:46
  • I read as well this post mentionning the sep=, in the first line of the CSV and I was thinking to get a similar code for the Text Qualifier but did not find anything on Internet.
    – рüффп
    Mar 26, 2014 at 22:49
  • Accepted as this workaround seems to be the best alternative.
    – рüффп
    Nov 27, 2015 at 9:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .