Learn more
 

Ready to get started?

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

 Download Now

Learn more:

REST Icon Excel Add-In for REST

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

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

In this article

Related articles

Excel Spreadsheet Automation with the QUERY Formula


Pull data, automate spreadsheets, and more with the QUERY formula.

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

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

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

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

    See the Getting Started chapter in the data provider documentation to authenticate to your data source: The data provider models REST APIs as bidirectional database tables and XML/JSON files as read-only views (local files, files stored on popular cloud services, and FTP servers). The major authentication schemes are supported, including HTTP Basic, Digest, NTLM, OAuth, and FTP. See the Getting Started chapter in the data provider documentation for authentication guides.

    After setting the URI and providing any authentication values, set Format to "XML" or "JSON" and set DataModel to more closely match the data representation to the structure of your data.

    The DataModel property is the controlling property over how your data is represented into tables and toggles the following basic configurations.

    • Document (default): Model a top-level, document view of your REST data. The data provider returns nested elements as aggregates of data.
    • FlattenedDocuments: Implicitly join nested documents and their parents into a single table.
    • Relational: Return individual, related tables from hierarchical data. The tables contain a primary key and a foreign key that links to the parent document.

    See the Modeling REST Data chapter for more information on configuring the relational representation. You will also find the sample data used in the following examples. The data includes entries for people, the cars they own, and various maintenance services performed on those cars.

  • 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 REST data, such as [ personal.name.last ].
  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 people WHERE [ personal.name.last ] = '"&B4&"'","DataModel="&B1&";URI="&B2&";Format="&B3&";Provider=REST",B5)
  3. Change the filter to change the data.