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