Menu
Simba Technologies
Simba Technologies

Simba ODBC Drivers
Quick Start Guide for Windows

Using Microsoft Excel

In Microsoft Excel, you can connect to your data store by using one of the following tools:

  • Data Connection Wizard: Use this wizard to retrieve a table from your data store. When using this wizard, you can only retrieve a single table at a time, and you cannot filter the result set. For more information, see Using the Data Connection Wizard.
  • Query Wizard: Use this wizard from Microsoft Query to define and run a simple query. You can query multiple tables and columns, and sort and filter the result set. For more information, see Using the Query Wizard.
  • Microsoft Query: Work directly in the application to define and run complex queries. For example, you can define parameterized queries and different types of joins. For more information, see Using Microsoft Query.

Note:

For Drill data stores, the schema that you use determines which types of data objects you can select and how you can work with them:

  • When using a Hive schema, you can select tables or views.
  • When using an HBase schema, you can select views, or select families and then modify the query to specify the data to select. In Excel, families are displayed in the list of data objects as tables.
  • When using a DFS schema, you can select views or files. Excel does not display files in the list of data objects, so you must use a query to select them.

The following procedures are written for Microsoft Excel 2013. Simba ODBC drivers also support Excel 2010.

Important:

Make sure that the bitness of the driver that you are using matches the bitness of Excel. If you are using the 32-bit version of Excel, then you need to use the 32-bit version of the driver. If you are using the 64-bit version of Excel, then you need to use the 64-bit version of the driver. For more information about diagnosing the issue, see Architecture Mismatch Problems.

Using the Data Connection Wizard

To connect using the Data Connection Wizard:

  1. In Excel, select the Data tab in the ribbon, then click From Other Sources in the Get External Data group, and then click From Data Connection Wizard.
  2. In the Data Connection Wizard, select ODBC DSN in the list of data source types, and then click Next.
  3. In the list of ODBC data sources, select your DSN, and then click Next.
  4. If you are prompted to authenticate the connection, type your credentials for accessing the data store and then click OK.
  5. From the list of tables, select the name of a table that you want to retrieve, and then click Finish.
  6. In the Import Data dialog box, in the Existing Worksheet field, specify the cell where you want the top left corner of the selected table to be inserted, and then click OK.
  7. If you are prompted to authenticate the connection again, type your credentials for accessing the data store and then click OK.

Data from the selected table is displayed in the current Excel worksheet.

Using the Query Wizard

Note:

The Query Wizard is part of Microsoft Query. When the Query Wizard is open, you can switch from using the Query Wizard to working directly in Microsoft Query at any time by clicking Cancel and then clicking Yes at the prompt.

To connect using the Query Wizard:

  1. In Excel, select the Data tab in the ribbon, then click From Other Sources in the Get External Data group, and then click From Microsoft Query.
  2. In the Choose Data Source dialog box, on the Databases tab, select your DSN.
  3. Make sure that the Use the Query Wizard to Create/Edit Queries check box is selected, and then click OK.
  4. If you are prompted to authenticate the connection, type your credentials for accessing the data store and then click OK.
  5. In the Query Wizard, select the tables or columns that you want to include in your query by selecting each item in the left pane and then clicking the > button. When your selection is complete, click Next.
  6. Note:

    If the tables or columns that you want to include are not listed in the left pane, then click Options and configure the table options as needed.

  7. To filter the data in your query, select a column from the left pane, then select a comparison operator from the list, and then specify a value in the list on the right. Repeat as needed to define additional filters, and then click Next to proceed.
  8. To sort the data in your query, select the column on which you want to base the sorting and then specify whether to sort the data in ascending or descending order. Repeat as needed to define additional levels of sorting, and then click Next to proceed.
  9. Optionally, to save the query so that you can run it again without going through the process of recreating it, click Save Query, type a unique name for the query, and then click Save.
  10. Select Return Data To Microsoft Excel and then click Finish.
  11. Note:

    If the query fails and you encounter an error message stating "Qualified column could not be bound", then the result set might contain column names that are not valid in Microsoft Query. For more information, see Known Issues When Using Microsoft Office.

  12. In the Import Data dialog box, in the Existing Worksheet field, specify the cell where you want the first column header of the result set to appear, and then click OK.

Data retrieved from the defined query is displayed in the current Excel spreadsheet.

Using Microsoft Query

To connect by working directly in Microsoft Query:

  1. In Excel, select the Data tab in the ribbon, then click From Other Sources in the Get External Data group, and then click From Microsoft Query.
  2. In the Choose Data Source dialog box, on the Databases tab, select your DSN.
  3. Clear the Use the Query Wizard to Create/Edit Queries check box, and then click OK.
  4. If you are prompted to authenticate the connection, type your credentials for accessing the data store and then click OK.
  5. In the Add Tables dialog box, select the tables that you want to include in your query by selecting each table and then clicking Add. When your selection is complete, click Close. If necessary, you can reopen the dialog box to add more tables by clicking Add Table(s) Add Table(s).
  6. Note:

    If the tables that you want to include are not listed in the dialog box, then click Options and configure the table options as needed.

  7. To define joins between your tables, in the upper pane, click and drag a field from one table to a field in another table. You can then double-click the line between the fields to modify the join.
  8. Note:

    For detailed information about defining joins in Microsoft Query, see the "Creating, Changing, and Removing Joins" section in the Microsoft Query Help documentation that is provided in the application.

  9. Select the columns that you want to include in your query by double-clicking the column names from the tables. You can double-click the asterisk (*) to select all the columns from a table.
  10. Note:

    If Auto Query Auto Query is enabled, then the results of your query automatically appear in the lower pane. Otherwise, you must click Query Now Query Now to run the query and see the results.

  11. To filter the data in your query, click Show/Hide Criteria Show/Hide Criteria and then use the options in the criteria pane to define your filters.
  12. To sort the data in your query, select a column header in the lower pane and then click Sort Ascending Sort Ascending or Sort Descending Sort Descending as needed.
  13. If Auto Query Auto Query is disabled, then click Query Now Query Now to run your query and see the results.
  14. When you are finished defining your query, click Return Data Return Data to retrieve the result set in Excel.
  15. In the Import Data dialog box, in the Existing Worksheet field, specify the cell where you want the first column header of the result set to appear, and then click OK.

Data retrieved from the defined query appears in the current Excel spreadsheet.

Note:

For more information about defining complex queries using Microsoft Query, see the Microsoft Query Help documentation that is provided in the application.