Querying DataCData Excel Add-In for OData 2019 - Build 19.0.7354
The add-in integrates with the Excel toolbar and ribbon, adding controls for querying and editing OData.
Building an SQL SELECT Query
After Establishing a Connection, select tables, columns, and filters -- as you make changes, the add-in generates the underlying SELECT query. Edit the query directly to execute joins, aggregations, or more complex SELECT Statements. See Data Model for more information on how the add-in models OData as a database, including any required columns to search or update data.
- Click "From OData" from the CData ribbon to open the Data Selection wizard.
- In the Connection menu, select a connection or select the option to create a new connection.
- Select a table to start building the query. You can define filters, column aliases, and a limit on the records to return.
Querying Data with Cell References and Formulas
By Writing Parameterized Queries, you can reference cells to dynamically search OData. The following query filters on cell A2 of the sheet InputSheet:
SELECT * FROM Lead WHERE FirstName = @InputSheet!A2
To execute parameterized queries in the Data Selection dialog, you need to edit the query directly.
Setting the Location of Results
In the Sheet Name box, enter the name of the worksheet that should contain the retrieved data. Additionally, in the Start Data at Row box enter the row where the query results should start.
Refreshing Data Automatically
Select the Enable Auto Refresh option to poll OData for changes over an interval specified in seconds. You can also refresh the spreadsheet on demand from the ribbon.
Note that the refresh overwrites local changes -- if you have local changes, you are prompted to accept a warning before refreshing the sheet.