CODEWITHSUNDEEP
csvutf-8google-sheets
sal i
sal i

Reputation: 351

How to enclose every cell with double quotes in Google docs spreadsheet

I have utf-8 data which I would like to save as CSV. My old version of Excel mangles utf-8, so I have to resort to using google's spreadsheet which handles utf-8 beautifully.

Some of my data have commas in them, so I must wrap every field of data in thCSVsv with double quotes. I have hundreds of lines, so it would take some time to do it manually and without error.

How can I accomplish this in google docs spreadsheet?

Upvotes: 35

Views: 101121

Answers (6)

dhaves
dhaves

Reputation: 37

i've achieved this by adding 3 ", then google sheets deleted 2 and showed one.

Upvotes: 1

TheTechnocrat
TheTechnocrat

Reputation: 59

Updated answer for 2021: At this time, when downloading a CSV, Google Sheets wraps any cells containing commas with quotes. Some persnickety CSV readers may not like having records with quotes and others without, but Excel handles it fine.

Another way to do wrap all in quotes is by using a custom Format.

  1. Format --> Number --> More Formats --> Custom number format

  2. Enter "@" This will wrap all fields in quotes.

  3. Download the CSV and use find/replace to reduce the triple quotes """ to "

Upvotes: 1

Code Guru
Code Guru

Reputation: 15578

You can use CHAR(34) like this

="{"&char(34)&"userId"&char(34)&":"&G1&"}"

this will result in

{"userId":1}

Upvotes: 13

Alexis
Alexis

Reputation: 842

You can use =CHAR(34) or wherever you need double quotes.

Upvotes: 2

DaveBagler
DaveBagler

Reputation: 369

This is my process:

1) wrap each cell in double quotes either using the concatenate function or by using Henrique's formula above. I generally use concatenate because I generally have a column or two that I don't want wrapped in double quotes.

2) export the CSV. You'll notice that the cells are wrapped but with three double quotes (""") rather than one (") so...

3) open the CSV in a text editor find """ and replace with "

That should get you a CSV with cells wrapped in double quotes.

Upvotes: 6

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

First, why don't you just export your spreadsheet as csv? Google has that feature built-in. It's under the menu File > Download as > CSV

But, answering your question, it's pretty easy to wrap all values in doubles quotes. Let's say the desired values are on Sheet1 columns A to D. On Sheet2, cell A1, just place this formula:

=ArrayFormula(""""&Sheet1!A:D&"""")

The issue with the issue with the double quotes is that they're used as string delimiters, so to have them inside a string you have to type two consecutive. So, one " to open, two to have one "" and one " to close :)

Upvotes: 44

Related Questions