Home » Developer & Programmer » Forms » Reading data from excel file and load into forms (Oracle 10g Forms, WinXP)
| Reading data from excel file and load into forms [message #599532] |
Fri, 25 October 2013 06:08  |
 |
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
Hi,
I have a Excel File which contains some columns and rows, i need to load that excel into a form and import that form data to Database Table, using DDE Method.
In simple i say;
Just to read the excel and load into a form, which can be imported into a table later.
How to achieve this? anybody using this can share your codes!
only dde method.
|
|
|
|
|
|
| Re: Reading data from excel file and load into forms [message #599651 is a reply to message #599542] |
Mon, 28 October 2013 01:05  |
|
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
The post you have mentioned is using webutil, but i m
should not use webutil;
below is the code i m trying to read the excel file;
My problem is; that the excel file has more than 300 lines, but
when i execute the below program through my form only 100 line is fetching in
my form; and finally an error is displayed
Ora-06502 : Numeric or value error;
i need to know why only 100 lines is reading; i trying to figure it out pls help.
all the 19 fields is character fields only;
--IMPORT_FROM_EXCEL_PROC;
forms_ddl('truncate table CHASSIS_ISDM_EXCEL');
commit;
DECLARE
appid PLS_INTEGER;
convid PLS_INTEGER;
docid PLS_INTEGER;
conv_established BOOLEAN := FALSE;
buffer1 VARCHAR2(200);
buffer2 VARCHAR2(200);
buffer3 VARCHAR2(200);
buffer4 VARCHAR2(200);
buffer5 VARCHAR2(200);
buffer6 VARCHAR2(200);
buffer7 VARCHAR2(200);
buffer8 VARCHAR2(200);
buffer9 VARCHAR2(200);
buffer10 VARCHAR2(200);
buffer11 VARCHAR2(200);
buffer12 VARCHAR2(200);
buffer13 VARCHAR2(200);
buffer14 VARCHAR2(200);
buffer15 VARCHAR2(200);
buffer16 VARCHAR2(200);
buffer17 VARCHAR2(200);
buffer18 VARCHAR2(200);
buffer19 VARCHAR2(200);
--buffer20 VARCHAR2(200);
--buffer21 VARCHAR2(200);
--buffer22 VARCHAR2(200);
DNO VARCHAR2(14);
DN VARCHAR2(14);
DL VARCHAR2(13);
DP VARCHAR2(13);
LC VARCHAR2(6);
I NUMBER(10) := 1;
BEGIN
go_block('CHASSIS_ISDM_EXCEL');
clear_block;
APPID := DDE.APP_BEGIN('C:\Program Files\Microsoft Office\Office12\excel.exe',
DDE.APP_MODE_MINIMIZED);
WHILE NOT conv_established
LOOP
BEGIN
convid := DDE.INITIATE('excel', 'system');
conv_established := TRUE;
EXCEPTION
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
conv_established := FALSE;
END;
END LOOP;
DDE.EXECUTE(convid, '[Open("c:\Chassis_Entry1.xlsx")]', 100000000);
docid := DDE.INITIATE('excel', 'c:\Chassis_Entry1.xlsx');
LOOP
LC := 'R'||I||'C1';
DDE.REQUEST (docid, LC, buffer1, DDE.CF_TEXT, 200);
exit when SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2)= 'END' ;
/*
IF SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2)= 'END' THEN
NULL;
END IF;
*/
LC := 'R'||I||'C2';
DDE.REQUEST (docid, LC, buffer2, DDE.CF_TEXT, 200);
LC := 'R'||I||'C3';
DDE.REQUEST (docid, LC, buffer3, DDE.CF_TEXT, 200);
LC := 'R'||I||'C4';
DDE.REQUEST (docid, LC, buffer4, DDE.CF_TEXT, 200);
LC := 'R'||I||'C5';
DDE.REQUEST (docid, LC, buffer5, DDE.CF_TEXT, 200);
LC := 'R'||I||'C6';
DDE.REQUEST (docid, LC, buffer6, DDE.CF_TEXT, 200);
LC := 'R'||I||'C7';
DDE.REQUEST (docid, LC, buffer7, DDE.CF_TEXT, 200);
LC := 'R'||I||'C8';
DDE.REQUEST (docid, LC, buffer8, DDE.CF_TEXT, 200);
LC := 'R'||I||'C9';
DDE.REQUEST (docid, LC, buffer9, DDE.CF_TEXT, 200);
LC := 'R'||I||'C10';
DDE.REQUEST (docid, LC, buffer10, DDE.CF_TEXT, 200);
LC := 'R'||I||'C11';
DDE.REQUEST (docid, LC, buffer11, DDE.CF_TEXT, 200);
LC := 'R'||I||'C12';
DDE.REQUEST (docid, LC, buffer12, DDE.CF_TEXT, 200);
LC := 'R'||I||'C13';
DDE.REQUEST (docid, LC, buffer13, DDE.CF_TEXT, 200);
LC := 'R'||I||'C14';
DDE.REQUEST (docid, LC, buffer14, DDE.CF_TEXT, 200);
LC := 'R'||I||'C15';
DDE.REQUEST (docid, LC, buffer15, DDE.CF_TEXT, 200);
LC := 'R'||I||'C16';
DDE.REQUEST (docid, LC, buffer16, DDE.CF_TEXT, 200);
LC := 'R'||I||'C17';
DDE.REQUEST (docid, LC, buffer17, DDE.CF_TEXT, 200);
LC := 'R'||I||'C18';
DDE.REQUEST (docid, LC, buffer18, DDE.CF_TEXT, 200);
LC := 'R'||I||'C19';
DDE.REQUEST (docid, LC, buffer19, DDE.CF_TEXT, 200);
/*LC := 'R'||I||'C20';
DDE.REQUEST (docid, LC, buffer20, DDE.CF_TEXT, 200);
LC := 'R'||I||'C21';
DDE.REQUEST (docid, LC, buffer21, DDE.CF_TEXT, 200);
LC := 'R'||I||'C22';
DDE.REQUEST (docid, LC, buffer22, DDE.CF_TEXT, 200);
*/
/*
IF buffer1 is not null then
INSERT INTO CHASSIS_ISDM_EXCEL VALUES(SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2),
SUBSTR(buffer2,1,LENGTH(buffer2)-2),SUBSTR(buffer3,1,LENGTH(buffer3)-2),SUBSTR(buffer4,1,LENGTH(buffer4)-2),SUBSTR(buffer5,1,LENGTH(buffer5)-2),
SUBSTR(buffer6,1,LENGTH(buffer6)-2),SUBSTR(buffer7,1,LENGTH(buffer7)-2),SUBSTR(buffer8,1,LENGTH(buffer8)-2),SUBSTR(buffer9,1,LENGTH(buffer9)-2),
SUBSTR(buffer10,1,LENGTH(buffer10)-2),SUBSTR(buffer11,1,LENGTH(buffer11)-2),SUBSTR(buffer12,1,LENGTH(buffer12)-2),SUBSTR(buffer13,1,LENGTH(buffer13)-2),
SUBSTR(buffer14,1,LENGTH(buffer14)-2),SUBSTR(buffer15,1,LENGTH(buffer15)-2),SUBSTR(buffer16,1,LENGTH(buffer16)-2),SUBSTR(buffer17,1,LENGTH(buffer17)-2),
SUBSTR(buffer18,1,LENGTH(buffer18)-2),null);
forms_ddl('COMMIT');
end if;
*/
go_block('CHASSIS_ISDM_EXCEL');
:CHASSIS_ISDM_EXCEL.sl_no := SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2);
:CHASSIS_ISDM_EXCEL.stu := SUBSTR(BUFFER2,1,LENGTH(BUFFER2)-2);
:CHASSIS_ISDM_EXCEL.make := SUBSTR(BUFFER3,1,LENGTH(BUFFER3)-2);
:CHASSIS_ISDM_EXCEL.invoiceno := SUBSTR(BUFFER4,1,LENGTH(BUFFER4)-2);
:CHASSIS_ISDM_EXCEL.invdate := SUBSTR(BUFFER5,1,LENGTH(BUFFER5)-2);
:CHASSIS_ISDM_EXCEL.chassisno := SUBSTR(BUFFER6,1,LENGTH(BUFFER6)-2);
:CHASSIS_ISDM_EXCEL.wbase := SUBSTR(BUFFER7,1,LENGTH(BUFFER7)-2);
:CHASSIS_ISDM_EXCEL.paid_date := SUBSTR(BUFFER8,1,LENGTH(BUFFER8)-2);
:CHASSIS_ISDM_EXCEL.baseprice := SUBSTR(BUFFER9,1,LENGTH(BUFFER9)-2);
:CHASSIS_ISDM_EXCEL.less := SUBSTR(BUFFER10,1,LENGTH(BUFFER10)-2);
:CHASSIS_ISDM_EXCEL.ex_duty := SUBSTR(BUFFER11,1,LENGTH(BUFFER11)-2);
:CHASSIS_ISDM_EXCEL.sale_tax:= SUBSTR(BUFFER12,1,LENGTH(BUFFER12)-2);
:CHASSIS_ISDM_EXCEL.del_date := SUBSTR(BUFFER13,1,LENGTH(BUFFER13)-2);
:CHASSIS_ISDM_EXCEL.due_date:= SUBSTR(BUFFER14,1,LENGTH(BUFFER14)-2);
:CHASSIS_ISDM_EXCEL.pdi:= SUBSTR(BUFFER15,1,LENGTH(BUFFER15)-2);
:CHASSIS_ISDM_EXCEL.gross_tot := SUBSTR(BUFFER16,1,LENGTH(BUFFER16)-2);
:CHASSIS_ISDM_EXCEL.discount := SUBSTR(BUFFER17,1,LENGTH(BUFFER17)-2);
:CHASSIS_ISDM_EXCEL.netpaid := SUBSTR(BUFFER18,1,LENGTH(BUFFER18)-2);
:CHASSIS_ISDM_EXCEL.ERRORMSG := SUBSTR(BUFFER19,1,LENGTH(BUFFER19)-2);
--:excel_test.id := SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2);
--:excel_test.Name:= SUBSTR(BUFFER2,1,LENGTH(BUFFER2)-2);
--:excel_test.Amt := SUBSTR(BUFFER3,1,LENGTH(BUFFER3)-2);
exit when SUBSTR(BUFFER19,1,LENGTH(BUFFER19)-2) is null;
next_record;
I := I+1;
END LOOP;
DDE.TERMINATE(docid);
DDE.TERMINATE(convid);
DDE.APP_END(appid);
EXCEPTION
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('WINDOWS APPLICATION CANNOT START.');
WHEN DDE.DDE_PARAM_ERR THEN
MESSAGE('A NULL VALUE WAS PASSED TO DDE');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('DDE CANNOT ESTABLISH A CONVERSATION');
WHEN DDE.DMLERR_NOTPROCESSED THEN
MESSAGE('A TRANSACTION FAILED');
END;
|
|
|
|
| Re: Reading data from excel file and load into forms [message #599653 is a reply to message #599651] |
Mon, 28 October 2013 01:13 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Error you got means the following (by the way, I believe that you should - by now - be capable of figuring that out yourself. Oracle error codes can easily be found in the Internet):Oracle
ORA-06502: PL/SQL: numeric or value errorstring
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Therefore, check whether your data (row 101?) meets criteria in order to be successfully loaded into form items.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 23:47:18 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
