SQL*Loader - Loading a Csv File

About

How to load a csv file in a table with SQL Loader.

Articles Related

Steps

Data File

The content of the data file data.csv is:

Id, Name, Description, StartDate
1, "Gerard, Nico", "Married two daugthers, data geek", "24-03-2008"
2, "Iron, Man", "Never Stop", "#"

This data must be loaded in the table MY_TABLE. See below.

Control File

The content of the control file load_data.ctl is:

OPTIONS (
  SKIP=1,
  ROWS=1000,
  PARALLEL=true,
  DIRECT=true,
  SKIP_INDEX_MAINTENANCE=true
)
LOAD DATA
APPEND
INTO TABLE MY_TABLE 
        FIELDS TERMINATED BY ","
	OPTIONALLY ENCLOSED BY '"' 
        TRAILING NULLCOLS
  (
    ID          ,
    NAME        ,
    DESCRIPTION CHAR(4000) "substr(:DESCRIPTION, 0, 50)",
    STARTDATE   DATE       "dd-mon-yy" "DECODE(:STARTDATE,'#','31-12-99')"
  )

where: the options are the sqlldr command line argument (but they can be specified in the control file):

• ROWS is the number of row before committing (default:2)
• ERRORS controls the number of error allowed
• PARALLEL specifies whether direct loads can operate in multiple concurrent sessions to load data
• DIRECT specifies the load data path. A value of true specifies a direct path load. A value of false specifies a conventional path load.
• SKIP_INDEX_MAINTENANCE will skip the maintenance (ie creation) of the index and is need with direct path

load data 
infile 'file1.csv' "str '\r\n'"
infile 'file2.csv' "str '\r\n'"

Shell File

sqlldr db_user/db_pwd@db_tnsname data=data.csv control=load_data.ctl log=load_data.log bad=load_data_badfile.csv

where:

• db_user is the database user
• db_pwd is the database password
• db_tnsname is the connect identifier (here a tnsname)
• data.csv is the above data file
• load_data.ctl is the above control file
• load_data.log is the log file
• load_data_badfile.csv is the file that will contain any bad records if any.

The data and control file must be in the same directory.

Results

SQL*Loader: Release 11.2.0.1.0 - Production on Wed Sep 18 11:05:59 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1