Expert: Binosh Mullassery-Padman - 10/22/2009

Question
I want to load data from csv file into oracle database

can u tell me step by step process for this

Answer
Hi san,

  These are the steps.

  lets take the example of DEPT table in scott schema

  Description of this table is

  DEPT
  ----

  deptno number
  dname  varchar2(30)
  loc    varchar2(30)

Step 1: Prepare the CSV file for this table

Step 2:

Create a control file as follows. Type these in a notepad and save as <file_name>.ctl

In this case I am saving this file as dept.ctl

LOAD DATA
APPEND
INTO TABLE dept
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
deptno,   
dname,
loc
)

Step 3:

The sql loader syntax is as follows


sqlldr <username>/<password>@dbname control=<control_file> log =<log_file> bad=<bad_file> data=<data_file>


So for the dept file, I will type the sql loader command in DOS command window as follows

sqlldr scott/tiger control=c:\dept.ctl log =C:\dept.lg bad=C:\dept.bad data=C:\dept.csv

The records from CSV will import into dept table .

You can use the dept.lg and dept.bad files to verify If the records are partially inserted into dept table or completely not inserted into log table.

These files will be created by the sql loader while executing, if there are some errors. And using these files you can fix the errors and rerun sql loader.

You can use the following book to learn more about sql loader

http://books.google.com/books?id=A9s_8Z_bgnMC&pg=PR13&dq=sql+loader#v=onepage&q=...
Thanks,
Binosh