XLS/Loader

(C) G. Hollfelder 2008, 2009 gho(at)gmx.net

What it does for you

Brings your Excel worksheets, when structured as simple table, fast and easily in your RDBMS. Supports Oracle(TM), MySQL(TM), PostgreSQL.

Why it may be useful

You get data as file that...

  • must be joined with data in a database for transformation or reporting tasks
  • can be easier analyzed with SQL than Excel

Or use Excel worksheets to design your database w/o SQL but real data.

What it needs

Written in Java - needs at least JRE5.

How it works

Steps to do...

  1. Workbook: Select your workbook file (.xls) in your file system (initially done when application is started).
  2. Worksheet: Select a worksheet contained in the workbook.
  3. System: Select your RDBMS.
  4. Driver: Optional. Change JDBC driver (select different driver file and/or specify different class name).
  5. URL: Enter JDBC URL for your RDBMS or select one you have already used before.
  6. Remove: Optional. Remove currently selected URL if obsolete.
  7. Test: Test selected JDBC URL.
  8. Table: Optional. Change table name proposal (derived from worksheet name).
  9. Drop Existing: If selected, an already equally named table in your RDBMS will be dropped on load.
  10. Default Schema: Optional. Select a schema of your RDBMS where the table should be created.
  11. Default Tablespace (only when RDBMS is Oracle): Optional. Select a tablespace of your RDBMS in which the table should be created.
  12. Analyze Worksheet: The selected worksheet gets analyzed and the table below is filled with the proposed table structure. The structure can be edited (e.g. to modify column name, type, length or select the primary key columns).
  13. 1st Row Has Names: If selected (default), it is assumed that contents of the first row in the worksheet contains column names. Otherwise, if first row of your worksheet already contains data, analyze process will result in column names following Excels native naming pattern ("A", "B", "C", ...).
  14. Table DDL: Shows DDL command used to create the table on load.
  15. Include Row Number: If selected (default), an extra column ("rownum_") is inserted as tables first column which is filled with the row number of complementary row in the source worksheet on load. If no primary key columns are selected explicitly in the above table structure this column also becomes the primary key column.
  16. Exclude Empty Columns: If checked, columns in the source worksheet which contains no data are excluded from table creation.
  17. Load: Start the process of taking over the worksheet into the RDBMS.
  

Watch the steps here as video.

Read application notes.

Go to project page on sourceforge.net to download.

Trademark Notice
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Oracle(TM) is a trademark of Oracle.
MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.