How to load a csv file in a table with SQL Loader.
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.
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):
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'"
sqlldr db_user/db_pwd@db_tnsname data=data.csv control=load_data.ctl log=load_data.log bad=load_data_badfile.csv
where:
The data and control file must be in the same directory.
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