VOOZH about

URL: https://www.cdata.com/kb/tech/odata-excel-cdataquery.rst

⇱ Excel Spreadsheet Automation on OData Services with the QUERY Formula

Excel Spreadsheet Automation on OData Services with the QUERY Formula

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
Pull data from OData, automate spreadsheets, and more with the QUERY formula.

The CData Excel Add-In for OData provides formulas that can edit, save, and delete OData data. The following three steps show how you can automate the following task: Search OData services for a user-specified value and then organize the results into an Excel spreadsheet.

About OData Data Integration

CData simplifies access and integration of live OData services data. Our customers leverage CData connectivity to:

  • Access OData versions 2.0, 3.0, and 4.0, working with legacy services and the latest features and capabilities.
  • Leverage advanced query options, including $filter, $select, and $expand, enhancing data retrieval from 3rd party tools.
  • Use Server-side execution of aggregation and grouping to minimize data transfer and boost performance.
  • Authenticate securely using a variety of schemes, including Azure AD, digest, negotiate, NTLM, OAuth, and more means secure authentication with every connection.
  • Use SQL stored procedures to manage OData service entities - listing, creating, and removing associations between entities.

Customers use CData's solutions to regularly integrate their OData services with preferred tools, such as Power BI, MicroStrategy, or Tableau, and to replicate data from OData services to their databases or data warehouses.


Getting Started


The syntax of the CDATAQUERY formula is the following: 

=CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

  • Query: The declaration of the OData services records you want to retrieve or the modifications to be made, written in standard SQL.

  • Connection: Either the connection name, such as ODataConnection1, or a connection string. The connection string consists of the required properties for connecting to OData services, separated by semicolons.

    The User and Password properties, under the Authentication section, must be set to valid OData user credentials. In addition, specify a URL to a valid OData server organization root or OData services file.

  • ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

  1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search OData services, such as ShipCity.
  2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
    3. =CDATAQUERY("SELECT * FROM Orders WHERE ShipCity = '"&B5&"'","URL="&B1&";UseIdUrl="&B2&";OData Version="&B3&";Data Format="&B4&";Provider=OData",B6)
    👁 Formula inputs used in this example. (Google Apps is shown.)
  3. Change the filter to change the data. 👁 The outputs of the formula. (Google Apps is shown.)

Ready to get started?

Download a free trial of the Excel Add-In for OData to get started:

 Download Now

Learn more:

👁 OData Icon
Excel Add-In for OData

The OData Excel Add-In is a powerful tool that allows you to connect with live OData services directly from Microsoft Excel.

Use Excel to search, aggregate, read, write, and update live OData feeds directly. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!