Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for Azure Table provides formulas that can edit, save, and delete Azure Table data. The following three steps show how you can automate the following task: Search Azure Table 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 Azure Table data records you want to retrieve or the modifications to be made, written in standard SQL.

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

  Specify your AccessKey and your Account to connect. Set the Account property to the Storage Account Name and set AccessKey to one of the Access Keys. Either the Primary or Secondary Access Keys can be used. To obtain these values, navigate to the Storage Accounts blade in the Azure portal. You can obtain the access key by selecting your account and clicking Access Keys in the Settings section.

• 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 Azure Table data, 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.
=CDATAQUERY("SELECT * FROM NorthwindProducts WHERE ShipCity = '"&B3&"'","AccessKey="&B1&";Account="&B2&";Provider=AzureTables",B4)
3. Change the filter to change the data.