Load formatted report in to Oracle using sqlloader

I this post we will learn to load formatted report data from excel or csv files into ORACLE database.

sample data in excel format.

Jan 2002- Dec 2002
St.Name	N	E	Jan_2002	Feb_2002	Mar_2002	Apr_2002	May_2002	June_2002	July_2002	Aug_2002	Sep_2002	Oct_2002	Nov_2002	Dec_2002
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	48	44	30	19	16	28	34	40	40	29	40	48
Bahawal-Pur	30.53333	71.78333	45	41	33	18	20	29	36	40	48	41	44	46
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
Jan 2003- Dec 2003
St.Name	N	E	Jan_2003	Feb_2003	Mar_2003	Apr_2003	May_2003	June_2003	July_2003	Aug_2003	Sep_2003	Oct_2003	Nov_2003	Dec_2003
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	58	51	37	20	14	31	55	53	49	28	29	46
Bahawal-Pur	30.53333	71.78333	53	52	35	19	17	31	53	55	51	35	44	45
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	38	22	15	25	53	54	53	33	30	40
Jan 2004- Dec 2004
St.Name	N	E	Jan_2004	Feb_2004	Mar_2004	Apr_2004	May_2004	June_2004	July_2004	Aug_2004	Sep_2004	Oct_2004	Nov_2004	Dec_2004
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	63	40	26	19	19	32	36	49	39	37	35	45
Bahawal-Pur	30.53333	71.78333	59	46	32	21	22	30	38	56	48	45	43	48
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	35	21	21	34	46	54	47	40	41	42

To load above data execute following scripts on your database with appropriate user.

I have used scott user for this demo.


Following scrip will create table in which data would be loaded.


create table climate_data
(
city_Name varchar2(100) ,
North varchar2(100) ,
East varchar2(100) ,
Jan varchar2(100) ,
Feb varchar2(100) ,
Mar varchar2(100) ,
Apr varchar2(100) ,
May varchar2(100) ,
Jun varchar2(100) ,
Jul varchar2(100) ,
Aug varchar2(100) ,
Sep varchar2(100) ,
Oct varchar2(100) ,
Nov varchar2(100) ,
Dec varchar2(100),
year_name varchar2(100) )
/


This package will help to hold data temporarily.


CREATE OR REPLACE PACKAGE uldemo7_climate_data AS
    year_name  varchar2(100);
END uldemo7_climate_data;
/


This trigger will convert the formatted data in to our required column in my case I want to populate year_name column.


CREATE OR REPLACE TRIGGER uldemo7_climate_data_insert
  BEFORE INSERT ON climate_data
  FOR EACH ROW

  BEGIN
  IF substr(:new.Dec,1,3) ='Dec' THEN
     uldemo7_climate_data.year_name := substr(:new.Dec,5,4);   -- save value for later use
  ELSE
     :new.year_name := uldemo7_climate_data.year_name;   -- use last valid value
  END IF;

  END;
/


Following triggers is used to  delete extra/garbage  data form the table.


create or replace TRIGGER uldemo7_climate_data_aft_ins
  after INSERT ON climate_data

  BEGIN
 delete from climate_data where year_name is null or city_name like 'Jan%' or north is null;

  END;
  /
 

======================================

To load data using sqlloader


create load_data.ctl  with following contents in any folder  in my case I am using d:\dataloading.



------------------------------------------------------------
-- SQL-Loader Basic Control File
------------------------------------------------------------
options  ( skip=1 )
load data
  INFILE                'mydata.csv'          
truncate into table   scott.climate_data
fields terminated by ','  
trailing nullcols
  (
    city_Name ,
North ,
East ,
Jan ,
Feb ,
Mar ,
Apr ,
May ,
Jun ,
Jul ,
Aug ,
Sep ,
Oct ,
Nov ,
Dec ,
year_name
  )

 
  --------control file end ----------------------
 
in control file mydata.csv is c.v file that we want to load .
climate_data is table in which data would be loaded.
every time we will load data existing data in the table will be truncated.

Now place your mydata.csv in the same folder where control file is placed.i.e d:\dataloading

go to d:\dataloading using command prompt and execute following command.

  sqlldr scott/oracle@gisdb  control='load_data.ctl' log='result.log'

above command will upload data in results will be displayed.

now we can query climate_data to view loaded data.