34

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?

Sunil Garg
  • 14,608
  • 25
  • 132
  • 189
sal i
  • 341
  • 1
  • 3
  • 4

6 Answers6

43

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 :)

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
10

You can use CHAR(34) like this

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

this will result in

{"userId":1}
Sunil Garg
  • 14,608
  • 25
  • 132
  • 189
  • If you use this solution, be very careful with quotes! Note that the braces are what is contained in the quotes, not the &char(34)& – magi182 Dec 22 '16 at 06:52
6

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.

DaveBagler
  • 351
  • 3
  • 8
2

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

g00glen00b
  • 41,995
  • 13
  • 95
  • 133
Alexis
  • 816
  • 2
  • 11
  • 28
  • 1
    if i were to block A:1 to A:100, how do i use =char(34) ? – radren Apr 30 '21 at 03:50
  • 1
    @radren Use a new column and write "=char(34)&A1&char(34)" Then copy this cell and paste it below, google sheets will automatically copy the command to "=char(34)&A2&char(34)". Continue this step for all rows, and you'll have a column with quote before and after the value of the cell. – TawabG Feb 03 '23 at 10:40
2

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 "

TheTechnocrat
  • 49
  • 3
  • As usual, the solution is not to use Excel (not that this post was about Excel, but I ended up here searching for a solution for a spreadsheet in Excel) – Albert Jun 16 '21 at 17:22
1

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

dhaves
  • 37
  • 6