Home » SQL & PL/SQL » SQL & PL/SQL » losing diacritic data after exporting (10g , windows XP, MS-Excel)
losing diacritic data after exporting [message #322332] Fri, 23 May 2008 02:24 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
SQL> select * from nls_session_parameters;

PARAMETER VALUE
----------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

PARAMETER VALUE
----------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE



SQL> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ---------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET UTF8
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY

PARAMETER VALUE
------------------------------ ---------------------------------------------------------------------
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 10.2.0.3.0


given above are the nls settings in session and server . I am exporting data from tables which contain data with diacritic characters in it. I am using utl_file package (using utl_file.FOPEN_NCHAR and utl_file.put_nchar functions) to write the table to data to files in Server and later copying these files to my client. I open it with MS-Word first ( in UTF-8 format) and then save it in Unicode format. Then i open MS-Excel and from Ms-EXCEL open the same file. But i find the diacritic data in database tables is now replaced with ? symbols when i open it in MS-EXCEL. Any body know any solution for this issue ? . I am sure it is not a database problem (we need to convert it some format after exporting it to physical files, in MS-WORD or MS-EXCEL ?? ), still posting it here since people working on database may be used to these type of issues and may be able to suggest a solution.

Thanks in Advance,

Nirmal
Re: losing diacritic data after exporting [message #322335 is a reply to message #322332] Fri, 23 May 2008 02:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Step one would be to look at the export file with a normal plain-text editor both on the client and server side, to see what those characters really are directly after the export.

Re: losing diacritic data after exporting [message #322368 is a reply to message #322335] Fri, 23 May 2008 04:54 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
It will not be visible correcly before encoding (opening in UTF8 format etc.,)in the text file. that is the reason for asking what will be the correct method of encoding while opening in the MS-Word or any similiar application.

Thanks

NN
Re: losing diacritic data after exporting [message #322376 is a reply to message #322368] Fri, 23 May 2008 05:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

It will not be visible correctly before encoding.



It will be if you use the correct character set for the export.

If you want to open the data with windows applications we8iso8859p1 or we8iso8859p15 might work better than Unicode, since I believe Unicode support in Office is not that complete.

Re: losing diacritic data after exporting [message #322386 is a reply to message #322376] Fri, 23 May 2008 05:37 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Hi,

Which is the format is it 'Central European(ISO)' or 'Western European(ISO)'. Please see this image attached which shows the dialog window while opening the file.

Thanks

NN
  • Attachment: image1.JPG
    (Size: 80.01KB, Downloaded 105 times)
Re: losing diacritic data after exporting [message #322388 is a reply to message #322386] Fri, 23 May 2008 05:48 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When it's exported with we8iso8859p1 then it's Western European.
Previous Topic: Package Case Statement
Next Topic: How to write a delete procedure?
Goto Forum:
  


Current Time: Mon Dec 05 23:54:18 CST 2016

Total time taken to generate the page: 0.11569 seconds