Using Microsoft Excel

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

• Data Connection Wizard: Use this wizard to retrieve a table from Spark. 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.

The following procedures are written for Microsoft Excel 2013. The Simba Spark ODBC Driver also supports Excel 2010.

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. From the list of tables, select the name of a table that you want to retrieve, and then click Finish.
5. 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.

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

Using the Query Wizard

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. 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.

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.

5. 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.
6. 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.
7. 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.
8. Select Return Data To Microsoft Excel and then click Finish.
9. 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. 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) .

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.

5. 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.

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.

6. 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.

Note:

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

7. To filter the data in your query, click Show/Hide Criteria and then use the options in the criteria pane to define your filters.
8. To sort the data in your query, select a column header in the lower pane and then click Sort Ascending or Sort Descending as needed.
9. If Auto Query is disabled, then click Query Now to run your query and see the results.
10. When you are finished defining your query, click Return Data to retrieve the result set in Excel.
11. 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.

Related topics

• Troubleshooting