|
|
| Re: How To import data through forms from excel [message #86299 is a reply to message #86287] |
Mon, 20 September 2004 09:03   |
Samir Patel
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
I have pasted the code we used to achieve this functionality and documented it .
We are importing 4 fields of information from the Excel file and doing validation and placing an error comment in the Excel file processed , for the rows which fails import. We do NOT bring the rows that failed validation on the form.
The code is for a Forms Program Unit and called it IMPORT_FROM_EXCEL_PROC.
When you will create the program unit paste this code in the Program Unit Text for the Program unit and please change the Datablock name B_CAPITAL_PROJECT_ID to suit your datablock name and the call to the package FMS.PKG_FMS_VALID_DATA should also be changed to do what you want to do for your needs to the business application.
Let me know if you need any more help.
Hope it helps.
Samir.
PROCEDURE IMPORT_FROM_EXCEL_PROC (as_FileName VARCHAR2)IS
appid PLS_INTEGER;
convid PLS_INTEGER;
docid PLS_INTEGER;
conv_established BOOLEAN := FALSE;
buffer VARCHAR2(100); /* This determines the maximum size
of the values being returned from
Excel. Adjust it if your values exceed
this. */
it_project Item;
it_award Item;
ls_project VARCHAR2(25);
ls_award VARCHAR2(15);
ls_capital VARCHAR2(9);
ls_occurrence VARCHAR2(3);
ls_error VARCHAR2(100);
li_row integer;
li_col integer;
li_error_count integer;
li_load_count integer;
BEGIN
--Resetting LOV on text items
it_project := Find_Item('B_CAPITAL_PROJECT_ID.PROJECT_NUM');
Set_Item_Property(it_project,LOV_NAME,'');
it_award := Find_Item('B_CAPITAL_PROJECT_ID.AWARD_NUMBER');
Set_Item_Property(it_award,LOV_NAME,'');
--Start Excel
--This line assumes that Excel is in the specified directory
APPID := DDE.APP_BEGIN('D:Program FilesMicrosoft OfficeOffice10EXCEL.EXE',
DDE.APP_MODE_MINIMIZED);
--Establish a conversation with Excel
--The following loop will not end until a conversation with Excel
--has been established. Therefore, it can result in a endless loop,
--so use with caution.
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;
--Open Excel document
--This assumes that you have an Excel spreadsheet named ddetest.xls in the root of c:
DDE.EXECUTE(convid, '[[Open("' || as_FileName || '")]]', 10000);
--Initiate conversation with Excel document
docid := DDE.INITIATE('excel', as_FileName);
--Begin transfer from Excel to Forms
li_load_count := 0;
li_error_count := 0;
li_col := 1;
li_row := 2;
GO_BLOCK('B_CAPITAL_PROJECT_ID');
DDE.REQUEST (docid, 'R' || TO_CHAR(li_row) || 'C' || TO_CHAR(li_col), buffer, DDE.CF_TEXT, 1000);
WHILE length(buffer) > 2 LOOP
WHILE li_col < 5 LOOP
buffer := substr(buffer, 1, instr(buffer, chr(10)) - 2);
IF li_col = 1 THEN --Project
IF FMS.PKG_FMS_VALID_DATA.f_ValidProject(buffer) THEN
ls_project := buffer;
ELSE
ls_error := 'Invalid Project';
END IF;
ELSIF li_col = 2 THEN --Award
IF FMS.PKG_FMS_VALID_DATA.f_ValidAwardProj(ls_project, buffer) THEN
ls_award := buffer;
IF NOT FMS.PKG_FMS_VALID_DATA.f_DupAwardProj(ls_project, ls_award) THEN
ls_error := 'Duplicate Award and Project Combination';
END IF;
--Condition added by Samir Patel on 9/1/2004 to check if imported
-- Projects/Awards are not in RETIRED table.
IF FMS.PKG_FMS_VALID_DATA.f_RetiredAwardProj(ls_project, ls_award) THEN
ls_error := 'Award and Project Combination in RETIRED Table';
END IF;
ELSE
ls_error := 'Invalid Award or Invalid Project and Award Combination';
END IF;
ELSIF li_col = 3 THEN --Capital Project
IF FMS.PKG_FMS_VALID_DATA.f_ValidCapitalProj(buffer) THEN
ls_capital := buffer;
ELSE
ls_error := 'Invalid Capital Project';
END IF;
ELSE --Occurrence
IF FMS.PKG_FMS_VALID_DATA.f_ValidOccurrrence(buffer) THEN
ls_occurrence := buffer;
ELSE
ls_error := 'Invalid Occurrence';
END IF;
END IF;
IF nvl(ls_error,'N') = 'N' THEN
li_col := li_col + 1;
DDE.REQUEST (docid, 'R' || TO_CHAR(li_row) || 'C' || TO_CHAR(li_col), buffer, DDE.CF_TEXT, 1000);
ELSE
li_col := 5;
END IF;
END LOOP;
IF nvl(ls_error, 'N') = 'N' THEN
li_load_count := li_load_count + 1;
IF li_load_count = 1 THEN
FIRST_RECORD;
ELSE
NEXT_RECORD;
END IF;
:B_CAPITAL_PROJECT_ID.PROJECT_NUM := ls_project;
:B_CAPITAL_PROJECT_ID.AWARD_NUMBER := ls_award;
:B_CAPITAL_PROJECT_ID.CAPITAL_PROJECT_ID := ls_capital;
:B_CAPITAL_PROJECT_ID.OCCURRENCE := ls_occurrence;
ELSE
li_error_count := li_error_count + 1;
DDE.POKE(docid, 'R' || TO_CHAR(li_row) || 'C5', ls_error, DDE.CF_TEXT, 10000);
END IF;
ls_error := '';
ls_project := '';
ls_award := '';
ls_capital := '';
ls_occurrence := '';
li_col := 1;
li_row := li_row + 1;
DDE.REQUEST (docid, 'R' || TO_CHAR(li_row) || 'C' || TO_CHAR(li_col), buffer, DDE.CF_TEXT, 1000);
END LOOP;
IF li_error_count > 0 THEN
DDE.EXECUTE(convid, '[[save]]', 10000);
Message(TO_CHAR(li_load_count) || ' Record(s) Loaded. ' ||
TO_CHAR(li_error_count) || ' Invalid Record(s). See error in excel file.');
ELSE
Message(TO_CHAR(li_load_count) || ' Record(s) Successfully Loaded.');
END IF;
--End transfer to Excel
DDE.TERMINATE(docid);
DDE.TERMINATE(convid);
DDE.APP_END(appid);
--Attaching LOV on text items
it_project := Find_Item('B_CAPITAL_PROJECT_ID.PROJECT_NUM');
Set_Item_Property(it_project,LOV_NAME,'PROJECT');
it_award := Find_Item('B_CAPITAL_PROJECT_ID.AWARD_NUMBER');
Set_Item_Property(it_award,LOV_NAME,'AWARD');
--Handle exceptions
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;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
