Hi I often need to save data as a CSV file (UTF-8) where all cells are surrounded by double quotes. I cannot find any way to do that in Excel - can it be true? Any idea how to do that?

Double quotes are needed when the text itself contains commas. Otherwise whatever is reading the file will not know correct field boundaries. It should really not be optional or at least Excel should offer it as an option during the saving as process. If MS continues to be immune to reason, then open office is the right choice.

eveneveneven I agree entirely with this. I have the same problem. The official Microsoft response to this is to https://support.microsoft.com/en-us/help/291296/procedure-to-export-a-text-file-with-both-comma-and-quote-delimiters-i. I also find the justification that "this is the up to date guidance" to be a sloppy one, as when I use CSV I often require backwards compatibility to systems not using that guidance that require double quotes around each field. It takes no effort to add functionality to export as such within Excel. My preferred solution as offered elsewhere is: download and install Open Office/Libre Office and use their inbuilt functionality for it. It will save you having to track down a Macro for it every time you need to do this.

I found a quick work-around using powershell: Import-Csv ".\SourceFile.csv" | Export-Csv ".\DestinationFile.csv" -NoTypeInformation Hope this helps.

Hi Peter, The idea is here http://lenashore.com/2012/04/how-to-add-quotes-to-your-cells-in-excel-automatically/. In brief, convert your file to CSV, select your range and apply custom \"@\" format, save again.

The format in the file should end up like this: "product no","description","quantity""1254A","Green bucket","10"

Save as CSV file (UTF-8) with double quotes - how?

43 Replies

elvira moran
Copper Contributor
Aug 03, 2018
Hello, you can change your file format to "CSV". You need to do the following:
Click on File
Select "Save As"
Selec the folder where you need to save your document
Select "Save as type", Click on the dropdown arrow to choose the new format, e.g., CSV file (UTF-8) Comma delimited.
I hope the above instructions help you.
Thank you
Elvira
- Elias2000
  Copper Contributor
  May 16, 2019
  elvira moran
- clarence potter
  Copper Contributor
  Nov 13, 2018
  Where is this mythical file type csv (utf8)? I only have csv, csv (dos), and csv (mac). I really need Excel to stop screwing up the foreign language characters when saving as csv. What is it that Excel actually saves them as that they don't work in my other apps?
  
  thanx,
  clarence
  - sagearbor
    Copper Contributor
    Nov 29, 2022
    clarence potter
    If you "Save As" → "CSV (Comma Delimited)" and then click "Tools" → "Web Options" → "Encoding" , you can choose UTF=8 ... see screen shot below.
Damien_Rosario
Silver Contributor
Aug 03, 2018
Hi Peter

Sorry I am not quite following what you are asking for.

I have saved cells with double quotes in them and they save fine in CSV file (UTF-8) in Office 365.

Can you please give an example of what type of data you have in " " and what version of Office you are using?

We can hopefully sort it out from there.

Thanks and have a great weekend.

Cheers
Damien
- Peter Snabe
  Copper Contributor
  Aug 03, 2018
  The format in the file should end up like this:
  
  "product no","description","quantity"
  "1254A","Green bucket","10"
  - Philip101
    Copper Contributor
    Apr 10, 2020
    I got round this by replacing the double quotes in Excel with a character that I did not use in the spreadsheet (£ worked for me), exporting as a .csv file, and then, having opened the file in Notepad, replacing the £ with the double quotes.
    Peter Snabe

Forum Discussion

Save as CSV file (UTF-8) with double quotes - how?

43 Replies

Resources