Forum Stats

  • 3,874,142 Users
  • 2,266,673 Discussions
  • 7,911,738 Comments

Discussions

How to import excel file having multiple sheets and create one table per sheet

User_HBVVY
User_HBVVY Member Posts: 1 Green Ribbon

I have an excel file with 500+ worksheets and each sheet has a table. I want to import the excel file so that a table is created for each sheet resulting in 500+ tables in the database filled with 500+ sheets' data. I want to know if there is a way to do this all at once as I don't want to manually import 500+ sheets and create their tables one by one.

Answers

  • GregV
    GregV Member Posts: 3,106 Gold Crown

    Hi,

    I would create a VBA macro for that, using ADO components to connect to the DB.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,555 Red Diamond

    You could use Heterogeneous Services to treat the Excel sheet as a database...

    Prerequisites:

    a) Oracle is running on Windows Server (or you set up an oracle instance on a windows client and then move your data later - if you must)

    b) Each worksheet in the Excel workbook is taken to be a "table" with the worksheet name being the table name (must not exceed the 30 character limit of Oracle, as Excel allows 32 chrs).

    c) The first row on the worksheet provides the column names for the table (again, limited to 30 characters)

    d) The second and subsequent rows provide the actual rows of data on the table.

    (not sure if the 30 chr limit on names is still an issue on recent Oracle versions - you'd have to check - I haven't done this for a while)

    Steps:

    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 e.g. (give it appropriate Oracle Home - this example is old):

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

    Don't forget to reload the listener (may require actual stop and start of 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 [email protected];
    
    TABLE_NAME
    ------------------------------
    DEPT
    EMP
    

    In this example the workbook contains two spreadsheets, one called DEPT and the other called EMP, which you can then query with select * from [email protected] etc.

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond

    An alternative to those solutions offered is @odie_63's ExcelTable, which can do what you want.