You can connect to a Microsoft Excel spreadsheet with DataPipe and TextPipe Pro. Please also read our notes on creating an OLE DB connection to an ODBC data source.

Step 1. Setting up the Excel spreadsheet

First we will need to create an excel spreadsheet. For our example we will create a spreadsheet called "contacts" containing the following data:

Step 2. Setting up the spreadsheet as an ODBC Data Source

Next we need to make this spreadsheet accessible to DataPipe or TextPipe by making it an ODBC data object. Under Windows 2000, go to the Control Panel, select Administrative Tools, and click on Data Sources (ODBC). If you are using other Windows platforms, you can usually find this item in the Control Panel. Now select the System DSN tag in the ODBC Data Source Administrator window.

Click the Add button here to add the Microsoft Excel spreadsheet we created as an ODBC Data Source. You will then be asked to select a driver. Select the "Microsoft Excel Driver (*.xls)" driver.  Next select the "Select Workbook" button, and select the Excel spreadsheet that we created earlier.  Also, make sure that the checkbox for "Read Only" is NOT checked in the Select Workbook menu.

Step 3. The Database Query

To insert data into the table with TextPipe, you need to generate a string of the following form:

INSERT INTO [Sheet1$] (Name, Phone, Job) VALUES (?, ?, ?)";

To select and modify data with DataPipe, you need to generate a string of the following form:

SELECT * FROM [Sheet1$];