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 nlsNLS_LANG GERMAN_GERMANY.WE8MSWIN1252DB_TIMEZONE +00:00NLS_CALENDAR GREGORIANNLS_CHARACTERSET WE8MSWIN1252NLS_COMP BINARYNLS_CURRENCY €NLS_DATE_FORMAT DD.MM.RRNLS_DATE_LANGUAGE GERMANNLS_DUAL_CURRENCY €NLS_ISO_CURRENCY GERMANYNLS_LANGUAGE GERMANNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSENLS_NUMERIC_CHARACTERS .,NLS_SORT GERMANNLS_TERRITORY GERMANYNLS_TIME_FORMAT HH24:MI:SSXFFNLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFFNLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZRNLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZRSESSION_TIMEZONE Europe/BerlinSESSION_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 \Temp\t_deskriptor_tw.csv--Anzahl verarbeitete Zeilen: 1--Anzahl fehlerhafte Zeilen: 00 - SUCCESS: Laden wurde ohne Fehler verarbeitetSQL> commit;Commit abgeschlossen.SQL> select *  2  from t_deskriptor;     ENUM    RASTER      XMIN    YMIN      XMAX    YMAX              NAME        WERT_________ _________ _________ _______ _________ _______ _________________ ___________   162028 0            156418    2152    163171    3612 Haltungsflche    22903324SQL>

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                           TestSQL>

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

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 numformatSQL> show sqlformatSQL> 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?

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 numformatnumformat ""SQL> show sqlformatSQL-Format: ansiconsoleSQL>

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.

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.

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:

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

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>

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.