Oracle Import CSV file

Question

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

User · Answer

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  quot  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  quot aa quot  1001 22  quot bb  2002 33  quot cc quot   44  quot dd quot  4004 55  quot ee    This is the control file  LOAD DATA APPEND  INTO TABLE MYSCHEMA MYTABLE fields terminated by     optionally enclosed by   quot   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] Oracle: Import CSV file

Examples related to oracle

Examples related to csv

Examples related to import

Examples related to sqlplus

Examples related to sql-loader