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.
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:
- 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.
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.
Create an ODBC Data Connection
In order to connect to Excel you need to create an ODBC data connection.
- 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
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.
Select Microsoft Excel Driver and click Finish.
- 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.
- In the Database section, select the Excel Version from the drop-down list.
Click Select Workbook and select the workbook that contains your data.
Click OK to create the data source.
Connect to the Data Source
- In SuperCHANNEL, select File > Connect to Source.
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
Add the ODBC Data Source Name that you set in the previous step to complete the location string. For example:
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: