| Issue with excel Import. Unable to import excel number field into varchar2 column [message #628796] |
Fri, 28 November 2014 08:38  |
 |
JayPote1986
Messages: 1
Registered: November 2014
Location: MUMBAI
|
Junior Member |
|
|
Hi,
I've issue with importing values from excel to oracle.
Want to read both numeric and alpha numeric values in single column, but it not giving desired output while using ole2.get_char_property or ole2.get_num_property.
Problem scenario is attached with the file.
My Code is :
----------------------------------------------------------
PROCEDURE import_validate_data_type IS
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);
v_path varchar2(250);
filename varchar2(100);
worksheet_name varchar2(100);
worksheet_name1 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
Message(cell||' X');Message(' ');
ole2.destroy_arglist(args);
END;
BEGIN
name_var1 := null;
v_path := 'D:\Project_Gold\';
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','false');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,v_path||'Book1.xls');
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args);
args:= ole2.create_arglist;
ole2.add_arg(args, 1);
worksheet := ole2.get_obj_property(workbook, 'Worksheets', args);
ole2.destroy_arglist(args);
worksheet_name := ole2.get_char_property(worksheet,'Name');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,worksheet_name);
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
ole2.destroy_arglist(args);
ctr := 1; --row number
cols := 1; -- column number
first_record;
loop
olearg;
name_var1 := ole2.get_obj_property(cell,'Value');
Message('Value :'||name_var1);Message(' ');
exit when ctr = 6;
:block3.po_number := name_var1;
ctr := ctr + 1;
end loop;
OLE2.INVOKE(workbook ,'Close');
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;
--------------------------------------------------------------------------
Hope someone will help me resolve this query.
Jay
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
