Forum Discussion
Save as CSV file (UTF-8) with double quotes - how?
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.
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?
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.
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.
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
- If the cells contain number, you can apply custom \"#\" format.
cantikaliem_ Thanks, but that doesn't work if there is a mix of text and numbers.
I figure out this customer formula which will work for all scenarios.
\"#\";\"#\";\"#\";\"@\"This works because you can have multiple custom formula's separated by ; for different data types.
<Format for POSITIVE Numer>;<Format for NEGATIVE Numer>;<Format for ZERO>;<Format for TEXT>YellowCylinder It shouldn't be this hard though, do you agree?
Thanks for the tip - just funny that Excel doens't have the feature built in.
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.
SergeiBaklan I tried this tip of putting \"@\" as the number format. The actual file ends up with """mytext""" in that column of values.
Use the PowerShell option , it is the only way I found that works winston_dowling
- This was the only solution that worked with my file. One problem though: if you have a number entered in the cell, even if the cell is formatted as [text] and special format is applied, the Double Quotation Character ["] will not be added.
To go around this problem I had to add Single Quotation Character ['] in front of the number that made excel to treat it as text and after exporting into csv format deleted all single quotation signs using Notepad.
If anyone has a better idea, please let me know.
