how to Copy from excel to oracle database 1

[ding1000ma]

hi,
I need to copy data that is present in excel to oracle table.Can any one suggest me how to achieve this.
Thanks in advance,
Ding

 

[Turkbear]

Hi,
You Can:
1
Save the data in a CSV file and use SqlLoader to load it into Oracle ( pre-define you table and create the CTL file)..
or
2
I think that there is a product called 'SQL LInk for MS Excel' that can load Oracle Directly from Excel..Try a web search..

hth,

 

[sfvb]

Here is an example using OLE2:

DECLARE
objApplication OLE2.OBJ_TYPE;
objWorkbooks OLE2.OBJ_TYPE;
objWorkbook OLE2.OBJ_TYPE;
objWorksheets OLE2.OBJ_TYPE;
objWorksheet OLE2.OBJ_TYPE;
objRange OLE2.OBJ_TYPE;
objLastCell OLE2.OBJ_TYPE;
args OLE2.List_Type;

row_cnt number;
text_from_xl varchar2(40);
number_from_xl number(11,2);

xlFilePathName varchar2(100) := 'C:\test.xls';
xlDown number := -4121;
BEGIN
objApplication := OLE2.CREATE_OBJ('Excel.Application');
-- OLE2.SET_PROPERTY(objApplication, 'Visible', 'True');

objWorkbooks := OLE2.GET_OBJ_PROPERTY(objApplication, 'Workbooks');

args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args, xlFilePathName);
objWorkbook := ole2.invoke_obj(objWorkbooks, 'Open', args);
ole2.destroy_arglist(args);

--set worksheet to Sheet1
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 1);
objWorksheets := OLE2.GET_OBJ_PROPERTY(objWorkbook , 'Worksheets');
ole2.destroy_arglist(args);
OLE2.INVOKE(objWorksheets, 'Select');
objWorksheet := OLE2.GET_OBJ_PROPERTY(objWorkbook, 'ActiveSheet');

----get reference to cell A1
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'A1');
objRange := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'Range', args);
OLE2.DESTROY_ARGLIST(args);

----get reference to the last cell (with contiguous data) in this row
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, xlDown);
objLastCell := OLE2.GET_OBJ_PROPERTY(objRange, 'End', args);
OLE2.DESTROY_ARGLIST(args);

row_cnt := OLE2.GET_NUM_PROPERTY(objLastCell, 'Row');

OLE2.RELEASE_OBJ(objLastCell);
OLE2.RELEASE_OBJ(objRange);
--------------------------------
FOR row_num IN 2 .. row_cnt
LOOP
---- get text from column A
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'A'||row_num);
objRange := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'Range', args);
OLE2.DESTROY_ARGLIST(args);
text_from_xl := OLE2.GET_CHAR_PROPERTY(objRange, 'Value', args);
OLE2.RELEASE_OBJ(objRange);

---- get corresponding number from column B
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'B'||row_num);
objRange := OLE2.GET_OBJ_PROPERTY(objWorksheet, 'Range', args);
OLE2.DESTROY_ARGLIST(args);
number_from_xl := OLE2.GET_NUM_PROPERTY(objRange, 'Value', args);
OLE2.RELEASE_OBJ(objRange);

---- do something with the data
END LOOP;
--------------------------------
OLE2.INVOKE(objWorkbooks, 'Close');
OLE2.INVOKE(objApplication, 'Quit');

OLE2.RELEASE_OBJ(objWorksheet);
OLE2.RELEASE_OBJ(objWorksheets);
OLE2.RELEASE_OBJ(objWorkbook);
OLE2.RELEASE_OBJ(objWorkbooks);
OLE2.RELEASE_OBJ(objApplication);
EXCEPTION
WHEN others THEN
message('OLE2 error');
END;