Use Sql Developer to load formatted excel report data into Oracle

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 and follow the procedure.

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;
  /


After executing above scripts we are ready to used sql developer or sqlloader to load data in our required format in the climate_data table.


To load data using sql developer start the sql developer with the your user in my case it is scott.

open the table tree in the object explorer and right click the climate_data table and click import.

at first screen click browse and provide the excel or csv file.

header checkbox must be unchecked click next

select import method to insert and click next
again click next on the choose column page
in the column definition page select match by position option and click next.
on finish page click finish.

A dialog box will appear to show the status if import .

click ok


now you can query climate_data table to view the loaded data .