4 Replies Latest reply: Jan 30, 2013 2:34 AM by Christian Erlinger RSS

    How to load the data from excel file into temprory table in Forms 11g?

    user7197586
      Hi

      How to Load the data from excel file(Extension is .CSV) into the temporary table of oracle in Forms11g.

      My Forms Version is - Forms [64 Bit] Version 11.1.2.0.0 (Production)

      Kindly Suggest the Solution.


      Regards,
      Sachin
        • 1. Re: How to load the data from excel file into temprory table in Forms 11g?
          Christian Erlinger
          This questoin has been asked quite often, you most certainly will find an answer fitting to your needs if you search the forum/google. Depending on your needs you could extend your search to SQL*Loader, external tables and (client_)text_io.

          cheers
          • 2. Re: How to load the data from excel file into temprory table in Forms 11g?
            user7197586
            Hi Please provide the solution how to load the data from excel file into table?



            kindly suggest



            Regards,
            Sachin
            • 3. Re: How to load the data from excel file into temprory table in Forms 11g?
              Hani
              Declare
                  v_full_filename         varchar2(500);
                  v_server_path           varchar2(2000);
                  v_separator             VARCHAR2(1);
                  v_filename              VARCHAR2(400);
                  filename                VARCHAR2 (100);
                  v_stop_load             varchar2 (2000);
                  v_rec_error_log         varchar2(4000);
                  v_error_log             varchar2(4000);
                  ctr                     NUMBER (12);
                  cols                    NUMBER (2);
                  btn                     number;
                  RES                     BOOLEAN;    
                  application             ole2.obj_type;
                  workbooks               ole2.obj_type;
                  workbook                ole2.obj_type;
                  worksheets              ole2.obj_type;
                  worksheet               ole2.obj_type;
                  cell                    ole2.obj_type;
                  cellType                ole2.OBJ_TYPE; 
                  args                    ole2.obj_type;
              
                  PROCEDURE olearg
                  IS
                  args   ole2.obj_type;
                  BEGIN
                  args := ole2.create_arglist;
                  ole2.add_arg (args, ctr);                                 
                  ole2.add_arg (args, cols);                                    
                  cell := ole2.get_obj_property (worksheet, 'Cells', args); 
                  ole2.destroy_arglist (args);
                  END;
              
              BEGIN
              
              
              v_full_filename := client_get_file_name(directory_name => null 
                                               ,file_name      => null
                                               ,file_filter    => 'Excel  files (*.xls)|*.xls|'   
                                                                                      ||'Excel  files (*.xlsx)|*.xlsx|'                                                                  
                                               ,message        => 'Choose Excel file'
                                               ,dialog_type    => null
                                               ,select_file    => null
                                                   );
                                          
              If v_full_filename is not null Then
              
              v_separator := WEBUTIL_CLIENTINFO.Get_file_Separator ;
              v_filename := v_separator||v_full_filename ;
              
              :LOAD_FILE_NAME := substr(v_filename,instr(v_filename,v_separator,-1) + 1);                                 
              
              RES := Webutil_File_Transfer.Client_To_AS(v_full_filename,"server_path"||substr(v_filename,instr(v_filename,v_separator,-1) + 1));      
              
              -----------------------------------------------------------------------------------------
              -----------------------------------------------------------------------------------------
              
              --Begin load data from EXCEL
              BEGIN
                  filename := v_server_path||substr(v_filename,instr(v_filename,v_separator,-1) + 1); -- to pick the file
              
              
                  application := ole2.create_obj ('Excel.Application');
                  ole2.set_property (application, 'Visible', 'false');
                  workbooks := ole2.get_obj_property (application, 'Workbooks');
                  args := ole2.create_arglist;
              
                  ole2.add_arg (args, filename); -- file path and name
              
                  workbook := ole2.get_obj_property(workbooks,'Open',args);
              
                  ole2.destroy_arglist (args);
                  args := ole2.create_arglist;
                  ole2.add_arg (args, 'Sheet1');
                  worksheet := ole2.get_obj_property (workbook, 'Worksheets', args);
                  ole2.destroy_arglist (args);
              
                  ctr := 2;                                                     --row number
                  cols := 1;                                                -- column number
                          
              
                  go_block('xxx');
                  FIRST_RECORD;   
              
                  LOOP        
              
                          --Column 1 VALUE --------------------------------------------------------------------
                      olearg;
                      v_stop_load := ole2.get_char_property (cell, 'Text'); --cell value of the argument
                          
                      :item1 := v_stop_load;
                      cols := cols + 1;                                                       
                                          
                        --Column 2 VALUE --------------------------------------------------------------------
                      olearg;
                     
              
                      :item2 := ole2.get_char_property (cell, 'Text'); --cell value of the argument
                      cols := cols + 1;
                          
                      --<and so on>
                      
                  ole2.invoke (application, 'Quit');
                  ole2.RELEASE_OBJ (cell);
                  ole2.RELEASE_OBJ (worksheet);
                  ole2.RELEASE_OBJ (worksheets);
                  ole2.RELEASE_OBJ (workbook);
                  ole2.RELEASE_OBJ (workbooks);
                  ole2.RELEASE_OBJ (application);
                  
                                                           
                      
              END;
              --End load data from EXCEL
              Please mark it as answered if you helped.
              • 4. Re: How to load the data from excel file into temprory table in Forms 11g?
                Christian Erlinger
                depending on your requirement there are different solutions.

                http://www.google.com/search?q=load+csv+oracle+-site%3Adba-oracle.com

                cheers