Importing Data from Salesforce.com using SOQL (Salesforce Object Query Language)

XL-Connector 365 has a very powerful tool allowing you to pull ANY data from your Force.com database into Excel.

To pull data from a Force.com database to Excel using a SOQL query, click the SOQL button in your XL-Connector ribbon:

If you know your way around SOQL, you can simply type or paste your query directly into the query box and hit Execute:

If you want to browse your objects and fields, you can use the query builder to put together a basic query using it’s controls:

There are some options that you can adjust for each query as well:

Create Data Table – will create an Excel Data Table around the pulled data. Data Tables are useful when building pivot tables and charts based on the pulled data.

Table Start Cell – defines the starting point for the data in your spreadsheet. You can place the data anywhere on the sheet.

Create New Worksheet – if checked, will always create a new worksheet and place the pulled data there.

Query All – this is an equivalent of using the ALL ROWS keyword in your query. Will pull archived and deleted records. Particularly useful for pulling of archived tasks and events.

Each SOQL query that you pull to a specific worksheet is saved inside of that worksheet and can be later re-pulled by clicking on one of the Refresh buttons:

  1. Refreshes all data sources on the currently active worksheet (visible when Active toggle is on, default value).
  2. Refreshes all data sources on all worksheets in the current workbook (switch the Active toggle to All to see them)
  3. Refreshes the specific data source.

Dynamic SOQL

XL-Connector lets you replace any part of your query with values coming from your spreadsheet by specifying the address(es) of where to take the text from within double curly braces: {{range}}

  1. Contents of a single cell. For example:
    SELECT Id, Name FROM Opportunity WHERE Owner.Name = ‘{{C2}}’ – grabs the value from the C2 cell of the currently active sheet. Be careful with this one, if you have the ‘Create new sheet’ option checked in your Get Data dialog – it will look at the cell C2 of the newly created sheet and fail because it will be empty. To work around this you can use the absolute path to the cell:
    SELECT Id, Name FROM Opportunity WHERE Owner.Name = ‘{{Sheet1!C2}}’
    SELECT Id, Name FROM Opportunity WHERE Amount > {{Sheet1!H10}}
  2. A range of values. For example:
    SELECT Id, Name FROM Opportunity WHERE Id IN {{MyCustomRange}}
    SELECT Id, Name FROM Opportunity WHERE Id IN {{A1:A20}}

    SELECT Id, Name FROM Opportunity WHERE Name IN {{Sheet1!C1:C10}}
    When using a range, the double curly brackets and range address will be replaced with a list of quoted values from all non-empty cells of the range, enclosed in parentheses:
    (‘Name1’, ‘Name2’, ‘Name3’)
    Please note that you can’t use Date, DateTime, Boolean, or Number fields when using a dynamic range of values since all values in the range will be enclosed in quotes.
  3. All values in a given Data Table column. For example:
    SELECT Id, Name FROM Opportunity WHERE Id IN {{@TableName[ColumnName]}}
  4. You can also replace other parts of the query with dynamic data, for example:
    SELECT {{A1}}, {{B1}}, {{C1}} FROM {{D1}} WHERE {{E1}} IN {{MyCustomRange}}