I've been searching for a while now but can't seem to find answers so here goes...
I've got a CSV file that I want to import into a table in Oracle (9i/10i).
Later on I plan to use this table as a lookup for another use.
This is actually a workaround I'm working on since the fact that querying using the IN clause with more that 1000 values is not possible.
How is this done using SQLPLUS?
Thanks for your time! :)
Another solution you can use is SQL Developer.
With it, you have the ability to import from a csv file (other delimited files are available).
Just open the table view, then:
You have the option to have SQL Developer do the inserts for you, create an sql insert script, or create the data for a SQL Loader script (have not tried this option myself).
Of course all that is moot if you can only use the command line, but if you are able to test it with SQL Developer locally, you can always deploy the generated insert scripts (for example).
Just adding another option to the 2 already very good answers.
An alternative solution is using an external table: http://www.orafaq.com/node/848
Use this when you have to do this import very often and very fast.
SQL Loader is the way to go. I recently loaded my table from a csv file,new to this concept,would like to share an example.
LOAD DATA infile '/ipoapplication/utl_file/LBR_HE_Mar16.csv' REPLACE INTO TABLE LOAN_BALANCE_MASTER_INT fields terminated by ',' optionally enclosed by '"' ( ACCOUNT_NO, CUSTOMER_NAME, LIMIT, REGION )
Place the control file and csv at the same location on the server. Locate the sqlldr exe and invoce it.
sqlldr userid/passwd@DBname control= Ex : sqlldr abc/xyz@ora control=load.ctl
Hope it helps.
Somebody asked me to post a link to the framework! that I presented at Open World 2012. This is the full blog post that demonstrates how to architect a solution with external tables.
Oracle 18c you could use Inline External Tables:
Inline external tables enable the runtime definition of an external table as part of a SQL statement, without creating the external table as persistent object in the data dictionary.
With inline external tables, the same syntax that is used to create an external table with a CREATE TABLE statement can be used in a SELECT statement at runtime. Specify inline external tables in the FROM clause of a query block. Queries that include inline external tables can also include regular tables for joins, aggregation, and so on.
INSERT INTO target_table(time_id, prod_id, quantity_sold, amount_sold) SELECT time_id, prod_id, quantity_sold, amount_sold FROM EXTERNAL ( (time_id DATE NOT NULL, prod_id INTEGER NOT NULL, quantity_sold NUMBER(10,2), amount_sold NUMBER(10,2)) TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir1 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY '|') LOCATION ('sales_9.csv') REJECT LIMIT UNLIMITED) sales_external;
I would like to share 2 tips: (tip 1) create a csv file (tip 2) Load rows from a csv file into a table.
====[ (tip 1) SQLPLUS to create a csv file form an Oracle table ]====
I use SQLPLUS with the following commands:
set markup csv on set lines 1000 set pagesize 100000 linesize 1000 set feedback off set trimspool on spool /MyFolderAndFilename.csv Select * from MYschema.MYTABLE where MyWhereConditions ; spool off exit
====[tip 2 SQLLDR to load a csv file into a table ]====
I use SQLLDR and a csv ( comma separated ) file to add (APPEND) rows form the csv file to a table. the file has , between fields text fields have " before and after the text CRITICAL: if last column is null there is a , at the end of the line
Example of data lines in the csv file:
11,"aa",1001 22,"bb',2002 33,"cc", 44,"dd",4004 55,"ee',
This is the control file:
LOAD DATA APPEND INTO TABLE MYSCHEMA.MYTABLE fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS ( CoulmnName1, CoulmnName2, CoulmnName3 )
This is the command to execute sqlldr in Linux. If you run in Windows use \ instead of / c:
sqlldr userid=MyOracleUser/MyOraclePassword@MyOracleServerIPaddress:port/MyOracleSIDorService DATA=datafile.csv CONTROL=controlfile.ctl LOG=logfile.log BAD=notloadedrows.bad
Good luck !