[an error occurred while processing this directive]
> developer > web app development
Accessing Microsoft Excel spreadsheet data using xCommerce on Microsoft NT
by exteNd Composer Product Team, eBusiness Integration Products, Novell
Date Created: 2001-07-30 15:19:00.000
  Introduction
  ODBC Configuration
  Create the xCommerce connection
  Excel spreadsheet requirements
  Create the xCommerce JDBC component
  conclusion
Introduction
Microsoft has made Excel spreadsheet data available as an ODBC data source.

This article will show how to access spreadsheets using the xCommerce JDBC enterprise enabler with an ODBC data source. The article will explain how to set up and configure your ODBC data source, create an xCommerce JDBC connection, as well as the requirements for the spreadsheet, and then finally how to access the data in the spreadsheet.

ODBC configuration

The first step is to set up an ODBC connection to your data source: the Excel .xls spreadsheet file.  

  1. Open the Control Panel and select ODBC Data Sources. 
  2. Select System DSN, then the Add _ button. 
  3. Select the Microsoft Excel Driver (*.xls).  
  4. Press the Finish button.

 

Give your Data Source a name and press the Select Workbook_ button (see below).  Select the name of the Excel spreadsheet that you want to use, and then press OK.  (In this example, the spreadsheet is ISPTelephone.)Your ODBC Excel Data Source is now complete.

 

 

create the xCommerce connection

Since our Excel spreadsheet is now an ODBC data source, we can "connect" to it from xCommerce. To do this, we create a JDBC Connection Resource that associates our JDBC driver with the appropriate JDBC URL.

The setup procedure is very simple. Open xCommerce and choose File | New xObject | Resource | Connection from the menu.  Give the connection a name on the Header Info panel.  Fill in the Connection Info panel as show below: 

 

Excel spreadsheet requirements

For practical purposes the Excel spreadsheet can be thought of as the database.  Tables are defined for an Excel spreadsheet by naming a range of cells.  So before you can use an Excel spreadsheet as an ODBC data source you need to ensure that your spreadsheet has named ranges defined.  You accomplish this by selecting a range of cells then select Insert | Name | Define and enter the name you want to use.  You can name more than one range. 

The screen shot below shows an Excel spreadsheet with a selected group of cells named AllNumbers.  Notice the values in row 1 (City, State, Telephone, etc).  Since they are the first row in the range these values will be the names of the columns.

 

 

create the xCommerce JDBC component
conclusion

Valuable corporate data are often stored in Excel spreadsheets.  We have shown one way you can configure and retrieve Excel spreadsheet data using the xCommerce Enterprise Enabler for JDBC (which is bundled with every copy of xCommerce). Please note that there are other third-party tools that may be of assistance if this method is not adequate.