Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar

Search for    in all of
view all search results right arrow
Support > Technical Documents > Document Types > Technote > Connecting to Excel Workbook from PowerBuilder usi...

Connecting to Excel Workbook from PowerBuilder using the Intersolv/Merant or PB DATADIRECT ExcelWorkbook ODBC drivers

This document describes how to prepare and define an EXCEL data source to connect to it using the Intersolv/Merant ExcelWorkbook ODBC driver.
RSS Feed

     To prepare an Excel Data source for use within PowerBuilder
     In order for PowerBuilder or InfoMaker to access an Excel data source, the Excel file must be a database. An Excel database is an
     Excel XLS workbook file that contains one or more named lists. An Excel list is a labeled series of worksheet rows that contain similar
     information (table).

     When you use an Excel workbook as a database, each list is analogous to a database table, the list rows correspond to database
     records, and the list columns correspond to database fields. When you connect to an Excel database in PowerBuilder or InfoMaker, the
     list names display in the Select Tables list in the Database painter.

     The first 'row' should be your 'column' names. Carefully look at all of the column headings that you intend to import into the database. SQL
     rules of column names are more strict than Excel. Therefore you need to modify all column headings to comply with the SQL standards. In
     most cases this involves removing any embedded blanks and non alphanumeric characters. For example the column name First...Name
     or First Name will cause errors. The column names should be changed to firstname or first_name.
     If you can not change the column or table names as suggested above, you can put DelimitIdentifier = 'YES' in the DBParm when you
     create a profile in PowerBuilder.

     Make sure your Excel workbook file contains one or more named lists. Your list name should follow standard SQL naming conventions.

     In order to define your list:
     In order to define your list, select (highlight) the rows and columns that are to be imported. Select Insert->name->define from the Excel
     menu bar to define the current workbook selection as a database table or type a name (uppercase) in the Name Field and hit < enter >. It
     is advised to always use all uppercase characters when naming tables and columns that you want to access in PowerBuilder and
     InfoMaker and avoid using database-specific reserved words.

     To define an Excel data source for the Intersolv/Merant or PB DATADIRECT ExcelWorkbook
     In PowerBuilder select the Configure ODBC toolbar item. If this is not on your Toolbar, press on the right mouse button on the powerbar,
     click on customize and drag the ODBC toolbar item down to the current toolbar.

     Once you click on the configure ODBC toolbar you will get a dialog box containing the installed ODBC drivers. Scroll down the list and
     select the Intersolv, Merant or PB DATADIRECT ExcelWorkbook and click the Create command button
     You will be presented with another dialog box.
     Give your data source a name and enter the full path for the excel database file in the Database workbook including the excel database
     Then click on the DB Profile toolbar. Select the Excel profile. It will display a window with title 'Choose an Excel Workbook as database'.
     Select the Excel workbook where you have saved your table, click "Open" and connect to the newly created excel data source.
     Click on the database icon and you should see your saved excel spreadsheets as tables.

     If there are no tables in the list:
     Make sure the directory where the spreadsheets are located is set correctly in the ODBC datasource.
     Makes sure you highlighted the rows and columns in the spreadsheet.
     Makes sure you followed the above described instructions on how to create one or more lists in your workbook.

     Common Error messages
     Cannot open workbook nnnn.xls , OLE error code (32)
     Excel is still open, close Excel and you should be able to connect.

     Select error : SQLSTATE = S1000 [INTERSOLV] [ODBC EXCEL DRIVER]
     Cannot open workbook nnnn.xls , OLE error code (80)
     The Excel file was created with a previous version of Microsoft excel. You should update it to
     current MS Excel format.

     Select error : SQLSTATE = S0002 [INTERSOLV] [ODBC EXCEL DRIVER]
     the table nnnn was not found in the workbook nnnn
     Table names do not comply with the SQL standards. Use uppercase characters to name your table OR set DelimitIdentifier = 'YES' in the
     DBParm, in the database Profile for your Excel connection. Do not use database-specific reserved words (eg: Naming an excel table

     Field not found: nnnn
     Column names do not comply with the SQL standards. Change them to follow the SQL standards OR set DelimitIdentifier = 'YES' in the
     DBParm, in the DataBase Profile for your Excel connection.

     table nnnn has no columns, possible invalid table format
     Intersolv driver does not recognize input as a valid Excel file.


Last Revised: Mar 28, 2003
Product: ODBC, PowerBuilder
Hardware Platform: Windows x86
Technical Topics: Connectivity
Business or Technical: Technical
Content Id: 47700
Infotype: Technote

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics