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

    Export format in text file(encoding)

    3556676

      Hi,

       

      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.

       

       

       

      Problem:

      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,

      Priyank

        • 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)
            3556676

            Hi,

             

            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,

            Priyank

            • 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 17.2.188.1599 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.