This content has been marked as final. Show 4 replies
I am trying to save it as a csv and them import them to oracle thru sql loader but the chinese character get transformed into ?????? in the csv file as a result in oracle they also look the same as ?????.If the characters are converted before being imported you couldn't get them back after the import. They are lost.
I also tried saving them into unicode text and separated by "pipe delimiter " but when it comes to oracle the chinese characters turn into gebarish.It's recommended to use AL32UTF8 Character Set on the Target Database. You may check it with the following query.
About conversion problem, the followin Notes from MOS give good advices:
select * from v$nls_parameters where parameter like 'NLS_CHARACTERSET';
The Chapter C.3) Configuring your Windows client to be an UTF-8 (Unicode) client. of the Note *788156.1* may be useful.
Character Sets & Conversion - Frequently Asked Questions [ID 227330.1] AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]
Hope this help.
I am trying to save it as a csv and them import them to oracle thru sql loader but the chinese character get transformed into ?????? in the csv file as a result in oracle they also look the same as ?????.
Pl identify the version of Excel being used, along with the encoding scheme used to create/save the CSV file.
I also tried saving them into unicode text and separated by "pipe delimiter " but when it comes to oracle the chinese characters turn into gebarish.(can't read them).
What is the characterset of the database ? Pl provide details on how the data is being loaded into the database. Where/how do you see the gibberish ?
It seems the issue is with saving a different format file from Excel.
I did a simple test, "save as...", showing:
- with .csv format the acp is used and non-acp code characters is converted to a '?' sign.
- with Unicode text .txt format unicode (no surprise, right) is used, with tab as default delimiter it seems. Different language characters, e.g. CJK graphs, input with Symbol tool are kept. Note the encoding used (probably UTF-16).
Verify the .csv or .txt file content after save from Excel via Notepad or other tool. With Notepad, make sure you use Encoding properly in the file open dialog.
So, you have two choices. Either use proper ACP, i.e. change Windows locale so that it matches source file's code page, or use a different format, such as 'Unicode Text' when converting the file via Excel.
Main point - Make sure you first verify the source file with which you feed sql*loader!
Edited by: orafad on Jul 26, 2011 8:14 PM