SQL*Loader - Loading a Csv File


SQL*Loader - Loading a Csv File

  2. > Oracle - SQL*Loader

−Table of Contents

1 - About

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

3 - Steps

3.1 - 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.

3.2 - 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
You can also specify multiple files to load with the INFILE option.
load data 
infile 'file1.csv' "str '\r\n'"
infile 'file2.csv' "str '\r\n'"

3.3 - 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.

3.4 - 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