How to load Excel spreadsheet data into Oracle tables

Oracle Tips by Burleson Consulting
December 27, 2007

Question: I want to load Excel spreadsheet data into Oracle. I know that I can convert the spreadsheet into a CSV and load the spreadsheet as an Oracle external table, but I want a method for loading the Excel data directly into an Oracle table. What's the best way to load spreadsheet data into Oracle?

Answer: As you noted, there are several approaches for getting Excel data into Oracle:

Load data directly from the spreadsheet - The best way to do this fast is with the Excel-DB product. You can load directly from within the Excel spreadsheet without the clumsy ODBC, very fast using a native API. It's also great for end-users since it adheres to all Oracle security access policies. Windows Vista and Excel 2007 are supported environments for Excel-DB software.
Define the spreadsheet as an Oracle external table - If you convert the spreadsheet from an xls file to a csv (comma-delimited) format, you can define the spreadsheet as an external table.
Load Excel Spreadsheet from SQL*Forms - This thread shows some code for loading data from a spreadsheet directly into an Oracle table:

DECLARE
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
ctr NUMBER(12);
cols NUMBER(2);
name_var1 VARCHAR2(2000);
name_var2 VARCHAR2(2000);
name_var3 VARCHAR2(2000);
name_var4 VARCHAR2(2000);
filename varchar2(100);

PROCEDURE OLEARG IS
args OLE2.OBJ_TYPE;
BEGIN
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,ctr); -- row value
ole2.add_arg(args,cols); -- column value
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell
ole2.destroy_arglist(args);
END;

BEGIN
filename :=GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','true');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args);
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'Sheet1');
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
ole2.destroy_arglist(args);

ctr := 2; --row number
cols := 1; -- column number

FIRST_RECORD;

LOOP
OLEARG;
name_var1 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var4 := ole2.get_num_property(cell,'Value'); --cell number value of the argument

IF ctr = 1 then
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
ELSE
NEXT_RECORD;
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
END IF;

EXIT WHEN length(name_var1) = 0 or length(name_var1) is null;
ctr := ctr + 1;
cols := 1;

END LOOP;
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;