csvmicrosoft excel
I work with many CSV files, and am constantly annoyed by the Excel dialog that pops up when I try to save a file:
"[your spreadsheet] may contain features that are not compatible with CSV (Comma Delimited). Do you want to keep the workbook in this format?"
My answer is always going to be YES. Is there a registry setting to suppress this confirmation dialog?
There are no polite answers to questions of the form "Why does Excel do X?".
Why don't you just adopt "Save as Unicode Text" as your standard transfer format? Excel Save as ensures there's a BOM, uses TAB as a delimiter, quotes fields just like CSV ...
By the way, you don't "select the CSV extension". You have TWO choices -- format and extension. Try save as unicode text in the 'Save as type' box and manually change the extension in the 'File name' box to csv. Then exit from Excel and double-click on the file name in Windows Explorer. Opens up fine. Try it.
Update to discuss OP's comments:
=> Please read carefully what I wrote above and try it out for your self. It will always save your file with a TXT extension unless you override that: delete the txt in the 'File name' box and type csv.
=> "Save as Unicode text" ensures that there is a UTF-16LE BOM at the front of the file. Try it and see.
=> and your expectations are not met, because Excel doesn't retain any info about input CSV files. There is no way to force it to do what you want to do. So you either give up or try something else, like what I'm suggesting.
=> I have experienced these problems, with Chinese data and Excel 1997, years ago. I have, before answering your question, verified with Excel 2003 and Excel 2007 that Excel is still behaving badly. I have verified that the "save as type = Unicode text, extension = CSV" workaround does indeed work. I have provided my insights.
Traveling Tech Guy's answer is certainly correct as far as it goes. A few points though - if you save the Macro in the current workbook, it won't be available when you start another workbook. You probably want to save it in the Personal Workbook - this is an Excel file that loads automatically every time you start Excel; it's a worksheet but it's hidden. That makes the macros saved in it available to other workbooks in the same Excel session.
Another issue is that the macro will probably record the folder and file name you are saving to. If you don't mind writing to the same file every time (not just in this session, but in all future sessions) that's cool, but if you want to be able to save the current worksheet to CSV without writing over the CSV files you created in previous sessions, you need to edit the macro and remove the Filename parameter. The macro without file name looks like this after you edit it...
Once this is done you can save any Excel file in CSV format by just pressing the assigned shortcut, Ctrl-x in this example. It will save to the same folder and name as the original XLS file but with the CSV extension - you don't have to choose a path or filename and it won't write over other CSV files from other worksheets. No warning messages, not even a confirmation dialog - just blink and done.
One last detail - when you go to edit a Macro that is saved in the Personal workbook, Excel says you can't because it's "hidden". This refers to the Hide/Unhide commands on the toolbar under the View menu - it doesn't mean you have to find the file in Explorer and remove the "hidden" file attribute. That wasted a few minutes of my time!
Good luck...
Best Answer
This is a common problem all Excel users suffer. I think, the only way to do that is to use a macro. For example: