Search

Excel Desktop

Excel Desktop

This page outlines the steps to install and configure the CData Connect Spreadsheets add-in for Excel Desktop. After installation, Excel Desktop is able to pull data from sources that you have connected to your CData Connect Spreadsheets account.

Note: Before you can configure and use Excel Desktop with CData Connect Spreadsheets, you must first connect a data source to your CData Connect Spreadsheets account. See Set Up Connection for more information.

CData Connect Cloud customers only: You can also import Virtual Datasets and Derived Views. To create a virtual dataset, follow the instructions in Virtual Datasets. To create a derived view (administrators only), follow the instructions in Creating a Derived View.

Install the Add-In

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

From the CData Connect Spreadsheets Dashboard

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

  2. Type Excel Desktop into the search field, then click the data source name. This opens a tab to download the CData Connect Spreadsheets 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 Spreadsheets.

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

    An authorization dialog opens.

  5. Click Continue in the dialog.

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

Connect to CData Connect Spreadsheets

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

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

    The CData Connect Spreadsheets add-in pane opens.

  2. Click Authorize.

  3. Enter your CData Connect Spreadsheets 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 Spreadsheets, 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 Spreadsheets.

  2. Select the connector for the data you want 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 Spreadsheets to Excel Desktop, follow these steps:

  1. Click Import.

  2. Select an option from the drop down menu: Connections, Virtual Datasets, or Derived Views. Then follow the directions for the option you selected. Note: Only CData Connect Cloud customers can access virtual datasets and derived views.

Import Connections

  1. Select a connection from the drop-down list.

  2. Select either Query Builder to build the query in steps or Custom SQL to enter a query manually.

    • For Query Builder, select a schema (if there are multiple schemas), table, and columns. You can search by column name. If desired, you can also set filters, sorting options, and limits. View the generated query and adjust if necessary.

    • For Custom SQL, enter the SQL Statement in the provided field.

  3. Click Execute.

  4. When prompted, choose to output the data in either the current spreadsheet or a new one.

Import Virtual Datasets (CData Connect Cloud Customers Only)

  1. Select a workspace from the drop-down list.

  2. Select Query Builder to build the query in steps or select Custom SQL to enter a query manually.

    • For Query Builder, select a virtual dataset and the columns to display. You can search by column name. If desired, you can also set filters, sorting options, and limits. View the generated query and adjust if necessary.

    • For Custom SQL, enter the SQL Statement in the provided field.

  3. Click Execute.

  4. When prompted, choose to output the data in either the current spreadsheet or a new one.

Import Derived Views (CData Connect Cloud Customers Only)

  1. Select Query Builder to build the query in steps or select Custom SQL to enter a query manually.

    • For Query Builder, select a virtual dataset and the columns to display. You can search by column name. If desired, you can also set filters, sorting options, and limits. View the generated query and adjust if necessary.

    • For Custom SQL, enter the SQL Statement in the provided field.

  2. Click Execute.

  3. When prompted, choose to output the data in either the current spreadsheet or a new one.

Refresh Data

Once your data is imported, you can set it to periodically refresh automatically. You can also perform a manual refresh of your spreadsheet data. The automatic refresh overwrites any uncommitted data. If you are only reading data, the automatic update is not an issue. If you have enabled auto refresh and are writing data, commit your changes often with the Update button.

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

  1. On the CData Connect Spreadsheets add-in pane, click 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 auto-refresh interval is set in hours, and one hour is the minimum interval between auto-refreshes. If the auto-refresh fails, the Excel add-in will attempt the auto-refresh two more times. After the third fail, the next auto-refresh attempt will occur at the next user set interval.

You can also remove a query or queries from the refresh list by selecting the query and clicking Remove.

Update Data

You can push changes from the Excel spreadsheet to the originating data source. Note that in order to update a spreadsheet, the data must contain at least one primary key. You must also have the proper permissions to update data from the source.

Be sure to update your data frequently if you are using the auto refresh feature, or your changes may be overwritten.

  1. Make your changes to the Excel spreadsheet.

  2. On the CData Connect Spreadsheets add-in pane, click Update. You can choose to update the entire spreadsheet or just the selected rows. Click Confirm.

Delete Data

You can delete selected records from the Excel spreadsheet, which then deletes the records from the originating data source. Note that in order to delete rows from a spreadsheet, the data must contain at least one primary key. You must also have the proper permissions to delete data from the source.

  1. Select the rows you want to delete and click Delete. You can select any cell(s) in the row and CData Connect Spreadsheets deletes the entire row. CData Connect Spreadsheets prompts if you are sure you want to delete the given number of rows.

    Note: You cannot undo the row deletion.

  2. Click Confirm to continue with the deletion. CData Connect Spreadsheets displays whether it deleted the rows successfully.

Logs

Click Logs to open a dialog that lists the most recent queries, including:

  • The time and date they occurred
  • Their results (success or failure)
  • The contents and parameters of the queries

Advanced Query Settings

When importing data, you can use Filters and Sorting to build your query. For SQL queries, you can use the advanced options Parameterized Queries and Searchable Spreadsheets.

Filters

To add a filter, click the + next to the Filters header. You can add more filters by clicking the + again, and you can delete a filter by clicking the trash can icon next to it.

Each filter has three fields to fill out:

  • Column—select the column from your table that you want to filter.
  • Operator—the operation the filter performs. Options are equals, does not equal, contains, does not contain, less than, less than or equal to, greater than, and greater than or equal to.
  • Value—the value for the filter operation.

For example, if you wanted to retrieve AccountValues above $100,000, you might set the Column to AccountValues, the Operator to greater than, and the Value to 100,000. Then, when you execute the query, only results matching this filter will be returned.

As you enter your filter parameters, the Generated Query at the bottom of the pane automatically updates.

Sorting

To add a sorting rule to your query results, click the + next to the Sort By header. You can add more sorting rules by clicking the + again, and you can delete a sorting rule by clicking the trash can icon next to it.

Each sorting rule requires a Column and a sorting Order (ascending or descending). If you add multiple sorting rules, the results are sorted in the order that the rules appear. The query gives highest sorting priority to the first rule, then the second rule, etc.

As you enter your sorting parameters, the Generated Query at the bottom of the pane automatically updates.

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