I'm trying to load non-UTF-8 files with SQLcl, but weren't successful so far ... please advise.
BTW: is there a manual available for SQLcl (especially as "help load" just shows a blank line )?
As far as I can reproduce it, SQLcl ignores the jvm option "file.encoding" completely regarding "load"
Basically I ran into four problems:
- "SQLcl load" only works with UTF-8 files
- file.encoding is also used for the SQL script (well ... THAT may be intended)
- CSV with "", as a quoted text cannot be loaded
- CSV with "" as a quoted text is not correctly interpreted
I created a little test case to describe the problem ...
Please note that although the testcase DOES spool data, this is just for the test case ... the data i need to import are provided from an external source.
The table I used for the testcase
create table ZCBRMDST_OneColumn (onecolumn varchar2(20 char));
The commands to enter test data into the test table (needed, because "file.encoding" is also used for loading the script - thus you need an alternate tool to fill the column):
truncate table SpeedySearch.ZCBRMDST_OneColumn; insert into SpeedySearch.ZCBRMDST_OneColumn values (' - "äöü",ÄÖÜ,ß,€'); commit;
The script (diacritics.spool.sql) to spool the data from the test table (as is, without comma, without quotes, without commas and quotes):
set feedback off set echo off spool diacritics.csv.quotecomma select /*csv*/ 'qc' || oneColumn oneColumn from SpeedySearch.ZCBRMDST_OneColumn; spool off spool diacritics.csv.comma select /*csv*/ 'c' || replace(oneColumn, '"') oneColumn from SpeedySearch.ZCBRMDST_OneColumn; spool off spool diacritics.csv.quote select /*csv*/ 'q' || replace(oneColumn, ',') oneColumn from SpeedySearch.ZCBRMDST_OneColumn; spool off spool diacritics.csv.pure select /*csv*/ 'p' || replace(replace(oneColumn, '"'), ',') oneColumn from SpeedySearch.ZCBRMDST_OneColumn; spool off exit;
The script was run with several "file.encoding" values ... UTF-8, ISO-8859-1, ISO-8859-15, IBM850 (resp. Cp850).
The file encodings of the four files were checked (with Notepad++, iconv from CygWin, type, cat, ...).
Then the files were imported back with this script (diacritics.load.sql):
set feedback off set echo off set sqlformat ansiconsole truncate table SpeedySearch.ZCBRMDST_OneColumn; prompt importing diacritics.csv.quotecomma load SpeedySearch.ZCBRMDST_OneColumn diacritics.csv.quotecomma select * from SpeedySearch.ZCBRMDST_OneColumn; prompt importing diacritics.csv.comma load SpeedySearch.ZCBRMDST_OneColumn diacritics.csv.comma select * from SpeedySearch.ZCBRMDST_OneColumn; prompt importing diacritics.csv.quote load SpeedySearch.ZCBRMDST_OneColumn diacritics.csv.quote select * from SpeedySearch.ZCBRMDST_OneColumn; prompt importing diacritics.csv.pure load SpeedySearch.ZCBRMDST_OneColumn diacritics.csv.pure select * from SpeedySearch.ZCBRMDST_OneColumn; exit;
This script was run with the same "file.encoding" as the spool-script.
java -Djava.awt.headless=true -Xss10M -Dfile.encoding=IBM850 -jar "%SQL_HOME%\lib\oracle.sqldeveloper.sqlcl.jar" <user>/<pwd>@//<server>:<port>/<instance> @diacritics.spool.sql java -Djava.awt.headless=true -Xss10M -Dfile.encoding=IBM850 -jar "%SQL_HOME%\lib\oracle.sqldeveloper.sqlcl.jar" <user>/<pwd>@//<server>:<port>/<instance> @diacritics.load.sql
The results are as follows:
- "diacritics.csv.quotecomma" never ever works ... it always fails to correctly interpret the quotes within quotes when followed by a comma
- "diacritics.csv.quote" is not correctly re-imported (doubled doublequotes during export are not converted back to single doublequotes)
""äöü""ÄÖÜß€ is imported as ""äöü""ÄÖÜß€ instead of "äöü"ÄÖÜß€
- the other two work, WHEN and only when the encoding of the file is UTF-8
Every other encoding just produces unreadable content.
So ... I am doing something wrong or did I run into a bug (er well ... as for me that's more a " B U G " ;-) ).
PS: before I forget ... ASCII does work too ... but that's not really a surprise, isn't it?
Thanks in advance