CSV and Unicode (or UTF-8) problem

In Office 2003 (and I suppose it's true for Office XP and 2000):

When we save an Excel file to CSV file, Excel doesn't ask if we have to care
about encoding, in particular, I wanted to save Unicode in UTF-8 but I can't.
This is very annoying for multi-national companies which has Excel files
coming from different part of the world.

On the other hand, I tried to edit a CSV to add some Chinese characters in
it and save it in UTF-8 and double-click to open it. Excel is then launched
but it doesn't care file encoding either. The only way to get around
"opening" is to change the file extension from .csv to .txt. Now, if we open
this text file, a wizard will present us the option to choose the file
encoding!!!

Only a little change in file extension and the behaviour is changed. What a
pity that Excel doesn't care about UTF-8.

In Excel 2k3, one of my Save As options is Unicode. I'm admittedly not
up to speed on Unicode and UTF-8 but a quick Google review makes me
wonder: are you mixing your metaphors by saving an XLS file to CSV and
then desiring it in Unicode format? What happens if you go straight
from XLS to Unicode?

What do you mean by "one of my Save As options"??? I have no Unicode
option (or whatever it suggests is) in my "Save As" window. Just a plain
Saving window in which there're only the "Filename" and "Save as type" fields
at the bottom of it. I am afraid the person who made mistake is you, sorry
to say so. I've checked against several Excel 2K3 in my company and they're
all the same. No way to specify Unicode (whatever format) while saving CSV.

"Dave O" wrote:

> In Excel 2k3, one of my Save As options is Unicode. I'm admittedly not
> up to speed on Unicode and UTF-8 but a quick Google review makes me
> wonder: are you mixing your metaphors by saving an XLS file to CSV and
> then desiring it in Unicode format? What happens if you go straight
> from XLS to Unicode?
>
>

Perhaps my mistake was offering to help. Asking again, in a different
way: if saving from .XLS to .CSV doesn't work in the manner you need,
will your foreign offices be able to read your file if you save direct
from XLS format to Unicode format?

Unicode format of what? You mean CSV file in Unicode (UTF-8) encoding? Well,
I'd already written in the first post that it's impossible to do so.

"Dave O" wrote:

> Perhaps my mistake was offering to help. Asking again, in a different
> way: if saving from .XLS to .CSV doesn't work in the manner you need,
> will your foreign offices be able to read your file if you save direct
> from XLS format to Unicode format?
>
>

SOLUTION:
The only "easy way" of doing this is as follows. First, realize that there is a difference between what is displayed and what is kept hidden in the Excel .csv file.

(1) Open an Excel file where you have the info (.xls, .xlsx)
(2) In Excel, choose "CSV (Comma Delimited) (*.csv) as the file type and save as that type.
(3) In NOTEPAD (found under "Programs" and then Accessories in Start menu), open the saved .csv file in Notepad
(4) Then choose -> Save As..and at the bottom of the "save as" box, there is a select box labelled as "Encoding". Select UTF-8 (do NOT use ANSI or you lose all accents etc). After selecting UTF-8, then save the file to a slightly different file name from the original.

This file is in UTF-8 and retains all characters and accents and can be imported, for example, into MySQL and other database programs.

No dear, it is not working for me.

When I save from xls 2003 to csv, the file is having ???? for unicode characters, whether I open it in notepad or wordpad or ms word or excel or anything, even after renaming the extension to txt or whatever.

Could you recheck that you are really able to retain unicode character when saving to csv format?

Thanks.
--
Rawat

Thank you for this solution. This worked great for me needing to insert French characters and making sure it worked with my CMS which only accepts UTF-8

Originally Posted by coolatron

SOLUTION:
The only "easy way" of doing this is as follows. First, realize that there is a difference between what is displayed and what is kept hidden in the Excel .csv file.

(1) Open an Excel file where you have the info (.xls, .xlsx)
(2) In Excel, choose "CSV (Comma Delimited) (*.csv) as the file type and save as that type.
(3) In NOTEPAD (found under "Programs" and then Accessories in Start menu), open the saved .csv file in Notepad
(4) Then choose -> Save As..and at the bottom of the "save as" box, there is a select box labelled as "Encoding". Select UTF-8 (do NOT use ANSI or you lose all accents etc). After selecting UTF-8, then save the file to a slightly different file name from the original.

This file is in UTF-8 and retains all characters and accents and can be imported, for example, into MySQL and other database programs.

If you want to solve this within Excel itself, using a macro, see the below:

http://www.ozgrid.com/forum/showthre...424#post560424

Hi,

I know it's a bit old thread, but you could use an Excel Addin to solve this issue.

http://jaimonmathew.wordpress.com/20...h_unicode_csv/

And it comes with complete source code as well.

HTH

Jaimon

Open your Excel file with Numbers (part of Apple's iWork suite), then do:
File, Export, CSV, Unicode (UTF-8).

Hi,
I understand your problem with this unicode issue. If you download the free "Calc" spreadsheet application from openoffice[dot]org you will be able to save CSV as UTF8 and upload to your database. You can also open excel files with this. I had issues with russian and swedish languages in csv format using excel - basically the text was formatting as ????. The above will solve it. Just save as as UTF8 using Calc in the save process.
Hope thats helpful!