4 Replies Latest reply on Sep 28, 2017 4:41 PM by rp0428

    Export format in text file(encoding)




      After running an SQL query, I am getting query-results as shown below.


      Please note the highlighted block.

      • 1st highlighted block has small super script '2' in each cell. This is 'square' sign.
      • 2nd highlighted block has the word : 'TZA/00/M/LAC50B30/WD/001' . Please notice the character after 'TZA/00/M/LAC50'. Its called em dash().



      I am trying to export this result into a delimited text file. I have tried following two formats.





      1. In Format 1 i.e. with 'ASCII' encoding, I am getting the symbol '?' in place of both characters (The square symbol & the em-dash).
      2. In Format 2, I.e. with 'UTF-8' encoding, I am able to get the square symbol(the superscript). But the in second block, the em-dash is not coming at all. It is coming like 'TZA/00/M/LAC50B30/WD/001'.



      Is there any way, I can get these characters in exported text file. Please note that I need to export the data in text file only with 'delimited' format only. What may be proper encoding here.



      This is urgent. Please help me.



      Best Regards,


        • 1. Re: Export format in text file(encoding)
          Gaz in Oz

          Encoding of cp1252 saves to csv correctly.

          • 2. Re: Export format in text file(encoding)



            Thanks for the reply.



            Encoding with cp1252 solves the first problem, i.e. I am able to get the superscript square.


            However, the em-dash still doesn't appear. I am getting the value as 'TZA/00/M/LAC50?B30/WD/001'. That is, the em-dash(—) is replaced by question mark(?).



            Please help me to fix this.



            Best Regards,


            • 3. Re: Export format in text file(encoding)
              Gaz in Oz

              could be:

              . Database NLS_CHARACTERSET

                  select * from nls_database_parameters

                  where  parameter = 'NLS_CHARACTERSET';

              . Client NLS_LANG

                  registry or %NLS_LANG%

              . chcp

                  C:\> chcp

              . locale of OS

                  somewhere in windows settings, registry.

              . Editor or client "tool" you are using to view the file "Exported" from SQL Dev

              . Version of SQL Dev

              . Something you are doing that, up until "now", only you know.


              In SQL Developer Worksheet (I used superscript "cubed" symbol):

              show encoding;
              SELECT '³', '—' from dual
              union all
              SELECT dump('³', 10) subs, DUMP('—', 10) from dual;
              select * from nls_database_parameters
              where  parameter = 'NLS_CHARACTERSET';


              Script  (F5) output:

              encoding "Cp1252"
              '³'                   '—'                      
              --------------------- -------------------------
              ³                     —                       
              Typ=96 Len=2: 194,179 Typ=96 Len=3: 226,128,148
              2 rows selected.
              PARAMETER                      VALUE                                   
              ------------------------------ ----------------------------------------
              NLS_CHARACTERSET               AL32UTF8                                
              1 row selected.

              Both characters are multi-byte. em-dash or what ever it's called is 3 bytes long. I export that to cp1252.csv, then view it in notepad++ which is using UTF8 encoding and looks lovely:


              Contents of cp1252.csv

              "'³'"                   "'—'"                 
              "³"                     "—"                   
              "Typ=96 Len=2: 194,179" "Typ=96 Len=3: 226,128,148"
              • 4. Re: Export format in text file(encoding)

                This is urgent.

                No - it isn't urgent - not for forum volunteers. Volunteers decide what questions/issues they want to respond to and WHEN they want to respond.


                It is is really 'urgent' then you need to hire a consultant.