Oracle: Import CSV file

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! :)

SQL Loader helps load csv files into tables: SQL*Loader

If you want sqlplus only, then it gets a bit complicated. You need to locate your sqlloader script and csv file, then run the sqlldr command.

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:

• choose actions
• import data
• find your file
• choose your options.

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.

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 !

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.

From 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;