Connecting to Excel - SuperCHANNEL

SuperCHANNEL can read data from Microsoft Excel through the jdbc:odbc bridge.

Limitations of the jdbc:odbc Bridge

There are a number of limitations when using the jdbc:odbc bridge:

• It cannot recognize table joins. You will have to construct table references in the SuperCHANNEL GUI.
• It supports a maximum of 64 characters for column names. Any text beyond this limit will be truncated.
• It does not support Unicode characters.

The jdbc:odbc bridge has been removed from Java 8 onwards, and is no longer available with SuperSTAR from version 9.3 onwards.

The jdbc:odbc bridge is not recommended for large data sets. You are strongly recommended to use a relational database instead, connecting using a JDBC driver.

If using a relational database is not an option for you, then you may be able to channel your data using Space-Time Research's Textual Database Definition format.

Use Named Ranges to Define Tables in your Excel Workbook

SuperCHANNEL interprets named ranges in Excel workbooks as tables, so you need to create some ranges to define the fact tables and classification tables to be used.

To create a named range in Excel:

1. Select the range of cells that includes the data for a specific table. Make sure you include the column headings as SuperCHANNEL will use the values in the first row as the column headings.

2. In the top left of the formula bar, enter a name for the set. This name will appear as the table name in SuperCHANNEL.

   

Ensure that the tables you define conform to the star-schema concepts of fact tables and classification tables.

It is also important to avoid including null rows or columns. In particular, do not select entire rows or columns when you create the named ranges, as SuperCHANNEL will then attempt to insert all the cells in the range, including those with null values.

Create an ODBC Data Connection

In order to connect to Excel you need to create an ODBC data connection.

1. If you have not already installed the Microsoft data access drivers, download and install the drivers from: http://www.microsoft.com/en-us/download/details.aspx?id=13255

2. Once you have installed the drivers, go to the Windows Control Panel and select System and Security > Administrative Tools > Data Sources (ODBC).

   The ODBC Data Source Administrator window displays.

3. Click Add.

   

4. Select Microsoft Excel Driver and click Finish.

   

5. Give your new data source a Name and a Description. You will use the Data Source Name to connect to this data source in SuperCHANNEL.
6. In the Database section, select the Excel Version from the drop-down list.

7. Click Select Workbook and select the workbook that contains your data.

8. Click OK to create the data source.

   

Connect to the Data Source

1. In SuperCHANNEL, select File > Connect to Source.

2. In the Driver field, select the odbc driver.

   When you select the driver, SuperCHANNEL automatically populates the Location field with the first part of the connection string: jdbc:odbc

3. Add the ODBC Data Source Name that you set in the previous step to complete the location string. For example:
   

   

4. Click OK (this data source does not require a username, password, or schema).

   SuperCHANNEL populates the Source View with the tables (named ranges) from the Excel workbook. It interprets the first row in each named range as the column names:

   

You can now connect to a target SXV4 database and design and build your database. For example:

When you are ready to build the target database, make sure you close the Excel worksheet. SuperCHANNEL will not be able to channel the data if the worksheet is currently open.