8 Replies Latest reply: Mar 18, 2010 9:21 AM by TheOtherGuy RSS

    Read CSV/XLS file to insert into Oracle database.

    pm
      hi,

      How to read csv/xls file to insert into Oracle database tables?

      I have xls file. i wanted to insert xls records into tables thr' job.


      Thanks
      PM
        • 1. Re: Read CSV/XLS file to insert into Oracle database.
          492296
          Hi,

          If your database version=11gR2, use external table in accessing and manipulating the CSV/xls file.

          BTF
          • 3. Re: Read CSV/XLS file to insert into Oracle database.
            pm
            I am using 10g database.

            select * from v$version;

            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
            PL/SQL Release 10.2.0.4.0 - Production
            CORE     10.2.0.4.0     Production
            TNS for HPUX: Version 10.2.0.4.0 - Production
            NLSRTL Version 10.2.0.2.0 - Production.


            Can i use external table here?
            • 4. Re: Read CSV/XLS file to insert into Oracle database.
              Prazy
              yes you can, AFAIK external table got introduced in Oracle 9i. But I doubt external tables support XLS files, you can pretty much convert it into .CSV file and load it using external table.

              Follow this link.

              http://www.oracle-base.com/articles/9i/ExternalTables9i.php

              If you got struck-up at any point, feel free to post the error.

              Regards,
              Prazy
              • 5. Re: Read CSV/XLS file to insert into Oracle database.
                TheOtherGuy
                I posted article about this on my blog few weeks ago, there are some easy ways to load excel to oracle

                http://jiri.wordpress.com/2010/01/21/load-ms-excel-file-to-oracle-database/
                • 6. Re: Read CSV/XLS file to insert into Oracle database.
                  Marwim
                  Have you tried to search this forum for "Excel import"?

                  http://forums.oracle.com/forums/search.jspa?threadID=&q=excel+import&objID=f75&dateRange=last90days&userID=&numResults=15&rankBy=10001

                  You might be surprised but you get a lot of useful postings ;-)

                  Regards
                  Marcus
                  • 7. Re: Read CSV/XLS file to insert into Oracle database.
                    BluShadow
                    Jiri in SF wrote:
                    I posted article about this on my blog few weeks ago, there are some easy ways to load excel to oracle

                    http://jiri.wordpress.com/2010/01/21/load-ms-excel-file-to-oracle-database/
                    Your blog completely forgets the most generic option of Heterogeneous Services for connecting to the Excel workbook as if it's a database (e.g. database link) using an Excel ODBC driver.

                    1- Go to Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN and create a data source with appropriate driver. Name it EXCL.

                    2- In %ORACLE_HOME%\Network\Admin\Tnsnames.ora fie add entry:
                    EXCL =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.0.24)(PORT = 1521))
                    )
                    (CONNECT_DATA =
                    (SID = EXCL)
                    )
                    (HS = OK)
                    )
                    Here SID is the name of data source that you have just created.

                    3- In %ORACLE_HOME%\Network\Admin\Listener.ora file add:
                    (SID_DESC = 
                    (PROGRAM = hsodbc) 
                    (SID_NAME = <hs_sid>) 
                    (ORACLE_HOME = <oracle home>) 
                    )
                    under SID_LIST_LISTENER like:
                    SID_LIST_LISTENER =
                    (SID_LIST =
                    (SID_DESC =
                    (SID_NAME = PLSExtProc)
                    (ORACLE_HOME = d:\ORA9DB)
                    (PROGRAM = extproc)
                    )
                    (SID_DESC =
                    (GLOBAL_DBNAME = ORA9DB)
                    (ORACLE_HOME = d:\ORA9DB)
                    (SID_NAME = ORA9DB)
                    )
                    (SID_DESC = 
                    (PROGRAM = hsodbc) 
                    (SID_NAME = EXCL) 
                    (ORACLE_HOME = D:\ora9db) 
                    ) ) 
                    Dont forget to reload the listener
                    C:\> lsnrctl reload
                    4- In %ORACLE_HOME%\hs\admin create init<HS_SID>.ora. For our sid EXCL we create file initexcl.ora.

                    In this file set following two parameters:
                    HS_FDS_CONNECT_INFO = excl
                    HS_FDS_TRACE_LEVEL = 0
                    5- Now connect to Oracle database and create database link with following command:
                    SQL> CREATE DATABASE LINK excl
                    2 USING 'excl'
                    3 /
                    
                    Database link created.
                    Now you can perform query against this database like you would for any remote database.
                    SQL> SELECT table_name FROM all_tables@excl;
                    
                    TABLE_NAME
                    ------------------------------
                    DEPT
                    EMP
                    Or refer to this Article...
                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206
                    • 8. Re: Read CSV/XLS file to insert into Oracle database.
                      TheOtherGuy
                      very good point, thank you


                      I never used oracle on windows system, odbc drivers on unix are usually not free