4 Replies Latest reply: May 20, 2007 7:55 AM by 578539 RSS

    how to copy data from excel to oracle forms

    Toufiq Raja
      hi all
      i want to copy the data of excel sheet into my oracle form

      regards
        • 1. Re: how to copy data from excel to oracle forms
          447371
          Excel -> CSV -> Oracle
          Save the Excel spreadsheet as file type 'CSV' (Comma-Separated Values).

          Transfer the .csv file to the Oracle server.

          Create the Oracle table, using the SQL CREATE TABLE statement to define the table's column lengths and types. Here's an example of an sqlplus 'CREATE TABLE' statement:
          CREATE TABLE SPECIES_RATINGS
          (SPECIES VARCHAR2(10),
          COUNT NUMBER,
          RATING VARCHARC2(1));

          Use sqlload to load the .csv file into the Oracle table. Create a sqlload control file like this:
          load data
          infile spec_rat.csv
          replace
          into table species_ratings
          fields terminated by ','
          (species,count,rating)

          Invoke sqlload to read the .csv file into the new table, creating one row in the table for each line in the .csv file. This is done as a Unix command:
          % sqlload userid=username/password control=<filename.ctl> log=<filename>.log

          This will create a log file <filename>.log. Check it for loading errors.
          Use these sqlplus commands to check the Oracle table:
          DESCRIBE SPECIES_RATINGS;
          SELECT COUNT(*) FROM SPECIES_RATINGS;
          SELECT * FROM SPECIES_RATINGS WHERE ROWNUM < 6;

          You're done Hakuna mattata.
          • 2. Re: how to copy data from excel to oracle forms
            hardcodr
            you can try this utility from sourceforge
            http://sourceforge.net/projects/quickload
            • 3. how to copy data from excel to oracle forms
              445547
              If the oracle is on unix server and the excell file is on my desktop which runs on windows, where will the location of parfile, logs be and how will i specify the path for excel file on unix from my windows.

              hakuna Matata rafiki
              • 4. Re: how to copy data from excel to oracle forms
                578539
                PROCEDURE get_from_xls IS

                CONVID PLS_INTEGER;
                APPID PLS_INTEGER;
                i number;
                x number;
                v_name VARCHAR2(100);
                v_BRN varchar2(10);
                v_NO varchar2(10);

                OUT_FILR TEXT_IO.FILE_TYPE;
                BEGIN

                synchronize;
                -- Appid := dde.app_begin('C:\Program Files\Microsoft Office\Office\excel.exe C:\ora_xls\creadit.xls',dde.app_mode_minimized);
                Appid := dde.app_begin('E:\Program Files\Microsoft Office\Office11\excel.exe C:\oracle_excel\EMP_ALL.xls',dde.app_mode_minimized);

                     dde.app_focus(appid);
                     convid := dde.initiate('EXCEL',/*:BLOCK2.SHEET_NAME*/'Sheet1'     );

                x := 0;

                FOR I IN 2..100000 loop
                     
                     dde.request(convid,'R' || to_char(i) ||'C1',v_brn,dde.cf_text,100000);
                     dde.request(convid,'R' || to_char(i) ||'C2',v_no,dde.cf_text,100000);
                     dde.request(convid,'R' || to_char(i) ||'C3',v_name,dde.cf_text,100000);

                if substr(v_no,1,length(v_no)-2) is null then exit;
                     
                end if;

                insert into EXCEL_TBL(
                CODE ,
                NAME )
                VALUES(
                substr(v_brn,1,length(v_brn)-2),
                substr(v_no,1,length(v_no)-2));


                x:= x + 1;
                end loop;
                COMMIT;
                     dde.terminate(convid);
                     dde.app_end(appid);
                END;

                ----------------

                Message was edited by:
                user575536