This discussion is archived
8 Replies Latest reply: Mar 18, 2010 7:21 AM by TheOtherGuy RSS

Read CSV/XLS file to insert into Oracle database.

pm Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.
    626204 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    very good point, thank you


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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points