Search

Excel Desktop

Excel Desktop

This page outlines the steps to connect Microsoft Excel Desktop to CData Connect.

Install the Add-In

You can install the Excel Desktop add-in from the CData Connect dashboard or from Excel Desktop.

From the CData Connect Dashboard

  1. Open the Client Tools page of the CData Connect dashboard.

  2. Type Excel Desktop into the search field, then click the data source name. This opens a tab to download the CData Connect Excel add-in from Microsoft.

  3. Click Get it now. If you are prompted, sign into your Microsoft account.

  4. On the Launch page, click Open in Excel.

    In Excel Desktop, the New Office Add-In pane opens.

  5. Click Trust this add-in.

  6. Continue to the steps in Connect to CData Connect.

From Excel Desktop

  1. Open Excel Desktop to a new or existing workbook.

  2. Click the Home tab on the Excel Desktop navigation ribbon.

  3. Click Add-ins and then click Get Add-ins in the pop-up window.

  4. In the Office Add-ins window, search for CData. In the search results, click Add next to CData Connect.

    An authorization dialog opens.

  5. Click Continue in the dialog.

  6. Continue to the steps in Connect to CData Connect.

Connect to CData Connect

After you install the Excel Desktop add-in, follow these steps to connect to your CData Connect account:

  1. Open the Data tab on the Excel Desktop navigation ribbon and click CData Connect.

    The CData Connect add-in pane opens.

  2. Click Authorize.

  3. Enter your CData Connect user and password and click Continue.

  4. Click Accept if you are prompted to provide permissions.

    The CData add-in pane appears to the right of your Excel workbook.

Set Up Connection

If you do not yet have a data connection to CData Connect, you need to set one up.

  1. In the add-in pane, click Setup Connection.

    A new window displays the Add Connection screen for CData Connect.

  2. Select the connector for the data you wish to connect to Excel Desktop.

  3. Enter your data connection settings. Save and test your connection.

    After you have a successful connection, you can Import Data.

Import Data

To import data from CData Connect to Excel Desktop, follow these steps:

  1. Open the Data tab on the Excel Desktop navigation ribbon and click CData Connect.

    The CData Connect add-in pane opens.

  2. Click Import.

  3. Choose a connection from the drop-down menu.

    You can now query the data you want to import.

  4. Select Query Builder to build the query in steps or select Custom SQL to enter a query manually. Follow the relevant instructions below.

Query Builder

  1. If using the Query Builder, choose a table from the drop-down list.

    A list of columns and column metadata for the table appears. By default, all columns are selected for inclusion in the query.

  2. (Optional) Deselect the unnecessary columns from your query.

  3. (Optional) Select one or more filters for your query.

  4. (Optional) Select one or more columns to sort by.

  5. Select Set Limit to set a limit of the number of results returned.

  6. Review the Generated query for accuracy.

  7. Click Execute to run your query. Excel imports the data that matches your query into the current sheet.

Custom SQL

  1. If using custom SQL, enter your SQL statement in the text box.

  2. Click Execute to run your query. Excel imports the data that matches your query into the current sheet.

Write Parameterized Queries

You can create dynamic spreadsheets by referencing cells in your custom SQL statement. The Excel Desktop add-in executes the statement as a parameterized query. As you change the values in the sheet, the Excel Desktop add-in executes a new query.

Create Searchable Spreadsheets

Use a parameterized SELECT WHERE query to create a spreadsheet that dynamically filters the data as you edit cells. The following filter criteria references the @InputSheet!A2 parameter, which is cell A2 of the sheet InputSheet. The @ prefix indicates that Industry is a required parameter.

SELECT BillingState, Name FROM Account WHERE Industry = @InputSheet!A2

Refresh Data

Once your data is imported, you can set it to periodically refresh automatically. You can also perform a manual update. The automatic refresh overwrites any uncommitted data. If you are only reading data, this is not a problem. If you are writing data, commit your changes often.

You must have the Excel workbook open in order for the automatic refresh to work properly.

  1. On the CData Connect add-in pane, select Refresh.

  2. Check the box of the data sheet or sheets to refresh.

  3. To manually refresh, click Refresh Now. To set up automatic updates, instead click Auto Refresh.

  4. On the Auto Refresh Interval, select the delay between refreshes and click Set.

Note that the refresh interval is set in hours, and one hour is the minimum interval between refreshes.