2 Replies Latest reply: May 17, 2013 7:42 AM by 860409 RSS

    Importing data from Excel sheet to Oracle 6i Form

    danish_rizvi
      Scenariio is as below :
      I have a Customer excel sheet with column names say, S_NO, CODE, NAME, LOCATION. I want to upload the data into the Oracle Form 6i.

      Please Help how to import data into the Oracle Form.
        • 1. Re: Importing data from Excel sheet to Oracle 6i Form
          970826
          Dear Danish,

          You can read data from Excel using using ole2 package.

          You can find an example in the following thread: map excel columns on oracle forms and insert it into database
          • 2. Re: Importing data from Excel sheet to Oracle 6i Form
            860409
            Hi Danish,

            you can use client_text_io.file_type as given below

            create tabular form
            and use this code

            declare
            v_get_file VARCHAR2(100);
            v_handle_file client_TEXT_IO.FILE_TYPE;
            v_retreived_storage VARCHAR2(3000);
            v_pst number;
                 DELIMETER varchar2(1):=',';
            BEGIN
            GO_BLOCK('BLOCK');

            :SYSTEM.MESSAGE_LEVEL:=5;
            v_get_file:=client_GET_FILE_NAME('*.*');
            v_handle_file:=client_TEXT_IO.FOPEN(v_get_file,'R');

            LOOP

            client_TEXT_IO.GET_LINE(v_handle_file,v_retreived_storage);
            :BLOCK.Rcrd_no := TO_NUMBER(:system.cursor_record) ;

            /*************Repeat this step for every column**************/
            SELECT INSTR(v_retreived_storage,DELIMETER)
            INTO v_pst
            from dual;

            IF v_pst > 0 then

            :BLOCK.S_NO:=SUBSTR(v_retreived_storage,1,INSTR(v_retreived_storage,DELIMETER,1)-1);
            v_retreived_storage:=SUBSTR(v_retreived_storage,INSTR(v_retreived_storage,DELIMETER,1)+1);
            else
            :BLOCK.S_NO:=v_retreived_storage;
            v_retreived_storage:= null;
            end if;
            /**********************************************************/

            end loop;
            end;


            Best luck
            Kashif