Oracle/Csv data into oracle
Expert: Binosh Mullassery-Padman - 10/22/2009
QuestionI want to load data from csv file into oracle database
can u tell me step by step process for this
AnswerHi 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