This discussion is archived
4 Replies Latest reply: Jan 30, 2013 12:34 AM by Christian Erlinger RSS

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

user7197586 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    depending on your requirement there are different solutions.

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

    cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points