	AllExperts > Oracle
	Search

About Binosh Mullassery-Padman
Expertise
I can answer any questions on Oracle SQL and PL/SQL. The following are the topics I am an expert in Writing SQL using SQL 92 standards Oracle Advanced Groups Analytical Functions Hierarchial Queries Partition Objects and Collections Oracle XML Oracle regular Expressions Exception Handling Bulk Binding Dynamic SQL and PL/Sql Procedures Functions Packages Triggers Advanced Queueing Implicit and Explicit Cursors Oracle sql functions(CAST, CURSOR etc) Oracle SQL Tuning Oracle Execution Plans

Experience
10 Years as Developer and DBA

Organizations
Currently working with a Health Care industry

Education/Credentials
Masters in Computer Applications

You are here: Experts > Computing/Technology > Oracle > Oracle > Csv data into oracle

Oracle - Csv data into oracle

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

Add to this Answer Ask a Question