1 2 Previous Next 19 Replies Latest reply on Jan 19, 2010 3:13 PM by 744442

    excel file to import database

    744442
      What do I need to import excel file database?

      package?
        • 1. Re: excel file to import database
          Solomon Yakobson
          All you need is to setup Oracle heterogenious connectivity - HS including database link. Then create ODBC datasource to excel file and you are all set - you can

          INSERT INTO oracle_table select * from tbl@dblink_to_excel
          There are plenty example on this forum and MetaLink.

          SY.
          • 2. Re: excel file to import database
            BluShadow
            Copy/Paste of my standard answer...

            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
            1 person found this helpful
            • 3. Re: excel file to import database
              riedelme
              Solomon's idea to use an ODBC connection should work.

              Other options include SQL*LOADER, external tables (file must be on server), Apex (which can load CSV files from remote computers), and if you really want to use PL/SQL UTL_FILE (source file must be on server).
              • 4. Re: excel file to import database
                BluShadow
                riedelme wrote:
                Solomon's idea to use an ODBC connection should work.

                Other options include SQL*LOADER, external tables (file must be on server), Apex (which can load CSV files from remote computers), and if you really want to use PL/SQL UTL_FILE (source file must be on server).
                Those options only work if individual Excel sheets are exported as CSV files. They can't read Excel files which are a MS proprietary binary format.
                • 5. Re: excel file to import database
                  744442
                  SQL> SELECT employee FROM all_tables@excl;

                  ORA-02019 : connection description for remote database not found

                  Where I might have done wrong :S
                  • 6. Re: excel file to import database
                    Solomon Yakobson
                    user8950972 wrote:

                    Where I might have done wrong :S
                    And how do we know what did you do?

                    SY.
                    • 7. Re: excel file to import database
                      744442
                      I did that by following the above procedure..

                      I am not sure yet so new.. :(
                      • 8. Re: excel file to import database
                        BluShadow
                        user8950972 wrote:
                        SQL> SELECT employee FROM all_tables@excl;

                        ORA-02019 : connection description for remote database not found

                        Where I might have done wrong :S
                        all_tables will tell you what "tables" you have in the workbook. Each worksheet in the Excel workbook is considered a table and the table name is obtained from the tab name shown in Excel.

                        You can't select "employee" from all_tables as that is not a valid column name for that view.

                        If your workbook contains a sheet called "employee" then you would use:
                        select * from employee@excl
                        However, your error would indicate that you've not set something up correctly. However, without knowing what you've done exactly that's going to make it a little bit hard for us to know where you've gone wrong.
                        • 9. Re: excel file to import database
                          746381
                          Why not just use import in SQLDeveloper?

                          Right click on your table, click import data, click your xls file. Fill out everything such as date format.
                          It will generate the sql. run the script. done.
                          • 10. Re: excel file to import database
                            BluShadow
                            Decklyn Dubs wrote:
                            Why not just use import in SQLDeveloper?

                            Right click on your table, click import data, click your xls file. Fill out everything such as date format.
                            It will generate the sql. run the script. done.
                            Not very "automatic" if you need to do this on a regular basis, especially if it's a job that needs to run overnight. ?:|
                            • 11. Re: excel file to import database
                              744442
                              I'm sorry I sent the missing


                              1- http://img40.imageshack.us/img40/427/excelj.jpg

                              2- http://img29.imageshack.us/img29/587/plist.jpg


                              3-

                              # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
                              # Generated by Oracle configuration tools.

                              STAWIZ =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = Comp)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = stawiz)
                              )
                              )

                              EXTPROC_CONNECTION_DATA =
                              (DESCRIPTION =
                              (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                              )
                              (CONNECT_DATA =
                              (SID = PLSExtProc)
                              (PRESENTATION = RO)
                              )
                              )

                              EXCL =
                              (DESCRIPTION =
                              (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = Comp )(PORT = 1521))
                              )
                              (CONNECT_DATA =
                              (SID = EXCL)
                              )
                              (HS = OK)
                              )


                              4-

                              # listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
                              # Generated by Oracle configuration tools.

                              SID_LIST_LISTENER =
                              (SID_LIST =
                              (SID_DESC =
                              (SID_NAME = PLSExtProc)
                              (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                              (PROGRAM = extproc)
                              )
                              )

                              LISTENER =
                              (DESCRIPTION_LIST =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                              (ADDRESS = (PROTOCOL = TCP)(HOST = Comp)(PORT = 1521))
                              )
                              )

                              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 = C:\oracle\product\10.2.0\)
                              (PROGRAM = extproc)
                              )
                              (SID_DESC =
                              (GLOBAL_DBNAME = ORA9DB)
                              (ORACLE_HOME = C:\oracle\product\10.2.0)
                              (SID_NAME = ORA9DB)
                              )
                              (SID_DESC =
                              (PROGRAM = hsodbc)
                              (SID_NAME = EXCL)
                              (ORACLE_HOME = C:\oracle\product\10.2.0)
                              ) )



                              5-

                              DB LINK

                              CREATE DATABASE LINK "EXCL" USING 'EXCL'


                              6-

                              select * from EXCL@EXCL

                              this way did :( :(

                              ORA-02019 : connection description for remote database not found -- same :( unchanged :(
                              • 12. Re: excel file to import database
                                BluShadow
                                user8950972 wrote:
                                http://img40.imageshack.us/img40/427/excelj.jpg
                                Very nice, but my work blocks images from places like imageshack, so I can't see anything.
                                • 13. Re: excel file to import database
                                  BluShadow
                                  Did you restart your listener?
                                  1 person found this helpful
                                  • 14. Re: excel file to import database
                                    744442
                                    could not do this :(( Do not have other way?
                                    1 2 Previous Next