2

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.

2

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.
  • 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 '12 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 '16 at 18:24
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, `""`)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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