Query an Excel Worksheet using Showcase Query

 

Problem Statement:

How do I query an Excel worksheet as a PC file using Showcase Query?

Solution:

In EXCEL        

In Excel97 or later, save the file as usual. In pre-Excel97, files must be saved as Microsoft Excel 4.0 worksheets.  (Either through Excel or as a target file type in Query)

1. Highlight the data in Excel that you want to query. This "named range" becomes the database.
2. In M/S Excel click Insert->Name->Define; type in a name for the highlighted data then click Add.
3. Repeat steps 1) and 2) as needed, each new name defines a "table" as seen in Query.
4. Save Excel sheet. (Remember to save as an Excel 4.0 worksheet if the PC is running a version of Excel earlier than Excel97)

Open Microsoft ODBC Administrator: Use either 16-bit or 32-bit, depending on which version of Query and Report Writer was installed.

1. Click ADD; choose Microsoft Excel Driver (*.xls)
2. Type in the data source name and description
3. Set database version to Excel97 (if using Excel97) or Excel 4.0 (if using a version earlier than Excel97)
4. Click OK and CLOSE.

To run a Query:

1. Within Query, click File\New. Choose the Excel Driver as the data source.
2. Browse for Excel file you want to query.  
3. After selecting, note that the file name is in the first window and the workbookname in the second window.  Add the TABLE/workbook name to the first window.  This can be done by adding '\' to the path in the top window and either PASTE or TYPE the entry from the Table window.

 Example:

    AFTER YOU BROWSE:    C:\Excel                     AFTER YOU COPY:   C:\Excel\workbookname
                          workbookname                                              workbookname

4. If the table window is blank, click the drop-down menu to see the tables that were defined in the first part of this resolution.
5. Continue to set up the query as desired.

The copying of the workbook name to the path is very important. Browsing will not automatically pull it in. In pre-Excel97, you will find that when browsing to select a workbook, Query will enter the path in the Workbook window and add the workbook name to the Table window.  This is not correct. Be sure that the workbook name is included to the path in the Workbook window, and then click on the table drop down to see tables for that workbook. These tables will be the names that were created in the first part of resolution.

Using this method multiple tables from one worksheet or even multiple tables from multiple workbooks can be chosen.

Remember: If another file is used, that workbook name must be added to the path.

Ref#: 1475533