Home » Developer & Programmer » Forms » How To import data through forms from excel  () 1 Vote
How To import data through forms from excel [message #86287] Sat, 18 September 2004 00:25 Go to next message
Raja Das
Messages: 42
Registered: July 2004
Member
Hi All,

How To import data through forms from excel.I have an excel sheet and i have to import these sheet to oracle.but through forms how should i do.

Thanks and

regards..

Raja
Re: How To import data through forms from excel [message #86299 is a reply to message #86287] Mon, 20 September 2004 09:03 Go to previous messageGo to next message
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;
Re: How To import data through forms from excel [message #260061 is a reply to message #86299] Fri, 17 August 2007 05:42 Go to previous messageGo to next message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
Thanks Sir... that was a real wonderful suggestion and coding... thanks a lot..
Re: How To import data through forms from excel [message #260122 is a reply to message #260061] Fri, 17 August 2007 09:05 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Samir Please format your code while posting.
For knowing how to format follow the link HOW TO FORMAT.


Cheers
Sanka
Re: How To import data through forms from excel [message #278355 is a reply to message #86287] Sat, 03 November 2007 05:02 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

i have problem in following string

APPID := DDE.APP_BEGIN('D:Program FilesMicrosoft OfficeOffice10EXCEL.EXE',
because if i run it in multiple machines then it will give me problem due to diff path of excel.exe

i have done more rnd in that but not found the solution

can anyone have solution

Regareds

Ganesh Jadhav

Re: How To import data through forms from excel [message #279535 is a reply to message #278355] Thu, 08 November 2007 23:00 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you tried just using 'excel.exe' without the full path name?

David
Re: How To import data through forms from excel [message #280298 is a reply to message #279535] Tue, 13 November 2007 02:10 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

yes, i had checked it out but its not working
Re: How To import data through forms from excel [message #281174 is a reply to message #280298] Fri, 16 November 2007 00:03 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you looked at all the entries in this forum containing 'dde app_begin'?

David
Re: How To import data through forms from excel [message #289515 is a reply to message #86287] Sun, 23 December 2007 03:56 Go to previous messageGo to next message
salwa
Messages: 76
Registered: December 2007
Member
plse give me fmb.
all doc.
e-mail titu_3038@yahoo.com
Re: How To import data through forms from excel [message #289517 is a reply to message #86287] Sun, 23 December 2007 04:53 Go to previous messageGo to next message
salwa
Messages: 76
Registered: December 2007
Member
as_FileName means?????
Re: How To import data through forms from excel [message #347726 is a reply to message #289515] Sat, 13 September 2008 04:24 Go to previous messageGo to next message
deepsahara
Messages: 2
Registered: September 2008
Junior Member
please send me fmb if possible. my id is deep1297@rediffmail.com
Re: How To import data through forms from excel [message #347727 is a reply to message #347726] Sat, 13 September 2008 04:28 Go to previous messageGo to next message
deepsahara
Messages: 2
Registered: September 2008
Junior Member
i m using oracle forms 9i and want to convert data (which is in forms) to excel format.But when file is generate save to client side not in serverside.But forms is run server.So please help me and give a small fmb with only to column in table. my id is deep1297@rediffmail.com
Re: How To import data through forms from excel [message #347936 is a reply to message #347727] Mon, 15 September 2008 02:17 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Sorry. This site does not 'send solutions'. You have to work it out for yourself.

Try the search 'client excel' to show threads using the 'webutil' ' client_' functions and 'excel'.

David
Re: How To import data through forms from excel [message #389285 is a reply to message #86299] Sat, 28 February 2009 01:46 Go to previous messageGo to next message
haf1971
Messages: 2
Registered: February 2009
Location: Tehran
Junior Member

hi samir

your code is really well.
would you please send me your fmb file?
it's kind of you.

best regards
haf
Re: How To import data through forms from excel [message #389287 is a reply to message #86299] Sat, 28 February 2009 01:54 Go to previous messageGo to next message
haf1971
Messages: 2
Registered: February 2009
Location: Tehran
Junior Member

hi samir
my email is akbarifard@mapna.com

regards
haf
Re: How To import data through forms from excel [message #471461 is a reply to message #86299] Sat, 14 August 2010 11:56 Go to previous messageGo to next message
riaddba
Messages: 1
Registered: August 2010
Location: Bangladesh
Junior Member

please give me fmb.and all doc.

please sir help me. I need this file and doc very urgent.
and FMS.PKG_FMS_VALID_DATA this package is also send me pls.
e-mail::: riad.khan@yahoo.com

Re: How To import data through forms from excel [message #471462 is a reply to message #471461] Sat, 14 August 2010 11:59 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Locking the topic, since it is turning into a me-too thread.
Previous Topic: R12 Form error
Next Topic: Old Value on Update
Goto Forum:
  


Current Time: Fri Dec 02 12:08:55 CST 2016

Total time taken to generate the page: 0.12072 seconds