Friday, October 15, 2010

Importing data from Excel Sheet into Oracle Database

The SQL file (generate_load.sql) generates the LOADDATA command. Place the generated LOADDATA command and the input file in the control file with the extension .ctl. The input file should be a comma separated file. Hence change the excel file into comma separated file.  SQL*Loader (sqlldr) is the utility to use for high performance data loads.  The data can be loaded from any text file and inserted into the database.


Steps to Import data from Excel into Oracle Database :

  1. Save the xls file as a comma separated file with extension .csv

2.   Execute the generate_load.sql file which will give you the LOADDATA command.
Save it into a control file with extension .ctl like example.ctl
SQL>@generate_load.sql
It will ask for the owner name and table name give the username(which is used to connect to sqlplus) and tablename in CAPS

3.   In the example.ctl file change the .txt file to the input .csv file

4.       sqlldr userid=scott/tiger control=example.ctl log=example.log bad = example.bad
On execution of this command it will insert the rows into the table. For any error just look into the log file and the .bad file.

NOTE: Remove the headings in the Excel file, if available.

userid  - The Oracle username and password
log                   - The name of the file used by SQL*Loader to log results
control - The name of the control file.  This file specifies the format of the data to be loaded.
bad     - A file that is created when at least one record from the input file is rejected.  The rejected data records are placed in this file.  A record could be rejected for many reasons, including a non-unique key or a required column being null.



--------------------------------------------- Load.sql ----------------------------

set trimspool on 
set serverout on 
clear buffer 
undef dumpfile 
undef dumptable 
undef dumpowner 
var maxcol number 
var linelen number 
var dumpfile char(40) 
col column_id noprint 
set pages 0 feed off termout on echo off verify off 
accept dumpowner char prompt 'Owner of table to dump: ' 
accept dumptable char prompt 'Table to dump: ' 

begin 
  select max (column_id) 
  into   :maxcol 
  from   all_tab_columns 
  where table_name = rtrim (upper ('&dumptable')) and 
         owner      = rtrim (upper ('&dumpowner')); 
         
  select sum (data_length) + (:maxcol * 3)
  into   :linelen 
  from   all_tab_columns 
  where table_name = rtrim (upper ('&dumptable')) and 
         owner      = rtrim (upper ('&dumpowner')); 
end; 
-- Build a basic control file 
set line 79 
spool dtmp.sql 
select 'spool '||lower ('&dumptable')||'.par' from dual; 
spool off 


select 'userid = /'||chr (10)|| 
       'control = '||lower ('&dumptable')||'.ctl'||chr (10)|| 
       'log = '||lower ('&dumptable')||'.log'||chr (10)|| 
       'bad = '||lower ('&dumptable')||'.bad'||chr (10) 
from   dual;
spool off

spool dtmp.sql 
select 'spool '||lower ('&dumptable')||'.ctl' from dual;
spool off 

select 'load data'||chr(10)|| 
       'infile '||''''||lower ('&dumptable')||'.txt'||''''||chr (10)|| 
       'into table &&dumptable'||chr (10)|| 
       'fields terminated by '||''''||','||''''||
       ' optionally enclosed by '||''''||'"'||''''||chr (10)||'('
from   dual;
select '   '||column_name||',', column_id 
from   all_tab_columns 
where table_name = upper ('&dumptable') and 
       owner      = upper ('&dumpowner') and 
       column_id < :maxcol 
union 
select '   '||column_name, column_id 
from   all_tab_columns 
where table_name = upper ('&dumptable') and 
       owner      = upper ('&dumpowner') and 
       column_id = :maxcol 
order by 2;
select ')' from dual;
spool off

No comments:

Post a Comment

 

©2010 Software Testing powered by Free Blogger Templates | Author : Anand Satish