Ready to get started?

Learn more about the CData Excel Add-In for Magento or download a free trial:

Download Now

Excel Spreadsheet Automation with the QUERY Formula

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

The CData Excel Add-In for Magento provides formulas that can edit, save, and delete Magento data.The following three steps show how you can automate the following task: Search Magento 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 Magento data records you want to retrieve or the modifications to be made, written in standard SQL.
  • Connection: Either the connection name, such as MagentoConnection1, or a connection string. The connection string consists of the required properties for connecting to Magento data, separated by semicolons.

    Magento uses the OAuth 1 authentication standard. To connect to the Magento REST API, you will need to obtain values for the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties by registering an app with your Magento system. See the "Getting Started" section in the help documentation for a guide to obtaining the OAuth values and connecting.

    You will also need to provide the URL to your Magento system. The URL depends on whether you are using the Magento REST API as a customer or administrator.

    • Customer: To use Magento as a customer, make sure you have created a customer account in the Magento homepage. To do so, click Account -> Register. You can then set the URL connection property to the endpoint of your Magento system.

    • Administrator: To access Magento as an administrator, set CustomAdminPath instead. This value can be obtained in the Advanced settings in the Admin menu, which can be accessed by selecting System -> Configuration -> Advanced -> Admin -> Admin Base URL.

      If the Use Custom Admin Path setting on this page is set to YES, the value is inside the Custom Admin Path text box; otherwise, set the CustomAdminPath connection property to the default value, which is "admin".

  • 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 Magento data, such as Style.
  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 Products WHERE Style = '"&B5&"'","OAuthClientId="&B1&";OAuthClientSecret="&B2&";CallbackURL="&B3&";Url="&B4&";Provider=Magento",B6)
  4. Change the filter to change the data.