I was going to refer someone on StackOverflow to my post on the LOAD command in SQLcl, but then I realized I hadn’t written one yet. Oops. So here’s that post.
One of the new (that is, a command in SQLcl that is NOT in SQL*Plus) commands is ‘LOAD.’
You can find all the new commands highlighted if you run ‘help’
No need to guess what LOAD does, just consult the help.
SQL> help load LOAD ----- Loads a comma separated value (csv) file into a table. The first row of the file must be a header row. The columns in the header row must match the columns defined on the table. The columns must be delimited by a comma and may optionally be enclosed in double quotes. Lines can be terminated with standard line terminators for windows, unix or mac. File must be encoded UTF8. The load is processed with 50 rows per batch. If AUTOCOMMIT is set in SQLCL, a commit is done every 10 batches. The load is terminated if more than 50 errors are found.
A quick demo
Let’s SPOOL some CSV to a file, then use the LOAD command to put that data into a new table.
SQL> SET sqlformat csv SQL> cd /Users/thatjeffsmith SQL> spool objects.csv SQL> SELECT * FROM all_objects fetch FIRST 100 ROWS ONLY; "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPLICATED","SHARDED","CREATED_APPID","CREATED_VSNID","MODIFIED_APPID","MODIFIED_VSNID" "SYS","I_FILE#_BLOCK#","",9,9,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_OBJ3","",38,38,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_TS1","",45,45,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_CON1","",51,51,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","IND$","",19,2,"TABLE",07-FEB-18,21-NOV-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","CDEF$","",31,29,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","C_TS#","",6,6,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,, "SYS","I_CCOL2","",58,58,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_PROXY_DATA$","",24,24,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_CDEF4","",56,56,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_TAB1","",33,33,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","CLU$","",5,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_PROXY_ROLE_DATA$_1","",26,26,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_OBJ1","",36,36,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","UNDO$","",15,15,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_UNDO2","",35,35,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_TS#","",7,7,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_FILE1","",43,43,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_COL2","",49,49,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_OBJ#","",3,3,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","C_OBJ#","",2,2,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,, "SYS","I_CDEF3","",55,55,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","C_COBJ#","",29,29,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,, "SYS","CCOL$","",32,29,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_OBJ5","",40,40,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","PROXY_ROLE_DATA$","",25,25,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_CDEF1","",53,53,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","C_USER#","",10,10,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,, "SYS","C_FILE#_BLOCK#","",8,8,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,, "SYS","FET$","",12,6,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_CON2","",52,52,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_OBJ4","",39,39,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","CON$","",28,28,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_CDEF2","",54,54,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","ICOL$","",20,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_COL3","",50,50,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_CCOL1","",57,57,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","COL$","",21,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_ICOL1","",42,42,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","UET$","",13,8,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","PROXY_DATA$","",23,23,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","USER$","",22,10,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_PROXY_ROLE_DATA$_2","",27,27,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_OBJ2","",37,37,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","TAB$","",4,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_COBJ#","",30,30,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_USER#","",11,11,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","FILE$","",17,17,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","OBJ$","",18,18,"TABLE",07-FEB-18,15-OCT-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","TS$","",16,6,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_UNDO1","",34,34,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","BOOTSTRAP$","",59,59,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_COL1","",48,48,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_FILE2","",44,44,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_IND1","",41,41,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_USER2","",47,47,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_USER1","",46,46,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","SEG$","",14,8,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","OBJERROR$","",60,60,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:26","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","OBJAUTH$","",61,61,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:26","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_OBJAUTH1","",62,62,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:26","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_OBJAUTH2","",63,63,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","C_OBJ#_INTCOL#","",64,64,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,, "SYS","I_OBJ#_INTCOL#","",65,65,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","HISTGRM$","",66,64,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_H_OBJ#_COL#","",67,67,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","HIST_HEAD$","",68,68,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_HH_OBJ#_COL#","",69,69,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_HH_OBJ#_INTCOL#","",70,70,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","FIXED_OBJ$","",71,71,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_FIXED_OBJ$_OBJ#","",72,72,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","TAB_STATS$","",73,73,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_TAB_STATS$_OBJ#","",74,74,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","IND_STATS$","",75,75,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_IND_STATS$_OBJ#","",76,76,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","OBJECT_USAGE","",77,77,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_STATS_OBJ#","",78,78,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","PARTOBJ$","",79,79,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_PARTOBJ$","",80,80,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","DEFERRED_STG$","",81,81,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_DEFERRED_STG1","",82,82,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","DEPENDENCY$","",83,83,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","ACCESS$","",84,84,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_DEPENDENCY1","",85,85,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_DEPENDENCY2","",86,86,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_ACCESS1","",87,87,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","USERAUTH$","",88,88,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_USERAUTH1","",89,89,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","UGROUP$","",90,90,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_UGROUP1","",91,91,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_UGROUP2","",92,92,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","TSQ$","",93,10,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","SYN$","",94,94,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","VIEW$","",95,95,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","TYPED_VIEW$","",96,96,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","SUPEROBJ$","",97,97,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_SUPEROBJ1","",98,98,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","I_SUPEROBJ2","",99,99,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, "SYS","SEQ$","",100,100,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,, "SYS","I_VIEW1","",101,101,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,, 100 ROWS selected. SQL> spool off SQL> CREATE TABLE demo_load AS SELECT * FROM all_objects WHERE 1=2; TABLE DEMO_LOAD created. SQL> LOAD demo_load objects.csv --Insert failed in batch rows 101 through 103 --ORA-01400: cannot insert NULL into ("HR"."DEMO_LOAD"."OWNER") --Row 101 data follows: INSERT INTO DEMO_LOAD(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID) VALUES ('','','',NULL,NULL,'',to_date(''),to_date(''),'','','','','',NULL,'','','','','','','','',NULL,NULL,NULL,NULL); --Row 102 data follows: INSERT INTO DEMO_LOAD(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID) VALUES ('100 rows selected. ','','',NULL,NULL,'',to_date(''),to_date(''),'','','','','',NULL,'','','','','','','','',NULL,NULL,NULL,NULL); --Row 103 data follows: INSERT INTO DEMO_LOAD(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID) VALUES ('','','',NULL,NULL,'',to_date(''),to_date(''),'','','','','',NULL,'','','','','','','','',NULL,NULL,NULL,NULL); --Number of rows processed: 103 --Number of rows in error: 3 1 - WARNING: LOAD processed WITH errors SQL> commit;
Wait, what’s with the 3 failed rows at the end?
If I tail the csv file I created, there’s a few extra lines due to feedback…hence the 3 rows failed to run – which is good 🙂
Browsing the TABLE in SQL Developer it looks like it ran just as it should.
If I go in and remove those 3 lines, truncate the table, and run the LOAD again…
Is this the BEST way to load CSV?
Probably not – I would still advise folks it’s much faster to use sqlldr or External TABLEs, but it would be hard to argue this isn’t simpler, especially when you’re dealing with reasonable amounts of data.
Reasonable being a number of rows that are adapt at being INSERTed one at a time.
19 Comments
SQL> set loadformat enclosures off
SQL> set loadformat delimiter |
SQL> set load batch_rows 100
SQL> set load timestamp_format dd-mon-yy hh.mi.ss.ff AM
SQL> set timer on
14:40:57 SQL> set load truncate on
14:40:57 SQL>
14:40:57 SQL> LOAD MIGDR.D1TABLE@MIGRATION C:\MIG\08162023_225339\2_MIGRATIONTEST_08162023_225339.csv;
Table MIGDR.D1TABLE@MIGRATION does not exists and cannot be loaded
14:41:25 SQL>
14:41:25 SQL> insert into D1TABLE@MIGRATION(createddate) values(’02-SEP-21 12.00.00.000000000 AM’);
1 row inserted.
14:41:34 SQL> rollback;
Rollback complete.
14:41:38 SQL> SELECT count(1) from “MIGDR”.”D1TABLE”@MIGRATION;
COUNT(1)
___________
261
14:41:48 SQL>
Here MIGRATION is the database link.
Hi Jeff,
I’m trying to load data using database link. The real question was, will SQLCL LOAD allows data load through database link?
Thanks
Harsha
You connect to a db and reference a file or object store item…to put into a table in that db.
No db_links
Is the LOAD command able to load a CSV file based on position?
I have some files where the headers don’t match the column names however, the positions do match. Using the GUI at Step 4 of 5 I’m able to select “Match By Position” and it works. Would like to use SQLDeveloper LOAD command instead of the GUI.
Thanks,
Rob
Yes just ignore the header line and it defaults to position matching at that point, I wrote a post on this subject very recently.
Hi Jeff,
In SQLCL I’m having trouble loading delimited files starting with a timestamp when there is more than 2 rows, here’s my test case.
cat x.txt
“COL1″~~~”COL2″~~~”COL3″
21-DEC-21 00.00.00.000000~~~123~~~”ABC”
21-DEC-21 00.00.00.000000~~~456~~~”DEF”
21-DEC-21 00.00.00.000000~~~789~~~”GHI”
create table x (
col1 timestamp,
col2 number,
col3 varchar2(10)
)
;
ALTER SESSION SET nls_timestamp_format=’DD-MON-YYYY HH24.MI.SS.FF6′;
set loadformat delimiter ~~~
load table x /tmp/x.txt
Load data into table X
csv
column_names on
delimiter ~~~
enclosures “”
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
#ERROR Insert failed for row 1
#ERROR COL1: GDK-05058: non-numeric character found
#ERROR Row 1 data follows:
21-DEC-21 00.00.00.000000,123,ABC
#ERROR Insert failed for row 2
#ERROR COL1: GDK-05058: non-numeric character found
#ERROR Row 2 data follows:
21-DEC-21 00.00.00.000000,456,DEF
#ERROR Insert failed for row 3
#ERROR COL1: GDK-05058: non-numeric character found
#ERROR Row 3 data follows:
21-DEC-21 00.00.00.000000,789,GHI
#INFO Number of rows processed: 3
#INFO Number of rows in error: 3
#INFO No rows committed
WARNING: Processed with errors
When I have I remove the 3rd row x.txt it loads fine
“COL1″~~~”COL2″~~~”COL3″
21-DEC-21 00.00.00.000000~~~123~~~”ABC”
21-DEC-21 00.00.00.000000~~~456~~~”DEF”
Load data into table X
csv
column_names on
delimiter ~~~
enclosures “”
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
#INFO Number of rows processed: 2
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 2
SUCCESS: Processed without errors
Thanks
Nick
ALTER SESSION SET nls_timestamp_format=’DD-MON-YYYY HH24.MI.SS.FF6′; ... NO, instead, try -SET LOAD TIMESTAMP DD-MON-YYYY HH24.MI.SS.FF6
Brilliant, thanks for your help Jeff.
Merry Christmas
SQLcl does not seem to handle embedded line break in the CSV data, ie:
1234,”this is data”,”this is more data
with an embedded newline”,”and finally more data”,””
That is valid CSV as per the spec (rfc4180), but SQLcl throws an error on it.
Away from my PC for a few days but this is a fun aspect of csv…it’s not super happy with multi line strings. Standard or no, not all apps dig it.
Easiest answer for you now is to open it in Excel and save it back out as xlsx.
Hi Jeff,
yes, the workaround solves this issue.
Thanks again for your immediate response and help!
Andre
Hi,
just to let you know:
It works like that (from a .bat file):
DELETE FROM upl_pk_vzk_import;
COMMIT;
set loadformat delimiter ; column_names on
LOAD upl_pk_vzk_import aufbereitung_pk_vzk_semicolon.csv;
QUIT;
However, it errors when I do it this way (please note the line breake):
DELETE FROM upl_pk_vzk_import;
COMMIT;
set loadformat delimiter ;
column_names on
LOAD upl_pk_vzk_import aufbereitung_pk_vzk_semicolon.csv;
QUIT;
— cmd output:
Fehler beim Start in Zeile: 5 Datei @ C:\Working\APP_SWLE_ALLIN\Laden_von_Aufbereitung_PK_VZK_csv_Daten_mit_SQLcl_Sample_20210813_1055\SQLcl_load.sql
In Befehl –
column_names on
Fehlerbericht –
Unbekannter Befehl
format csv
column_names on
delimiter ,
enclosure_left ”
enclosure_right ”
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
–Spalte in Headerzeile DIM_KST;DIM_JAHRE;DIM_MONAT;DIM_KENNZAHL;DIM_VERSION;DIM_BWA;DIM_GB;DIM_CENTER;DIM_SEG;WERT ist nicht f├╝r Tabelle definiert.
–Anzahl verarbeitete Zeilen: 0
–Anzahl fehlerhafte Zeilen: 0
–Keine Zeilen festgeschrieben
SEVERE: Load terminated
Right – I logged that bug, but you know how to get it working in the meantime now, right?
Hi Jeff,
We need badly a parameter to set CSV delimiter for LOAD. There is a problem…
SQLcl implicitely uses the comma, however when storing csv from Excel (in german) the delimiter is a semi-colon. Very ugly… Can you help. Or do I miss something.
Would be nice if you could drop a mail to me.
Thank you in advance!
Andre
see the help topic of set loadformat
use delimited
then set the delimiter to ;
You can do it, but there’s a bug, which requires a workaround. You have to set something AFTER the delimiter, so the command doesn’t ‘eat’ the ; as a statement delimiter. I will file a bug for you.
This will work –
SQL> set loadformat delimiter ; column_names on
SQL> load temp_locations semicolon.csv
format csv
column_names on
delimiter ;
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
--Number of rows processed: 23
--Number of rows in error: 0
--Last row processed in final committed batch: 23
0 - SUCCESS: Load processed without errors
SQL> select * from temp_locations fetch first 5 rows only;
LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID
______________ __________________________ ______________ ____________ ___________________ _____________
1000 1297 Via Cola di Rie 00989 Roma IT
1100 93091 Calle della Testa 10934 Venice IT
1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
1300 9450 Kamiya-cho 6823 Hiroshima JP
1400 2014 Jabberwocky Rd 26192 Southlake Texas US
SQL>
Have you tried loading a file in a path that contains spaces (or a file name with spaces)? I have not been able to get around this. I have tried quotes, double quotes, substitution variables and escapes to no avail. Do you have any suggestions?
This is great. Can you add a little more detail about how this works depending on csv file location? For example, how to refer to the file if it is on a linux server the database is hosted on, or an sftp site, or on a windows desktop running SQL, or a web site using PHP…
Thanks!
It’ll work as long as the process (SQLcl) has access to the file. If it’s on a SFTP site, I’d download it locally.
PHP, not sure how that comes into play with a CLI.