This discussion is archived
7 Replies Latest reply: Mar 6, 2013 10:52 PM by 995210 RSS

Using DML order to import a .xls file

477409 Newbie
Currently Being Moderated
Hi,

I have an excel file I'd like to import into my ora10g database. I can't find the right SQL request to import these data into my empty tables.
Someone told me to convert the .xls file to a .csv file, which I did, but this is not helping !

I know it is possible, so if anybody has an idea...

thank you in advance.
  • 1. Re: Using DML order to import a .xls file
    249425 Journeyer
    Currently Being Moderated
    After converting a file to csv please use SQL Loader utility to import that file to database tables.
    You can find documentation of SQLLoader on httpL://tahiti.oracle.com

    Best Regards
    Krystian Zieja / mob
  • 2. Re: Using DML order to import a .xls file
    6363 Guru
    Currently Being Moderated
    I would save the Excel file as tab delimited, commas can get messy if they are also in the data.
    SQL> host cat /tmp/emp.dat
    7369    SMITH   CLERK   7902    17-DEC-80       800             20
    7499    ALLEN   SALESMAN        7698    20-FEB-81       1600    300     30
    7521    WARD    SALESMAN        7698    22-FEB-81       1250    500     30
    7566    JONES   MANAGER 7839    02-APR-81       2975            20
    7654    MARTIN  SALESMAN        7698    28-SEP-81       1250    1400    30
    7698    BLAKE   MANAGER 7839    01-MAY-81       2850            30
    7782    CLARK   MANAGER 7839    09-JUN-81       2450            10
    7788    SCOTT   ANALYST 7566    09-DEC-82       3000            20
    7839    KING    PRESIDENT               17-NOV-81       5000            10
    7844    TURNER  SALESMAN        7698    08-SEP-81       1500    0       30
    7876    ADAMS   CLERK   7788    12-JAN-83       1100            20
    7900    JAMES   CLERK   7698    03-DEC-81       950             30
    7902    FORD    ANALYST 7566    03-DEC-81       3000            20
    7934    MILLER  CLERK   7782    23-JAN-82       1300            40
    
    SQL> create or replace directory data_dir as '/tmp';
    
    Directory created.
    
    SQL> create table emp_external (
      2      empno       number(4) ,
      3      ename       varchar2(10),
      4      job         varchar2(9),
      5      mgr         number(4),
      6      hiredate    date,
      7      sal         number(7, 2),
      8      comm        number(7, 2),
      9      deptno      number(2)
     10      )
     11      organization external
     12      (
     13      type oracle_loader
     14      default directory data_dir
     15      access parameters (fields terminated by '\t')
     16      location ('emp.dat')
     17      )
     18      reject limit unlimited;
    
    Table created.
    Now you can select from it, and import it into the database.
    SQL> select * from emp_external;
    
     EMPNO ENAME    JOB          MGR HIREDATE     SAL   COMM  DEPTNO
    ------ -------- --------- ------ --------- ------ ------ -------
      7369 SMITH    CLERK       7902 17-DEC-80    800             20
      7499 ALLEN    SALESMAN    7698 20-FEB-81   1600    300      30
      7521 WARD     SALESMAN    7698 22-FEB-81   1250    500      30
      7566 JONES    MANAGER     7839 02-APR-81   2975             20
      7654 MARTIN   SALESMAN    7698 28-SEP-81   1250   1400      30
      7698 BLAKE    MANAGER     7839 01-MAY-81   2850             30
      7782 CLARK    MANAGER     7839 09-JUN-81   2450             10
      7788 SCOTT    ANALYST     7566 09-DEC-82   3000             20
      7839 KING     PRESIDENT        17-NOV-81   5000             10
      7844 TURNER   SALESMAN    7698 08-SEP-81   1500      0      30
      7876 ADAMS    CLERK       7788 12-JAN-83   1100             20
      7900 JAMES    CLERK       7698 03-DEC-81    950             30
      7902 FORD     ANALYST     7566 03-DEC-81   3000             20
      7934 MILLER   CLERK       7782 23-JAN-82   1300             40
      
    14 rows selected.
    
    SQL> create table emp2 as select * from emp_external;
    
    Table created.
    
    SQL> select * from emp2;
    
     EMPNO ENAME    JOB          MGR HIREDATE     SAL   COMM  DEPTNO
    ------ -------- --------- ------ --------- ------ ------ -------
      7369 SMITH    CLERK       7902 17-DEC-80    800             20
      7499 ALLEN    SALESMAN    7698 20-FEB-81   1600    300      30
      7521 WARD     SALESMAN    7698 22-FEB-81   1250    500      30
      7566 JONES    MANAGER     7839 02-APR-81   2975             20
      7654 MARTIN   SALESMAN    7698 28-SEP-81   1250   1400      30
      7698 BLAKE    MANAGER     7839 01-MAY-81   2850             30
      7782 CLARK    MANAGER     7839 09-JUN-81   2450             10
      7788 SCOTT    ANALYST     7566 09-DEC-82   3000             20
      7839 KING     PRESIDENT        17-NOV-81   5000             10
      7844 TURNER   SALESMAN    7698 08-SEP-81   1500      0      30
      7876 ADAMS    CLERK       7788 12-JAN-83   1100             20
      7900 JAMES    CLERK       7698 03-DEC-81    950             30
      7902 FORD     ANALYST     7566 03-DEC-81   3000             20
      7934 MILLER   CLERK       7782 23-JAN-82   1300             40
      
    14 rows selected.
    
    SQL>
    Edited by: 3360 on Mar 16, 2011 9:40 PM

    I just noticed this post was linked in the FAQ so I updated the format tags that were no longer supported due to forum upgrades for readability.
  • 3. Re: Using DML order to import a .xls file
    kolipaka Newbie
    Currently Being Moderated
    Thanks for your help...:)

    Can you please suggest me if there are more than one sheet in excel file, then how can we use/create an external table. Any idea please let me know.

    Thanks in advance.

    Regards,
    KLR
  • 4. Re: Using DML order to import a .xls file
    Marwim Expert
    Currently Being Moderated
    Hello KLR,

    if you want to read a Excel file without converting it to CSV you can use Heterogeneous Services
    See {message:id=9360007}

    Regards
    Marcus
  • 5. Re: Using DML order to import a .xls file
    995210 Newbie
    Currently Being Moderated
    SQL> host D:\task2\sample.csv ***************** im using this in plsql developer tool first

    SQL>
    SQL>/


    ORA-00911: invalid character


    this type of error is show so pls help me
  • 6. Re: Using DML order to import a .xls file
    995210 Newbie
    Currently Being Moderated
    SQL> host D:\task2\sample.csv ***************** im using this in plsql developer tool first

    SQL>
    SQL>/


    ORA-00911: invalid character


    this type of error is show so pls help me
  • 7. Re: Using DML order to import a .xls file
    995210 Newbie
    Currently Being Moderated
    SQL> host D:\task2\sample.csv ***************** im using this in plsql developer tool first

    SQL>
    SQL>/


    ORA-00911: invalid character


    this type of error is show so pls help me