Import into Excel using CSV File having Unicode Data

[Mahesh Naik]

Hi guys,
I am facing a small prb with Excel and Unicode Data.
I am writing data into a CSV file from the database with
VB.NET using a Stream Writer object with UTF8 Encoding.
This data is Unicode data with Japanese characters with
UTF8 encoding.
When I open it in Excel (office XP) it does not show the
Japanese characters properly, they are converted into
ASCII Characters.
Even when I try to do an import data option it does not
give me the UTF8 format option to convert the data into
its original form it gives some Japanese character set
which does not work properly.
BTW If I try to paste data into the excel sheet it
recognizes that its Unicode and prompts for a conversion
to UTF8 format and everything is converted properly.
If the same data is kept in an XML File and opened in
Excel it transforms it properly as XML based data is
defaulted to UTF 8 encoding.
Do you have any information on this. Would really like a
some inputs for this prb if its not very time consuming
for you.
Please let me know if this behavior of Excel is by default
and is there any work around on the same.
Thanks
Mahesh Naik
P.S. Attaching a sample TXT file with Unicode Data.

[Deepak Gulati [MS]]

Hi Mahesh,

You can do two things to fix this:

a.) Set the CharSet of response to utf8. Response.CharSet = "utf-8"
b.) utf8 files have a byte pattern in the header - the BOM - to indicate
that its a unicode file. The BOM is 0xEF, 0xBB, 0xBF. Here is how you would
output this in classic .asp:
Response.BinaryWrite(chrb(239) & chrb(187) & chrb(191)) 'BOM = EF BB BF

Please do let me know how it goes!

Regards,
Deepak
--
This posting is provided "As Is" with no warranties, and confers no
rights.

"Mahesh Naik" <mahes...@wipro.com> wrote in message
news:057f01c2a58a$932af0d0$d4f82ecf@TK2MSFTNGXA11...

[Deepak Gulati [MS]]

Sorry Mahesh, misread your question a bit - you are not dealing with
ASP/ASP.NET - so just make sure that the first few byets in the csv that you
are outputting are EF BB BF (i.e. ASCII values 239, 187 and 191). This will
make excel interpret your file correctly as utf-8.

Thanks,
Deepak

--
This posting is provided "As Is" with no warranties, and confers no
rights.

"Deepak Gulati [MS]" <dee...@online.microsoft.com> wrote in message
news:Ot2ufXZpCHA.1888@TK2MSFTNGP09...

[Mahesh Naik]

Hey
Thanks Deepak.
I think i have resolved the issue.
The prb is that Excel does not or cannot interpret UTF 8
encoding.
So i played a trick on it and encoded the Data using the
16 bit encoding.
Which goes something like this
Dim otxtEncoding As System.Text.Encoding = New
System.Text.UnicodeEncoding()
Dim oSW As StreamWriter = New StreamWriter
("c:\UTC16.txt", True, otxtEncoding)

This was correctly interpreted by Excel when the file was
saved as a csv file.

Thanks for the input anyway.
Mahesh Naik

>.
>