Forum Stats

  • 3,852,396 Users
  • 2,264,102 Discussions
  • 7,905,058 Comments

Discussions

Import excel data into database

Asad Masood
Asad Masood Member Posts: 7
edited Jul 9, 2018 2:54AM in Forms

I want to import data from excel file into oracle database 11 g using forms 10 g. For this I googled many times and got the solution but this solution is not working in my application.

The solution is given below. Kindly give me the right solution. Thanks in advance for help.

PROCEDURE IMPORT_EXCEL_PROC IS

application    OLE2.Obj_Type;

workbooks      OLE2.Obj_Type;

workbook       OLE2.Obj_Type;

worksheets      OLE2.Obj_Type;

worksheet       OLE2.Obj_Type;

cell                  OLE2.OBJ_TYPE;

args                 OLE2.OBJ_TYPE;

cell_value         varchar2(100);

cell_value_num NUMBER;

num_wrkshts    NUMBER;

wksht_name     VARCHAR2(250);

eod                   Boolean := false;

j                        integer := 2;

v_fName          VARCHAR2(450);

BEGIN

v_fName := GET_FILE_NAME(directory_name => 'C:\IMPORT\',File_Filter => 'Excel Files (*.XLS)|*.XLS|',message => 'Select Client filename to Open.');

IF ( v_fName IS NOT NULL ) THEN   -- Make sure the user selected a file

The following sets up communication with the excel spreadsheet:

application := OLE2.create_obj('Excel.Application');  -- Open the OLE application

OLE2.set_property(application,'Visible','false');    -- Keep the application hidden

OLE2.set_property(application,'DisplayAlerts','false');

workbooks := OLE2.Get_Obj_Property(application, 'Workbooks');

args := OLE2.CREATE_ARGLIST;

OLE2.add_arg(args,v_fName);

workbook := OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args); --Open the selected File

OLE2.destroy_arglist(args);

worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');

num_wrkshts := OLE2.GET_NUM_PROPERTY(worksheets, 'Count');  -- Get number of worksheets

worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');

Go_Item('GL_DAILY_COMPOUND.BRANCH');  --Go to the last record

last_record;

loop    -- Loop through the Block and create a new row if needed.   

exit when eod;   -- Exit when the last row of the spreadsheet is reached.     

for k in 1..7 loop  --7 fields per record   -- Loop through the spreadsheet and get cell values

  args:= OLE2.create_arglist;

  OLE2.add_arg(args, j);

  OLE2.add_arg(args, k);

  cell:= OLE2.get_obj_property(worksheet, 'Cells', args);

  OLE2.destroy_arglist(args);

  IF K=1 THEN

  Cell_value_num :=OLE2.get_num_property(cell, 'Value');

  if cell_value_num=0 or cell_value_num is null then   --Check for End of Data…

            eod:=true;

            IF J>1 THEN

             Message(j-1||' Records are imported successfully.');

             Message(j-1||' Records are imported successfully.');

INSERT INTO GL_COMPOUND_HEADER_EXCEL(EXL_DATE, EXL_USER, EXL_REMARK)

VALUES(SYSDATE, :GLOBAL.USER,'Records:'||TO_CHAR(j-1)||' File:'||v_fName);

     end if;

        Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE');

             exit;

   end if;

    If :system.record_status <> 'NEW' then

     create_record;

     end if;

    copy(cell_value_num,name_in('system.cursor_item'));

    else

     IF K =6 THEN

     cell_value :=OLE2.get_char_property(cell, 'Value');

     copy(cell_value,name_in('system.cursor_item'));

     else

      cell_value_num :=OLE2.get_num_property(cell, 'Value');

      copy(ROUND(cell_value_num,2),name_in('system.cursor_item'));

     end if;

    end if;

    next_item;

   end loop; --for

   j:=j+1;

  end loop;  --main loop

  IF (cell IS NOT NULL) THEN  -- Release the OLE2 object handles

  OLE2.release_obj(cell);

  END IF;

  IF (worksheet IS NOT NULL) THEN

  OLE2.release_obj(worksheet);

  END IF;

  IF (worksheets IS NOT NULL) THEN

  OLE2.release_obj(worksheets);

  END IF;

  IF (workbook IS NOT NULL) THEN

  OLE2.release_obj(workbook);

  END IF;

  IF (workbooks IS NOT NULL) THEN

  OLE2.release_obj(workbooks);

  END IF;

  OLE2.invoke(application,'Quit');

  --      IF (args IS NOT NULL) THEN

  --      OLE2.release_obj(args);

  --      END IF;

  OLE2.release_obj(application);

  ELSE

  Message('No File selected.');

  message(' ');

  RAISE Form_Trigger_Failure;

END IF;

END;

Tagged:

Answers

This discussion has been closed.