Learn more
 

Ready to get started?

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

 Download Now

Learn more:

Cosmos DB Icon Excel Add-In for Cosmos DB

The Cosmos DB Excel Add-In is a powerful tool that allows you to connect with live Cosmos DB document databases, directly from Microsoft Excel.

Use Excel to read, write, and update Cosmos DB. 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 Cosmos DB provides formulas that can edit, save, and delete Cosmos DB data. The following three steps show how you can automate the following task: Search Cosmos DB 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 Cosmos DB data records you want to retrieve or the modifications to be made, written in standard SQL.

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

    To obtain the connection string needed to connect to a Cosmos DB account using the SQL API, log in to the Azure Portal, select Azure Cosmos DB, and select your account. In the Settings section, click Connection String and set the following values:

    • AccountEndpoint: The Cosmos DB account URL from the Keys blade of the Cosmos DB account
    • AccountKey: In the Azure portal, navigate to the Cosmos DB service and select your Azure Cosmos DB account. From the resource menu, go to the Keys page. Find the PRIMARY KEY value and set AccountKey to this value.
  • 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 Cosmos DB data, such as Name.
  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 Customers WHERE Name = '"&B3&"'","AccountEndpoint="&B1&";AccountKey="&B2&";Provider=CosmosDB",B4)
  3. Change the filter to change the data.