Switch to standard view 
  Sybase logo
 
 
 



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

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,
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 tablesAt 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;

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.


Back to Top
© Copyright 2010, Sybase Inc.