Worked in Database technology for fixed the issues faced in daily activities in Oracle, Sql Server, MySQL etc.

Tag Archives: CSV file to table example

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 )

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
)

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.

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

5. Check the table values
select * from scott.test.

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 EMPSolution:
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.