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