Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    30

    Question Unanswered: Excel to Oracle Table

    I want to insert values into Oracle Table from an Excel Sheet.

    Which way i can use for this if there is any software. which can easily do this.

    I will be very thank full to all who help and evrn who think a littl eabout this.

    With best regards.
    Kashif

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    One easy way is to save the Excel sheet as a CSV file, and then use SQL Loader to load from the CSV file into the table.

  3. #3
    Join Date
    Dec 2003
    Posts
    30
    Quote Originally Posted by andrewst
    One easy way is to save the Excel sheet as a CSV file, and then use SQL Loader to load from the CSV file into the table.

    I dont know about SQL Loader. please tell me a little about this.
    Kashif

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SQL Loader is an Oracle utility for loading data from flat files into tables. You have to create a "control file" which looks something like this example (for CSV files):
    Code:
    LOAD DATA
    INFILE "C:\tony\empdata.csv"
    APPEND
    INTO TABLE emp
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    ( empno
    , ename
    , job
    , mgr
    , hiredate date "DD-MON-YYYY"
    , sal
    , comm
    , deptno
    )
    The datafile for the standard scott.emp table would look like this:
    Code:
    "7369","SMITH","CLERK","7902","17-dec-1980","800","","20"
    "7499","ALLEN","SALESMAN","7698","20-feb-1981","1600","300","30"
    "7521","WARD","SALESMAN","7698","22-feb-1981","1250","500","30"
    "7566","JONES","MANAGER","7839","02-apr-1981","2975","","20"
    "7654","MARTIN","SALESMAN","7698","28-sep-1981","1250","1400","30"
    "7698","BLAKE","MANAGER","7839","01-may-1981","2850","","30"
    "7782","CLARK","MANAGER","7839","09-jun-1981","2572.5","","10"
    "7788","SCOTT","ANALYST","7566","09-dec-1982","3000","","20"
    "7839","KING","PRESIDENT","","17-nov-1981","5250","","10"
    "7844","TURNER","SALESMAN","7698","08-sep-1981","1500","0","30"
    "7876","ADAMS","CLERK","7788","12-jan-1983","1100","","20"
    "7900","JAMES","CLERK","7698","03-dec-1981","950","","30"
    "7902","FORD","ANALYST","7566","03-dec-1981","3000","","20"
    "7934","MILLER","CLERK","7782","23-jan-1982","1365","","10"
    You would run SQL Loader from the command line like this:

    sqlldr USERID=scott/tiger CONTROL=empdata.ctl

    See the SQL Loader docs for more details.

  5. #5
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    44
    Save the excel file and CSV (Comma Seperated File).
    Write the control file which reads this CSV file and loads into database table.
    Example :
    Filename : temp.ctl
    load data infile 'temp.csv'
    append into table temp_table
    fields terminated by ","
    (field1,
    field2,
    field3,
    field4
    )

    Note : temp.csv is the csv file created. temp.ctl file is control file.
    field1, field2, field3 are the column names of the database table and temp_table is the database table name. Command to run the sql loader :
    sqlldr dbusernam/dbpassword@connectstring control=temp.ctl
    This command will read the CSV file through control file and loads the data into temp_table database table. There are lot of options u can give while loading the data through CSV file.
    Thanks and Regards,
    Mahesh

  6. #6
    Join Date
    Dec 2003
    Posts
    30
    Thanx u very much
    Kashif

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •