Using DML order to import a .xls file

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

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.

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

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

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

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

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