• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3642
  • Last Modified:

Insert excel data into oracle table

How do I insert records into a oracle table using an excel file as the data source? Env. is ASP.net 1.1 with vb.net.
0
PDMD_Support
Asked:
PDMD_Support
1 Solution
 
Jai STech ArchCommented:
assuming that you are using a WEB PAGE...you can do this

1) upload the file to a temp directory in your server...
2) oparse the excel sheet ( you have to know what the excel sheet contains)
3) if the excel contains static columns(with varying data) you can load it into a DATASET / DATATABLE
4) you open a connection to your database...using ORACLE connection string...you can use OLEDB ir OracleClient available for .net3
5) take the value from your datatable and save to to your database table...

0
 
dragyCommented:
Hi PDMD_Support
I was doing this by Oracle Forms and DDE package. Here is an example of a sample procedure in WHEN-BUTTON-PRESSED event which export data from oracle table to excel:

DECLARE
 appid PLS_INTEGER;
 convid PLS_INTEGER;
 docid PLS_INTEGER;
 i number;
 j number;
 rowcol varchar2(10);
 rowcol1 varchar2(10);
 str varchar2(4000);
 filename varchar2(400);

BEGIN
go_block('MY_BLOCK');
First_Record;

APPID := DDE.APP_BEGIN('C:\Program Files\Microsoft Office\Office\excel.exe', DDE.APP_MODE_MAXIMIZED);

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; -- loop
END LOOP;

DDE.EXECUTE(convid, '[Open("C:\LOCATION_OF_XLS_FILE.xls")]', 150000);
docid := DDE.INITIATE('excel', 'C:\LOCATION_OF_XLS_FILE.xls');

 i :=2;
while :MY_BLOCK.field1_from_my_block is not null loop
 j := 1;

If :MY_BLOCK.field_you_want_to_write_to_excel_from_my_block is null then
        str := ' -';
 Else
      str := :MY_BLOCK.field_you_want_to_write_to_excel_from_my_block;
End if;  
        rowcol1 := 'R1C' || to_char(j);
        DDE.POKE(docid, rowcol1, 'name_of_field', DDE.CF_TEXT, 50000);
        rowcol := 'R' || to_char(i) || 'C' || to_char(j);      
      DDE.POKE(docid, rowcol, str, DDE.CF_TEXT, 50000);  
      j := j + 1;
.
. here you should put all the fields you want to insert in excel
.
 j := j + 1;      
 i := i + 1;
next_record;
end loop;
filename := 'C:\Sifarnici\Poziv_izvjestaja\Excel_dokumenti\upitnik\upitnik-' || :GLOBAL.korisnicko_ime || '-' || systime || '.xls';

DDE.EXECUTE(convid, '[Save.As("' || filename || '")]', 150000);

DDE.TERMINATE(docid);

DDE.TERMINATE(convid);

DDE.APP_END(APPID);


/* 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');
when DDE.DMLERR_DATAACKTIMEOUT then
MESSAGE('Time out!');
/* End of trigger */
END;

 The same is inserting from excel to oracle table, I didn't do this but here is the link on page where man does similar work:
http://209.85.135.104/search?q=cache:SphVFiNvviUJ:forums.oracle.com/forums/thread.jspa%3FmessageID%3D2112312%26tstart%3D0+oracle%2Bforms%2Bdde%2Binsert%2Bfrom%2Bexcel%2Bin%2Boracle&hl=bs&ct=clnk&cd=1&gl=ba

I hope that this will help you.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now