How to Create Database Tables from Excel Spreadsheets Introduction
There is a frequent need to combine information from different databases into a single data repository or warehouse for Inquiry, Analysis and Reporting.
Fortunately most systems have exporting functions that can export relevant data into a format that can be read by Microsoft Excel. Once in Excel format Powerbuilder's Data Pipeline can be used to build database tables from the Excel spreadsheets. Once the tables are in the database, Datawindows can be easily built to combine the Excel information as required.
For instructions on exporting data from your system to Excel see your system's Documentation and possibly the Excel documentation.
Once you have your data in Excel Columns with no quotes or delimiters and in appropriate columns, you are ready for the next step.
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.
To define an Excel data source for the DataDirect and Microsoft Excel Driver
- 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 such as:
SQLSTATE 37000 [....] [ODBC EXCEL DRIVER] Syntax Error in query expression 'First Name'.
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. To define your list in Excel, 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 in the Name Field and hit < enter >.
If you are using the DataDirect driver, it is advised to always use all uppercase characters when naming tables and columns that you want to use in PowerBuilder and InfoMaker and avoid using database-specific reserved words. Close Excel before you try to connect to the Excel data source from within PowerBuilder.
- If the following error occurs, while retrieving from the Excel table:
SQLSTATE 37000 [....] [ODBC EXCEL DRIVER] Syntax error in FROM clause
Use DelimiterIdentifier='Yes' in the DBParm when you create your database profile in Powerbuilder.
If you are using Excel (other than versions 5.0/95 or 97 & 5.0/95), use the Microsoft Excel driver.
In Powerbuilder select the Configure ODBC toolbar item or use the Control Panel ODBC applet. Scroll down the list and select either the "Microsoft Excel driver" or a "DataDirect" driver and click the Create command button.
You will be presented with another dialog box,
- click on the select directory command button if you have selected the MS driver
- or if you have selected the DataDirect driver enter the full path for the excel database file in the Database workbook and select the directory where you saved your excel workbook.
- When creating the DataDirect DataSource and
if the following error occurs:
[DataDirect][ODBC Excel Driver]Cannot open stream Book. Ole error (2).
Make sure the excel file is saved to the format of 5.0/95 or 97 & 5.0/95. For more information, see DataDirect KnowledgeBase Article 19823.
Next give your data source a name like excel spreadsheets.
Then click on the DB Profile toolbar 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:
Create Database tables from the Excel tables
- Make sure the directory where the spread sheets are located is set correctly in the odbc datasource
- Make sure you highlighted the rows and columns in the spreadsheet.
- Make sure you followed the above described instructions on how to create one or more lists in your workbook.
- Make sure the "Show System Tables" property is checked.
At this point you can create a datawindow join of two or more spreadsheets and report on the data but you will not be able to update anything. In order to any data updates the Excel data will have to be moved into a real SQL table (such as a SQL Anywhere table).
Usually you want to move this data into a relational database that contains other tables you want to join to. This is where the database pipeline comes in. To move the data from the Excel spreadsheet to a SQL table;
- Click on the pipeline icon on the powerbar.
- Click on new
- As the source connection choose the Excel spreadsheet datasource. As the Destination connection choose the database you want to move the data into.
- Select the table containing your spreadsheet
- Select the columns you want to create in the new table. You can put where criteria in to limit the rows to be selected if required. You can create computed columns here if you know the syntax of the receiving database. For example in SQLAnywhere, string(last_name,', ', first_name) would concatenate the first and last name columns in to a third column, separated by a comma and a space.
- Select the Design->Datasource or click on the SQL button.
- You can change the length or column names of the datacolumns to be created or column names if required. You can also create an index on the table if desired.
- Click on the execute toolbar item to create the table and copy the rows into the destination database.
Once in the new database, you can read, update, join this new table to any others in the database.
You can change the pipeline options from 'create' to 'refresh' if desired and save the pipeline object in your pbl. Then you can create a PowerBuilder program to access an updated spreadsheet and update the database table on a regular basis. See Powerbuilder on-line help for pipeline and pipelineobject.