Importing Unicode Text File

[Mike DeCleene]

Hi,

I have a CSV (comma seperated) text file. The text in
this file is Unicode (UTF-8). I can open the file in
Notepad and, if I use a Unicode font, see the characters
are correct in my text file.

However, when I try to open this in Excel, it gets mangled-
-the Japanese characters get converted into jibberish.
I've tried opening a blank file, then using "Import
External Data", but the Japanese characters in the file
still wind up getting corrupted.

What's BETTER is to change the "File Origin" to something
other than my default of Windows(ANSI) to something like
Japanese (Shift-JIS)--this handles MOST but not ALL of the
characters properly. There does not appear to be an
option to specidy the file origin as Unicode or UTF-8.

Any ideas? I'm looking for a reliable way to import what
looks like a correct .csv in unicode that will reliably
render Japanese character (and other languages supported
by Unicode) correctly.

Mike

 

[Gord Dibben]

Mike

Don't know how or if this would work but..........

Copy the text from notepad(Edit>Select All)>Copy then in Excel Edit>Paste
Special>Unicode Text.

Gord Dibben Excel MVP

 

[Mike DeCleene]

Gord,

Thanks. This has occurred to us as well (thought it only works properly for tab-delimited files--you need some additional logic to parse a CSV correctly). There are a number of manual workarounds to mitigate this issue.

The probelm, from my perspective, is that we're generating these files to share with external customers, and so we'd like to be able to offer them a "one step" process (I'm OK telling them to update their Excel confiuration once if necessary), so that we don't have to tell them to cut-and-paste every file. I'm looking for a more elegant solution, if possible. It's not possible for us to generate native Excel format, mainly because of concerns with the cost of licensing tools to generate proper Excel formats.

Is there any way to allow Excel to import this unicode CSV data directly without it getting mangled? I'm blown away that this is so hard to do, given that Microsoft touts full native Unicode support as a major feature of Excel.

Mike