4

How does one specify a cell to contain exactly one double-quote character in a TSV file so that it will be handled correctly when imported (as a spreadsheet) into Google Sheets?

(Note: when importing the file, Tab rather than Automatic must be specified as Separator Character in the import dialog; if I don't do this, the imported sheet has even more problems.)

I have tried all of the following, without success

"
'"
""
'"'

I also tried manually inserting a lone double-quote character in one cell of a Google Sheets, and then downloading the spreadsheet and inspecting the downloaded file. Frustratingly enough, the downloaded file shows a lone double-quote character (i.e. not escaped in any way) between the flanking tab characters.

BTW, this means, among other things, that Google Sheets cannot do the download-re-import round trip. E.g. if the spreadsheet I downloaded is re-imported (exactly as it was delivered by Google Sheets) into a Google Sheets (specifying tab as a separator character in the import dialog), the formatting is messed up; the lone double quote apparently has the effect of escaping the subsequent tab, so the import does not recognize it as a separator.

I stress that what I'm looking is for how to write the TSV so that it gets imported correctly into a Google Sheets. IOW, simply entering the double-quote manually into a Google spreadsheet is of no use here.

Improve this question

3 Answers 3

Reset to default
6

Try four double-quotes, e.g.

""""

According to Wikipedia there is no reliable standard, but some attempts have been made.

A (double) quote character in a field must be represented by two double quote characters.
Improve this answer
2
  • Amazing. I wouldn't have guessed this one in a million years. Three double-quotes is maybe understandable (as in "the outer quotes quote the middle one"), but four of them makes no sense to me. But it works! Thanks!
    – kjo
    May 14, 2012 at 0:43
  • @kjo 4 makes sense because the 1st opens the string 2nd and 3rd is the escaped quote and the 4th closes the string.
    – panzi
    Jun 28, 2016 at 18:24
1

I replaced the double quote in the original file with something unique, the concatenated word doublequote. Then I imported into Google Sheets and it went ok. Then I went to the Find dialog and entered doublequote. In the Replace dialog, just entering the " replaced correctly. Entering '" (a single quote followed by a double quote) also replaced correctly. Entering two double quotes, "", did not replace correctly, it replaced the word with the two double quotes. Before I was getting 8k cells with lots of aggregated cells, and now I got clean 17k cells as in my original.

Improve this answer
0

I just made a little nodejs tool to build a tsv that has fields contining double-quotes. Pasting the tsv into an empty google sheet would only work if i escaped the double-quotes by replacing them with two double-quotes. The answer above saying four double-quotes didn't work for me. Maybe things have changed since 2012?

E.g. the text...

She said "Hey that's my horse".

should be

She said ""Hey that's my horse"".

The javascript escaping code:

fieldtext = fieldtext.replace(/"/g, `""`)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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