Forum Stats

  • 3,783,401 Users
  • 2,254,768 Discussions
  • 7,880,380 Comments

Discussions

how to copy data from excel to oracle forms

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

regards

Comments

  • 447371
    447371 Member Posts: 1
    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.
  • hardcodr
    hardcodr Member Posts: 85
    you can try this utility from sourceforge
    http://sourceforge.net/projects/quickload
  • 445547
    445547 Member Posts: 242
    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
  • 578539
    578539 Member Posts: 1
    edited May 20, 2007 8:55AM
    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
This discussion has been closed.