Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOM and Unicode CSV's
Hi,
I am currently exporting data to a CSV file so to be viewed by Excel. I need to support the exporting of data in Japanese so therefore the CSV needs to support Unicode. The issue is that Excel seems to have problems differentiating a file that contains Unicode chars. After exploring the internet I have found that you need to use a BOM in the first few bytes of the file to tell excel what sort of encoding has been used. I have found different codes to use but cannot seem to find much information on what the codes actually stand for. Here are the codes I have tried. 0xEF 0xBB 0xBF This actually maintains the comma separation but the text is garbage I am assuming that this is because this one sets UTF-8 encoding when I am just using straight unicode. 0xFF 0xFE This actually makes Excel open the CSV file in Unicode correctly displaying the Japanese characters but it defaults to a TAB deliminated separator instead. At the moment I can simply use tabs to separate fields instead of the commas but I anticipate maybe not all spread sheeting programs have the tab deliminated feature? I guess what I am after is some more information on BOM's and how Excel interprets them. Does anyone know of a BOM code that will tell Excel that the CSV file is comma separated and that it contains straight Unicode. Thanks for taking the time to read this and have a nice day.... Danny Mosquito |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOM and Unicode CSV's
Excel does not interpret a BOM any further then to see if the (text) file is unicode or not. Worse: you have very limited control if a text file's extension is CSV. The only way you'll get the Import wizard is to change the file's extension from .CSV to .TXT Excel saves CSV files with localized separators and number (DATE!) formats. (so the comma may turn out to be a ;) Unless you save via VBA, then all of a sudden in saves in USEnglish. If you are automating have a look at OpenText and TextToColumns methods If you are using xlXP+ OPenText and SaveAs have a LOCAL argument, to open/save localized vs USEnglish. hth -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Danny Mosquito wrote : Hi, I am currently exporting data to a CSV file so to be viewed by Excel. I need to support the exporting of data in Japanese so therefore the CSV needs to support Unicode. The issue is that Excel seems to have problems differentiating a file that contains Unicode chars. After exploring the internet I have found that you need to use a BOM in the first few bytes of the file to tell excel what sort of encoding has been used. I have found different codes to use but cannot seem to find much information on what the codes actually stand for. Here are the codes I have tried. 0xEF 0xBB 0xBF This actually maintains the comma separation but the text is garbage I am assuming that this is because this one sets UTF-8 encoding when I am just using straight unicode. 0xFF 0xFE This actually makes Excel open the CSV file in Unicode correctly displaying the Japanese characters but it defaults to a TAB deliminated separator instead. At the moment I can simply use tabs to separate fields instead of the commas but I anticipate maybe not all spread sheeting programs have the tab deliminated feature? I guess what I am after is some more information on BOM's and how Excel interprets them. Does anyone know of a BOM code that will tell Excel that the CSV file is comma separated and that it contains straight Unicode. Thanks for taking the time to read this and have a nice day.... Danny Mosquito |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOM and Unicode CSV's
Thanks for that mate :) I will try using .txt extensions instead.
Danny *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unicode in CSV files | Excel Discussion (Misc queries) | |||
unicode | Excel Discussion (Misc queries) | |||
Unicode | Excel Worksheet Functions | |||
Unicode Symbols | Excel Discussion (Misc queries) | |||
Excel.......Automate importing multiple changing CSV's | Excel Programming |