Sunday, August 30, 2009

Data Trasnfer From Excel to Oracle Developer

PROCEDURE Prc_get_from_xls(p_File_Path_File_Name varchar2) IS

 CONVID PLS_INTEGER;
 APPID PLS_INTEGER;
 i number;
 x number;
 v_card varchar2(500) :=null;
 v_actnum varchar2(500) :=null;
 v_amount varchar2(500) :=null;
 v_table varchar2(4000);
 OUT_FILR TEXT_IO.FILE_TYPE;

BEGIN
  synchronize;
  Appid := dde.app_begin('C:\Program Files\Microsoft Office\Office\excel.exe '||p_File_Path_File_Name,dde.app_mode_minimized);
  dde.app_focus(appid);
  convid := dde.initiate('EXCEL.EXE','Sheet1' );

  go_block('temp');
 
  x := 0;

  FOR I IN 2..1000 loop
  dde.request(convid,'R' || to_char(i) ||'C1',v_card ,dde.cf_text,100000);
  dde.request(convid,'R' || to_char(i) ||'C2',v_actnum,dde.cf_text,100000);
  dde.request(convid,'R' || to_char(i) ||'C3',v_amount,dde.cf_text,100000);
 
  if substr(v_card ,1,length(v_card)-2) is null
  then exit;
  end if;
 
  :temp.col1:= substr(v_card ,1,length(v_card)-2 );
  :temp.col2:= substr(v_actnum,1,length(v_actnum)-2);
  :temp.col3:= substr(v_amount,1,length(v_amount)-2);

  next_record;
 
  x:= x + 1;
  end loop;

  dde.terminate(convid);
  dde.app_end(appid);
  first_record;
 
Exception
 when others then msgbox(sqlerrm);
END;

No comments:

Post a Comment