6 Replies Latest reply on Feb 20, 2020 4:47 PM by Glen Conway

    Problem loading csv with numbers with decimal separator (real numbers)

    BerndL

      Following setup:

      • SQLcl: Release 19.1 Production auf Do Jan 23 15:56:46 2020
      • Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      SQL> show nls
      NLS_LANG GERMAN_GERMANY.WE8MSWIN1252
      DB_TIMEZONE +00:00
      NLS_CALENDAR GREGORIAN
      NLS_CHARACTERSET WE8MSWIN1252
      NLS_COMP BINARY
      NLS_CURRENCY €
      NLS_DATE_FORMAT DD.MM.RR
      NLS_DATE_LANGUAGE GERMAN
      NLS_DUAL_CURRENCY €
      NLS_ISO_CURRENCY GERMANY
      NLS_LANGUAGE GERMAN
      NLS_LENGTH_SEMANTICS BYTE
      NLS_NCHAR_CONV_EXCP FALSE
      NLS_NUMERIC_CHARACTERS .,
      NLS_SORT GERMAN
      NLS_TERRITORY GERMANY
      NLS_TIME_FORMAT HH24:MI:SSXFF
      NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
      NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
      NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
      SESSION_TIMEZONE Europe/Berlin
      SESSION_TIMEZONE_OFFSET +01:00
      

       

      Target table is t_deskriptor:

      SQL> desc t_deskriptor;
      Name   Null?    Typ
      ------ -------- -------------
      ENUM   NOT NULL NUMBER(10)
      RASTER          VARCHAR2(20)
      XMIN            FLOAT(126)
      YMIN            FLOAT(126)
      XMAX            FLOAT(126)
      YMAX            FLOAT(126)
      NAME            VARCHAR2(255)
      WERT            VARCHAR2(255)
      SQL>                            
      

       

      Content of import file:

      "ENUM","RASTER","XMIN","YMIN","XMAX","YMAX","NAME","WERT"
      162028,"0",1564.18,21.52,1631.71,36.12,"Haltungsfläche","22903324"
      

       

      Import (umlaut gets eaten by the console but is actually correct):

      SQL> load t_deskriptor D:\Temp\t_deskriptor_tw.csv
      --Anzahl verarbeitete Zeilen: 1
      --Anzahl fehlerhafte Zeilen: 0
      0 - SUCCESS: Laden wurde ohne Fehler verarbeitet
      SQL> commit;
      Commit abgeschlossen.
      SQL> select *
        2  from t_deskriptor;
      
      
           ENUM    RASTER      XMIN    YMIN      XMAX    YMAX              NAME        WERT
      _________ _________ _________ _______ _________ _______ _________________ ___________
         162028 0            156418    2152    163171    3612 Haltungsflche    22903324
      
      
      
      
      SQL>
      
      

       

      Just to make sure that there isn't a display problem:

      SQL> insert into t_deskriptor (enum, xmin, name)
        2  values (4711, 1.0815, 'Test');
      
      
      1 Zeile eingefügt.
      
      
      SQL> commit;
      Commit abgeschlossen.
                                                                                                                              
      SQL> select * 
        2* from t_deskriptor;
           ENUM    RASTER      XMIN    YMIN      XMAX    YMAX              NAME        WERT
      _________ _________ _________ _______ _________ _______ _________________ ___________
         162028 0            156418    2152    163171    3612 Haltungsflche    22903324
           4711              1.0815                           Test
      
      
      SQL>
      
      

       

      Changing nls_numeric_characters to ',.' (which would actually be wrong) doesn't change anything.

      Trying the same in SQL Developer 17.4.1 via "import delimited data" yields the same (wrong) result.

      Is there anything I can change to get the data correctly imported? Obviously the period isn't recognized as decimal separator but how can I change that?

       

      Thanks in advance, Bernd

        • 1. Re: Problem loading csv with numbers with decimal separator
          Gaz in Oz

          Could it be a display issue as opposed to a load issue perhaps?

          Please show the output from the following:

          SQL> show numformat

          SQL> show sqlformat

          SQL> col

          It looks like you have sqlformat ansiconsole, and are running sqlcl on *NIX or cygwin or MacOS and/or using an xterm compatible console, due to the underline of the column headers. It may be a problem with ansiconsole. set it to default then select * from t_deskriptor.

          Do you set any other sqlcl display options?

          Is there a login.sql that sets up anything?

          • 2. Re: Problem loading csv with numbers with decimal separator
            BerndL

            Sorry for not giving more info.

            I'm on Windows 10 and I indeed have set ansiconsole but apart from that nothing else.

             

            SQL> show numformat
            numformat ""
            SQL> show sqlformat
            SQL-Format: ansiconsole
            SQL>
            

             

            But as can be seen in my first post, the second dataset, which was inserted via INSERT INTO... is shown correctly, also controlled in SQL Developer.

            Maybe there is one thing of interest: I have set the environment variable NLS_LANG to GERMAN_GERMANY,WE8MSWIN1252.

            • 3. Re: Problem loading csv with numbers with decimal separator (real numbers)
              BerndL

              Okay - I tried putting all values in the csv in quotation marks and use comma as decimal separator, so that the import file looks like this:

               

              "ENUM","RASTER","XMIN","YMIN","XMAX","YMAX","NAME","WERT"
              "162028","0","1564,18","21,52","1631,71","36,12","Haltungsfläche","22903324"
              

               

              This file could successfully be imported. Obviously setting nls_numeric_characters doesn't have any meaning in this context.

              So, do I really have to put each and every value in quotation marks to be able to import csv data into a server on a system where the decimal separator is comma?

              Can one call this a bug? I'm tempted to do so.

              • 4. Re: Problem loading csv with numbers with decimal separator (real numbers)
                Glen Conway

                Your original test case works for me using SQLcl 19.4 against an 11g XE database on WIndows 10.  I notice the help for LOAD says the csv file must be encoded UTF8:

                Capture.JPG

                Also, you can see above that my database NLS characterset is AL32UTF8.

                 

                So, basically, you should be able to get this to work.  No bug with FLOAT datatype, no bug with NSL_NUMERIC_CHARACTERS.

                 

                Cheers

                 

                Edit:  The umlaut is not there, but you can't win them all

                 

                Capture.JPG

                • 5. Re: Problem loading csv with numbers with decimal separator (real numbers)
                  BerndL

                  Well, I guess your default decimal separator is dot, and not comma as it is in a German environment. And I suspect this being the culprit.

                  The import file I used is UTF-8 encoded, so this cannot be the problem.

                  But you mentioned SQLcl 19.4, so I'll try this one out.

                  <edit> tried 19.4 - same (buggy? ) result </edit>

                  • 6. Re: Problem loading csv with numbers with decimal separator (real numbers)
                    Glen Conway

                    Sorry, you are no doubt correct.  But in your first post, you list

                    NLS_NUMERIC_CHARACTERS .,

                    just as I have in my AMERICA case, and that is what confused me.