To prepare an Excel Data source for use
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
When you use an Excel workbook as a database,
each list is analogous to a database table, the list rows correspond to
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
Select error : SQLSTATE = S0022 INTERSOLV]
[ODBC EXCEL DRIVER]
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
Intersolv driver does not recognize input as a valid Excel file.