Tuesday, January 6, 2009

Handling unicode characters while opening a csv file in MS Excel

Eventhough if we write the unicode characters using utf-8 in csv file , the unicode characters will write properly in .csv file and it will display properly when we open the file using notepad.

The problem will occur when we open the .csv file using Excel. The problem is unicode characters will not appear properly and the values will get strangled when openeing in Excel.The reason is excel will support only unicode charactesr using UNICODE endocing and not using UTF-8. For doing this please follow the below given steps.

This will help when writing Chinese , Japanese characters in csv file and opening it using Excel.

First when writing entries to the file, use TAB '\t' to separate entities instead of the default COMMA ',' separation.
The file encodng should be UNICODE instead of UTF-8

Sample code snippets in Java. (The same concept can be used for other programming languages also)

Writer out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("c:\\temp" +"\\"+"test.csv"), "UNICODE"));
//writing the values to csv file
strTextToWrite = "value1" + "\t";
for (int j=0;j<10;> j++)
strTextToWrite += "value"+j +"\t";
out.write(strTextToWrite + "\n");

No comments:

Post a Comment