loading data from csv to database table

Steps to load data from csv to database table

1. Create table to load the date 
2. Create a control file for the loading data into table 
3. Place the data file and control file in same folder in Application server
4. Run Sql loader command .

let us take the example of a emp table and load data from csv file to database table



This is existing emp table
copied  data from this emp table output and created a data file and save with '.csv' extension

Step-1

create table to load the date 

• creating table  emp_load to load the data

CREATE TABLE "APPS"."EMP_LOAD"

   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,

    "ENAME" VARCHAR2(10),

    "JOB" VARCHAR2(9),

    "MGR" NUMBER(4,0),

    "HIREDATE" DATE,

    "SAL" NUMBER(7,2),

    "COMM" NUMBER(7,2),

    "DEPTNO" NUMBER(2,0)

   )

 

Step-2

create a control file for the loading data into table 

LOAD DATA
APPEND
INTO TABLE emp_load
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(EMPNO "ltrim(rtrim(:EMPNO))",
    ENAME "ltrim(rtrim(:ENAME))",
    JOB "ltrim(rtrim(:JOB))",
    MGR "ltrim(rtrim(:MGR))",
    HIREDATE "ltrim(rtrim(:HIREDATE))",
    SAL "ltrim(rtrim(:SAL))",
    COMM "ltrim(rtrim(:COMM))",
    DEPTNO TERMINATED BY WHITESPACE
) 

 

Save it with '.ctl' extension

Step-3:

Put the data file and control file in same folder Application server

Step-4:

Run Sql loader command 

syntax: sqlldr <DBUSERNAME>/<BDPWD> control="<CONTROL FILE>" , data="DATA FILE"

Example:

sqlldr apps/apps control="emp.ctl" , data="emp.csv".

now data is loaded into emp_load table.