Connect Workday to your favorite reporting tools without moving data.
Learn More →Excel Spreadsheet Automation with the QUERY Formula
Pull data, automate spreadsheets, and more with the QUERY formula.
The CData Excel Add-In for Snowflake provides formulas that can edit, save, and delete Snowflake data. The following three steps show how you can automate the following task: Search Snowflake 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 Snowflake data records you want to retrieve or the modifications to be made, written in standard SQL.
Connection: Either the connection name, such as SnowflakeConnection1, or a connection string. The connection string consists of the required properties for connecting to Snowflake data, separated by semicolons.
To connect to Snowflake:
- Set User and Password to your Snowflake credentials and set the AuthScheme property to PASSWORD or OKTA.
- Set URL to the URL of the Snowflake instance (i.e.: https://myaccount.snowflakecomputing.com).
- Set Warehouse to the Snowflake warehouse.
- (Optional) Set Account to your Snowflake account if your URL does not conform to the format above.
- (Optional) Set Database and Schema to restrict the tables and views exposed.
See the Getting Started guide in the CData driver documentation for more information.
- 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.
- 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 Snowflake data, such as Id.
- 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.
- Change the filter to change the data.
=CDATAQUERY("SELECT * FROM Products WHERE Id = '"&B7&"'","User="&B1&";Password="&B2&";Server="&B3&";Database="&B4&";Warehouse="&B5&";Account="&B6&";Provider=Snowflake",B8)
