Forum Discussion
Peter Snabe
Aug 03, 2018Copper Contributor
Save as CSV file (UTF-8) with double quotes - how?
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?
eveneveneven
May 23, 2019Copper Contributor
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?
cosmo90
Jun 20, 2019Copper Contributor
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.
- BikertjeSep 10, 2019Copper Contributor
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"
- Roger-RODEONMar 28, 2020Copper Contributor
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.
- PeterKnxAug 08, 2019Copper Contributor
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.