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 位用户发现它有用
          • 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 位用户发现它有用
                                • 14. Re: excel file to import database
                                  744442
                                  could not do this :(( Do not have other way?
                                  1 2 上一个 下一个