Japanese-language CSV export for Excel
Thursday, November 8th, 2007MS Office is a complicated piece of junk software. Especially when it comes to Excel handling non-Roman encodings in anything outside of native Excel worksheets.
I have faced this problem when I was asked to output data from database (everything is stored in UTF-8, of course), to a CSV file. Which I, of course, did - and used UTF-8 as the encoding for CSV file.
Now we have: Unicode, already 10 years in use, and CSV, in use for even a longer time, and Excel, being developed by the biggest corporation in the world during all that time. What could go wrong you wonder?
Well.. Excel can and mostly always goes wrong.
The problem - Excel DOESN'T KNOW how to import Unicode UTF-8 CSV files! It just doesn't know about Unicode when it comes to CSV import! Absolutely unbelievable. More than that - if you use non-Japanese version of MS Office (I use English version) - you can't even export Japanese-language Excel document to CSV file! All Japanese characters will be converted to underscores.
The solution.
It took me several hours of googling, but I finally brought all the pieces together and here is what you need to do to create Excel-readable CSV with Japanese characters in it:
- When creating CSV, you must use the ShiftJIS encoding and Windows line-breaks \r\n
- In order to read the created CSV file you must use Japanese version of MS Office
And no, you can't test everything works as it should on non-Japanese version of MS Office. There's just no support for ShiftJIS there!
Of course you can use OpenOffice which read Unicode (and any other encoded)-CSV files without a problem. It doesn't work good on Mac, but is pretty nice on Windows.
I can talk for hours on what the hell is going with Microsoft and its Office package, but what I think is this situation is just not acceptable in the year 2007 (and was not acceptable even in year 2003 when the prev version of Office came out). But oh well.. at least there's a solution which will possibly save someone a couple of hours and some couple of millions nerve cells ;)
