Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-223484%22%20slang%3D%22en-US%22%3ESave%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223484%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20often%20need%20to%20save%20data%20as%20a%20CSV%20file%20(UTF-8)%20where%20all%20cells%20are%20surrounded%20by%20double%20quotes.%20I%20cannot%20find%20any%20way%20to%20do%20that%20in%20Excel%20-%20can%20it%20be%20true%3F%20Any%20idea%20how%20to%20do%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-223484%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-286225%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-286225%22%20slang%3D%22en-US%22%3E%3CP%3EWhere%20is%20this%20mythical%20file%20type%20csv%20(utf8)%3F%26nbsp%3B%20I%20only%20have%20csv%2C%20csv%20(dos)%2C%20and%20csv%20(mac).%26nbsp%3B%20I%20really%20need%20Excel%20to%20stop%20screwing%20up%20the%20foreign%20language%20characters%20when%20saving%20as%20csv.%26nbsp%3B%20What%20is%20it%20that%20Excel%20actually%20saves%20them%20as%20that%20they%20don't%20work%20in%20my%20other%20apps%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanx%2C%3C%2FP%3E%3CP%3Eclarence%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227312%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227312%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CP%3EThe%20idea%20is%20here%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Flenashore.com%2F2012%2F04%2Fhow-to-add-quotes-to-your-cells-in-excel-automatically%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Flenashore.com%2F2012%2F04%2Fhow-to-add-quotes-to-your-cells-in-excel-automatically%2F%3C%2FA%3E.%3C%2FP%3E%3CP%3EIn%20brief%2C%20convert%20your%20file%20to%20CSV%2C%20select%20your%20range%20and%20apply%20custom%20%5C%22%40%5C%22%20format%2C%20save%20again.%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EIt%20seems%20not%20to%20work%20with%20fields%20containing%20numbers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224057%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224057%22%20slang%3D%22en-US%22%3E%3CP%3ERFC%204180%20doesn't%20require%20double%20quotes%2C%20it%20only%20says%20what%20%3CEM%3EAny%20field%20%3CSTRONG%3Emay%3C%2FSTRONG%3E%20by%20quoted%3C%2FEM%3E.%20To%20quote%20or%20not%20to%20quote%20depends%20on%20concrete%20standard%20implementation%2C%20Microsoft%20choose%20the%20latest.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20the%20same%20time%20if%20you%20import%20quoted%20csv%20file%20into%20Excel%20in%20most%20cases%20it%20recognizes%20it%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-223914%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223914%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20tip%20-%20just%20funny%20that%20Excel%20doens't%20have%20the%20feature%20built%20in.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-223903%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223903%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20format%20in%20the%20file%20should%20end%20up%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22product%20no%22%2C%22description%22%2C%22quantity%22%3CBR%20%2F%3E%221254A%22%2C%22Green%20bucket%22%2C%2210%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-223549%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223549%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Peter%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20idea%20is%20here%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Flenashore.com%2F2012%2F04%2Fhow-to-add-quotes-to-your-cells-in-excel-automatically%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Flenashore.com%2F2012%2F04%2Fhow-to-add-quotes-to-your-cells-in-excel-automatically%2F%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EIn%20brief%2C%20convert%20your%20file%20to%20CSV%2C%20select%20your%20range%20and%20apply%20custom%20%5C%22%40%5C%22%20format%2C%20save%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-223505%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223505%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20you%20can%20change%20your%20file%20format%20to%20%22CSV%22.%20You%20need%20to%20do%20the%20following%3A%3C%2FP%3E%3CUL%3E%3CLI%3EClick%20on%20File%3C%2FLI%3E%3CLI%3ESelect%20%22Save%20As%22%3C%2FLI%3E%3CLI%3ESelec%20the%20folder%20where%20you%20need%20to%20save%20your%20document%3C%2FLI%3E%3CLI%3ESelect%20%22Save%20as%20type%22%2C%26nbsp%3BClick%20on%20the%20dropdown%20arrow%20to%20choose%20the%20new%20format%2C%20e.g.%2C%26nbsp%3B%20CSV%20file%20(UTF-8)%26nbsp%3B%20Comma%20delimited.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20hope%20the%20above%20instructions%20help%20you.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EElvira%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-223496%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223496%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Peter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20I%20am%20not%20quite%20following%20what%20you%20are%20asking%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20saved%20cells%20with%20double%20quotes%20in%20them%20and%20they%20save%20fine%20in%20CSV%20file%20(UTF-8)%20in%20Office%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20give%20an%20example%20of%20what%20type%20of%20data%20you%20have%20in%20%22%20%22%20and%20what%20version%20of%20Office%20you%20are%20using%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20can%20hopefully%20sort%20it%20out%20from%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20and%20have%20a%20great%20weekend.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EDamien%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-574985%22%20slang%3D%22en-US%22%3ERe%3A%20How%20the%20CSV%20file%20(comma%20Delimited)%20save%20as%20from%20excel%20adjusted%20his%20align%20at%20one%20time%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-574985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F170764%22%20target%3D%22_blank%22%3E%40elvira%20moran%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-636717%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-636717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20do%20what%20you%20propose%20(format%20with%20double%20quotes)%20it%20will%20actually%20quote%20and%20escape%20them%20in%20the%20csv.%20So%20now%20it%20will%20apparently%20quote%20the%20fields%20after%20all%2C%20just%20when%20I%20didn't%20want%20it%20to...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E(I'm%20using%20semicolon%20delimited%20btw)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etext%20text2%20-%26gt%3B%20text%3Btext2%3C%2FP%3E%3CP%3E%22text%22%20%22text%22%20-%26gt%3B%20%22%22%22text%22%22%22%3B%22%22%22text2%22%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%20I%20could%20do%20some%20search%20and%20replace%20to%20make%20it%20work%20but%20honestly%2C%20it's%20embarrassing%20that%20this%20isn't%20a%20trivial%20operation%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20idea%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-710910%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-710910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347196%22%20target%3D%22_blank%22%3E%40eveneveneven%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree%20entirely%20with%20this.%20I%20have%20the%20same%20problem.%20The%20official%20Microsoft%20response%20to%20this%20is%20to%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F291296%2Fprocedure-to-export-a-text-file-with-both-comma-and-quote-delimiters-i%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ebuild%20a%20macro%20that%20exports%20a%20CSV%20in%20the%20format%20you%20want%20it%3C%2FA%3E.%20I%20also%20find%20the%20justification%20that%20%22this%20is%20the%20up%20to%20date%20guidance%22%20to%20be%20a%20sloppy%20one%2C%20as%20when%20I%20use%20CSV%20I%20often%20require%20backwards%20compatibility%20to%20systems%20not%20using%20that%20guidance%20that%20require%20double%20quotes%20around%20each%20field.%20It%20takes%20no%20effort%20to%20add%20functionality%20to%20export%20as%20such%20within%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20preferred%20solution%20as%20offered%20elsewhere%20is%3A%20download%20and%20install%20Open%20Office%2FLibre%20Office%20and%20use%20their%20inbuilt%20functionality%20for%20it.%20It%20will%20save%20you%20having%20to%20track%20down%20a%20Macro%20for%20it%20every%20time%20you%20need%20to%20do%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795920%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795920%22%20slang%3D%22en-US%22%3E%3CP%3EDouble%20quotes%20are%20needed%20when%20the%20text%20itself%20contains%20commas.%20Otherwise%20whatever%20is%20reading%20the%20file%20will%20not%20know%20correct%20field%20boundaries.%20It%20should%20really%20not%20be%20optional%20or%20at%20least%20Excel%20should%20offer%20it%20as%20an%20option%20during%20the%20saving%20as%20process.%20If%20MS%20continues%20to%20be%20immune%20to%20reason%2C%20then%20open%20office%20is%20the%20right%20choice.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-846461%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20CSV%20file%20(UTF-8)%20with%20double%20quotes%20-%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F363831%22%20target%3D%22_blank%22%3E%40cosmo90%3C%2FA%3E%26nbsp%3BHi%2C%20I%20have%20had%20also%20trouble%20with%20exporting%20Excel%20to%20quoted%20comma%20delimited%20csv.%20I%20now%20use%20a%20lightweight%20tool%20called%20Ron's%20CSV%20Editor%20(Lite%20%3D%20free%2C%20Pro%20%3D%20US%24%2035)%2C%20a%20handy%20tool%20where%20you%20can%20easy%20mass%20change%20separation%20characters%20and%20add%20quotes.%20For%20instance%20One%3B%20Two%3B%20Tree%20(or%20tabs)%20to%20%22One%22%2C%22Two%22%2C%22Three%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Peter Snabe
New Contributor

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?

13 Replies

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

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

 

 

 

 

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"

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.


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

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

@Sergei Baklan 

 

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?

@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.

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.

@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"

Related Conversations