It is very usual requirement to move custom data available in external files to be moved to the particular tables present in Oracle DB. SQL Loader is one of the option; where we can play along with different requirements in moving the data.
First of all, what is SQL Loader? - A Quick view.
SQL Loader is an utility by which we can move data from external files such as CSV, TXT, XLS etc., to Oracle tables.
How to achieve this?
Very simple. We need to create two files first. 1. Control file and 2. Source data file.
What is control file?
Control file is similar to txt file but have to be saved with extension " .ctl " ; where we will hold the specifications about the flat file, data, table names, delimiter etc.,
Sample & Simple control file will be as following one:-
load data
infile 'c:\data\item_data.csv' BADFILE 'myydata.bad' DISCARDFILE 'mydata.dis'
into table item
fields terminated by "," optionally enclosed by '"'
( item_gid, item_desc, item_type, domain_name)
==> infile holds the external source file name and its path details
The bad file and discard files both contain rejected rows, but they are rejected for different reasons: Both are optional.
- Bad file: The bad file contains rows that were rejected because of errors. These errors might include bad datatypes or referential integrity constraints.
- Discard file: The discard file contains rows that were discarded because they were filtered out because of a statement in the SQL*Loader control file.
==> into table clause holds the < table name >
==> terminated by "," means data are separated by comma. " # " means data are separated by #.
==> optionally enclosed by ' " ' means.. values within double quotes with spaces will be considered as single data.
For example : " Logitech Pen Drive " will be considered as data for single column.
==> last line in the above example holds the list of column names of the table ITEM.
Sample Source File :-
the source file item_data.csv file may look like this:
10001,"Laptop HP", 'Electronic', 'Sales'
10002,"Logitech Pen drive", 'Electronic', 'Service'
One we created the above said two files, we have to execute the below SQLLDR statement from command promt. Upon execution of any of the below statement; data will be loaded into the table ITEM.
sqlldr username@server/password control=loader.ctl
sqlldr username/password@server control=loader.ctl
==> sqlldr = keyword
==> username, password & server names are related to the DB where we want to connect and load the data.
==> control = keyword
==> loader.ctl is the name of control file.
More options on SQL Loader? - A Detailed view.
Load fixed length data ( instead of comma separator ).
In this case, control file will be as follows.
load data
infile 'c:\data\item_data.txt'
into table item
( item_gid position (02:05) char(4),
item_desc position (08:27) char(20)
)
item_data.txt will be like this:-
1234 Laptop
1111 Mouse
Can have data in control file itself ( No need of source file ). ?
Yes we can .. as follows
Please note that; infile * and begindata are the keywords change in this control file than earlier one.
load data
infile *
replace
into table item_data
( item_gid position (02:05) char(4),
item_desc position (08:27) char(20)
)
begindata
1111 APPLE IPHONE 7S
2222 LENOVA LAPTOP
3333 LOGITECH MOUSE V2
4444 NIKON CAMERA
Can we modify the data.. on the fly .. whilst upload the data. ?
Yes we can.. by using the below one sample control file.
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state,
move_date "substr(:move_date, 3, 2) || substr(:move_date, 7, 2)"
)
Can we upload data from multiple source files ?
Yes we can . Please refer the below sample control file.
LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
Can we upload into multiple tables ?
Yes we can. please refer the below sample control file.
LOAD DATA INFILE 'mydata.dat' REPLACE INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL ) INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL )
Can we upload only selected data from source file ?
Yes we can. please refer the below sample control file.
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
INTO TABLE my_selective_table
WHEN (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
Can we load images, sound clips, blog data and documents ?
Yes, we can by using the below control file as sample one.
Consider the following table is created.
CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
Control File:
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg
We are at the end of the session of SQL Loader. Now you are proudly a SQL Loader Expert. Try this out to experience more..
Click the link and
Like this FB page to get more articles like this.
