Processing data from ODBC sources

You can process data from any ODBC source over the first row of a worksheet. You do not need to create a database alias for the source. The Process Data dialog includes a third type of source, ODBC, from which you can process data. You can also save previously defined ODBC and MS Query queries in a special format so they can be re-used as data sources. The ODBC processing function uses MS Query to define queries on relational sources. To be able to define queries for processing, you must have MS Query installed. This is not usually part of the standard Microsoft Excel installation.

You can use previously defined MS Query (*.DQY) or OLAP Server (*.ALQ) queries. Or you can define a new query.

To define a relational database query to process:

  1. Select More > Process Data.
  2. Select ODBC from the Process from list.
  3. Click Define Query.
    MS Query opens and, within it, the Choose Data Source dialog.
  4. Select a data source and click OK. The Query from [data source name] dialog is displayed.
    Alternatively, you can use the MS Query Wizard. To do this, select Use the Query Wizard to create/edit queries before you select the data source.

    If your data source is not available, click Data Source to create one.

  5. Define the query.
  6. On the MS Query menu, select File > Return to OLAP.
    If you used the MS Query Wizard, returning to OLAP Server is the final step. When you return to OLAP Server the Process Data dialog is displayed and shows the first lines of the data query. If you want to return the field names from the query to the Excel sheet, select Copy field names to Excel sheet. The field names are then copied to the second row of the Excel sheet.
  7. To create a log file to track records that were not imported correctly, select Log file and specify a file name and location.
  8. Click OK.
    The data is processed over the first line of the Excel worksheet.