Load file to table with SQL Loader

Load data from file to Oracle database with SQL Loader

It is client based utility for loading the data into the database from files with help of SQL Loader engine.
You can load the text file, csv file into database at client side where SQL loader engine is running.

SQLLOADER is used control file which will define the format of data read from datafile with other parameters.
Control file defines the format and method in which data is loaded and you can also use function while inserting data and define character set as you wish.
SQLLoader is powerful tool to read file and load into the database.

Method to use the SQLLOADER

sqlldr username@server/password control=loader.ctl

Following is the example of Control file:
Following example show us to build a control file to load data from file mydata.csv. It is comma separated file and load itno the emp table. Last line give us column name for inserting data.

load data
infile 'c:\mydata.csv'
into table emp1
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )

DATA of mydate.csv file:

mydate.csv
20001,Rakesh,10000,10
20006,Shweta,20000,20
20008,Anil,20000,20

Note:
LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load.
INFILE specifies the name of a datafile containing data that you want to load.
INTO TABLE enables you to identify tables, fields, and data types.
FIELDS TERMINATED BY “,” This is the symbol used to separate values in your CSV file.
OPTIONALLY ENCLOSED BY ‘”‘ This allows CSV values to be enclosed in double-quotes.
(column name)List of columns to be loaded. The order of this list comes from the CSV file and the column names come from the table.

Explain the Control file syntax:
LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load.
INFILE clause specifies the name of a datafile containing data that you want to load.
BADFILE clause specifies the name of a file into which rejected records are placed.
DISCARDFILE clause specifies the name of a file into which discarded records are placed.
APPEND clause is one of the options you can use when loading data into a table that is not empty. INSERT is used in which table is empty.
INTO TABLE clause enables you to identify tables, fields, and data types.
WHEN clause specifies one or more field conditions.
TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
FIELD LIST which provides information about column formats in the table being loaded.

-- This is a sample control file
Options (SKIP = 1)
LOAD DATA
INFILE 'sample.dat'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
)

Example to Load CSV file into database table:

1. Place the file in D:\ folder.
Data.csv file:

NAME , BALANCE, START_DT
"Jones, Joe" , 14 , "Jan-12-2012 09:25:37 AM"
"Loyd, Lizy" , 187.26 , "Aug-03-2004 03:13:00 PM"
"Smith, Sam" , 298.5 , "Mar-27-1997 11:58:04 AM"
"Doyle, Deb" , 5.95 , "Nov-30-2010 08:42:21 PM"

1datafile

2. Create a table in Oracle according to data in CSV file.

create table scott.test(name varchar2(50),amount number,start_date date) ;

2createtable

3. Create a control file
Control file: Create a control file which tell SQL loader about all the details.

————————————————————
— SQL-Loader Basic Control File
————————————————————
options ( skip=1 )
load data
infile ‘Data.csv’
truncate into table scott.test
fields terminated by “,”
optionally enclosed by ‘”‘
( name
, amount
, start_date DATE “Mon-DD-YYYY HH:MI:SS am”
)

Note: Detail of control file as follows:
OPTIONS (SKIP =1 ) = Skip the header row of the CSV file. If there is no header row it would be: (skip=0).
LOAD DATA = This is the command to start loading data.
INFILE ‘DATA.CSV’ = This is the name of your CSV data file.
TRUNCATE INTO TABLE = This is the schema and name of your Oracle table in which data is loaded. The “truncate” specifies that the existing data in the table will be truncated or erased prior to the load.
FIELDS TERMINATED BY “,” = This is the symbol used to separate values in your CSV file.
OPTIONALLY ENCLOSED BY ‘”‘ = This allows CSV values to be enclosed in double-quotes.
( )= List of columns to be loaded. The order of this list comes from the CSV file and the column names come from the table.

3controlfile.JPG

4. Run the SQLLDR utility for import the datafile into table

@echo off
sqlldr 'scott/tiger@my_database' control='Control.ctl' log='Results.log'
pause

4runsqlloader.JPG

5. Check the table values

select * from scott.test.

5verifydataintable

Error SQL*Loader-466: Column EMPNO does not exist in table EMP1.
D:\>sqlldr scott
control = loader.ctl
Password:
SQL*Loader: Release 11.2.0.2.0 – Production on Tue May 1 12:11:34 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-466: Column EMPNO does not exist in table EMP1.
Solution:
Match the exact table and column number according to table definition with control file column list (last line in example)
desc scott.emp1;

Error SQL*Loader-500: Unable to open file (control.ctl)
D:\>sqlldr scott
control = control.ctl
Password:
SQL*Loader: Release 11.2.0.2.0 – Production on Tue May 1 12:11:20 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-500: Unable to open file (control.ctl)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
Solution:
Check correct path of control file. It is present on correct path or directory from is running. Or you may mention the wrong name of control file.

SQL*Loader-601: For INSERT option, table must be empty. Error on table EMP
D:\>sqlldr scott
control = loader.ctl
Password:
SQL*Loader: Release 11.2.0.2.0 – Production on Tue May 1 12:19:28 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-601: For INSERT option, table must be empty. Error on table EMP
Solution:
Need to modify the control file it you want to append the data into the table other wise use truncate for truncate the old data before going to proceed further insert into table.
Choose from anyone and modified the control file according
append into table emp – append means add the data into existing table
truncate into table emp – truncate means add truncate the old existing data in table and load new one.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.