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

[Peter Snabe]

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?

[#2]

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

[#3]

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

[#4]

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.

[#5]

The format in the file should end up like this:

"product no","description","quantity"
"1254A","Green bucket","10"

[#6]

Thanks for the tip - just funny that Excel doens't have the feature built in.

[#7]

RFC 4180 doesn't require double quotes, it only says what Any field may by quoted. To quote or not to quote depends on concrete standard implementation, Microsoft choose the latest.

At the same time if you import quoted csv file into Excel in most cases it recognizes it correctly.

[#8]

---

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.

---

It seems not to work with fields containing numbers

[#9]

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

[#10]

@elvira moran 

[#11]

@SergeiBaklan 

When I do what you propose (format with double quotes) it will actually quote and escape them in the csv. So now it will apparently quote the fields after all, just when I didn't want it to... 

Example:

(I'm using semicolon delimited btw)

text text2 -> text;text2

"text" "text" -> """text""";"""text2"""

Of course I could do some search and replace to make it work but honestly, it's embarrassing that this isn't a trivial operation in Excel.

Do you have any idea?

[#12]

@eveneveneven 

I agree entirely with this. I have the same problem. The official Microsoft response to this is to build a macro that exports a CSV in the format you want it. 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.

[#13]

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.

[#14]

@cosmo90 Hi, I have had also trouble with exporting Excel to quoted comma delimited csv. I now use a lightweight tool called Ron's CSV Editor (Lite = free, Pro = US$ 35), a handy tool where you can easy mass change separation characters and add quotes. For instance One; Two; Tree (or tabs) to "One","Two","Three"

[#15]

LibreOffice Calc does it by default 🙂
-Chris

[#16]

Hi, Need help. i m using lenovo ideapad s145 laptop with inbuilt windows 10 an and Excel, if i am pressing Ctrl+Shift+L keys as a keyboard shortcut for data filter, it results nothing whereas ctrl+c and ctrl+v shortcuts are working fine

[#17]

@Jitender_Sehgal This is not related.  Please start a new thread.

[#18]

@Peter Snabe- hi Peter, were u able to get a workable solution to this problem? I've been trying to find an easy solution but none so far. I wonder why the save to CVS (UTF-8) doesn't work even when cells redefined as "text"-

[#19]

I totally agree. My customers need to work with CSV’s on a regular base. Excel lacks in saving to .CSV’s big time on the following points:

1. Since we use the “;” as a separator in our local settings so Excel exports with this separator.
Most software in expect a comma “,”. So it’s often a struggle to get it right or many bad imports are the result.

2. An export option to double quote all entities would be very helpfull for data that includes “,” / comma’s, like names and so on. E.g. “Skywalker, Luke”

I also suggest Ron’s editor to my customers in the Netherlands to handle the .CSV’s and I’m using Easy CSV myself since I’m a MacOS user ( ps. I bought the whole pack from this dev. With a json and plist editor in it too)

So Microsoft please listen to your customers and add some CSV functionality to Excel.

[#20]

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