VOOZH about

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

⇱ Excel Spreadsheet Automation on Sage Intacct Data with the QUERY Formula

Excel Spreadsheet Automation on Sage Intacct Data with the QUERY Formula

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

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

About Sage Intacct Data Integration

CData provides the easiest way to access and integrate live data from Sage Intact. Customers use CData connectivity to:

  • Access Sage Intacct without worrying about API updates or changes.
  • Access custom objects and fields in Sage Intacct with no extra configuration steps involved.
  • Write data back to Sage Intacct using embedded Web Services credentials with Basic authentication.
  • Use SQL stored procedures to perform functional operations like approving or declining vendors, inserting engagements, and creating or deleting custom objects or fields.

Users frequently integrate Sage Intact with analytics tools such as Tableau, Power BI, and Excel, and leverage our tools to replicate Workday data to databases or data warehouses.

To learn about how other customers are using CData's Sage Intacct solutions, check out our blog: Drivers in Focus: Accounting Connectivity.


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 Sage Intacct data records you want to retrieve or the modifications to be made, written in standard SQL.

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

    To connect using the Login method, the following connection properties are required: User, Password, CompanyId, SenderId and SenderPassword.

    User, Password, and CompanyId are the credentials for the account you wish to connect to.

    SenderId and SenderPassword are the Web Services credentials assigned to you by Sage Intacct.

  • 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 Sage Intacct data, such as CustomerId.
  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 Customer WHERE CustomerId = '"&B6&"'","User="&B1&";CompanyId="&B2&";Password="&B3&";SenderId="&B4&";SenderPassword="&B5&";Provider=SageIntacct",B7)
    👁 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 Sage Intacct to get started:

 Download Now

Learn more:

👁 Sage Intacct Icon
Excel Add-In for Sage Intacct

The Sage Intacct Excel Add-In is a powerful tool that allows you to connect with live data from Sage Intacct, directly from Microsoft Excel.

Use Excel to read, write, and update Sage Intacct Contacts, Invoices, Transactions, Vendors, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!