7 Replies Latest reply: Mar 7, 2013 12:52 AM by 995210 RSS

    Using DML order to import a .xls file

    477409
      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
          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
            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
              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
                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
                  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
                    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
                      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