Importing Data from Salesforce.com using SOQL
XL-Connector 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 Get Data button in your XL-Connector ribbon.
One important thing to remember when using XL-Connector Get Data dialog is that it will run any text entered in the big text box as a SOQL query against the Salesforce database you’re currently logged in to. Essentially, you don’t need to use the rest of the controls in this dialog box at all, as long as you can craft a syntactically correct SOQL query. If you’re experiencing any troubles with SOQL, please refer to Salesforce’s documentation.
All other controls in the Get Data dialog are there to help you build a correct query providing you a view into your Force.com schema and making sure you’re using the correct object names, field names, and pick list values. These controls are called ‘query builder controls’.
Another important set of controls in the dialog box control the refreshing of text in the query box based on the current selections of the query builder controls:
If the ‘Auto-generate query’ box is checked – any change to the query builder controls will rebuild the query in the query box. Please be careful with this setting if you are building a more complicated query that requires manual entry, it is always a good idea to uncheck that box and use the ‘Refresh query’ button (green circular arrows) if you really need to refresh the query based on the query builder selections.
Saving and Loading Queries
The Get Data dialog lets you save your queries locally in theXL-Connector settings and later retrieve and reuse them. The saved queries will only be available to you on the same computer, if you need to take a query to a different computer, just copy-paste it to any text editor and save to a file.
Grabbing values from your spreadsheet
Sometimes when building a query you need a list of values that are already present in your spreadsheet. As an example, lets take the following query:
SELECT Name,Id,BillingAddress FROM Account WHERE Id IN(‘001o000000XaAPyAAN’,’001o000000XaAPzAAN’, ‘001o000000XaAQ0AAN’,’001o000000XaAQ1AAN’, ‘001o000000XaAPxAAN’,’001o000000XbeqsAAB’, ‘001o000000XbeqtAAB’,’001o000000XbeqmAAB’,’001o000000XbeqnAAB’,’001o000000XbeqoAAB’)
As you can see there’s quite a bit of typing involved and it would be pretty hard to get all the IDs right if you were to type them all in manually. Fortunately,XL-Connector can grab a list of values directly from the spreadsheet, format them properly and append to the end of the query with the help of ‘Grab from spreadsheet’ button:
Building More Complex Queries
As we mentioned at the beginning of this article, you can use any SOQL query to pull data from Salesforce.com to your Excel spreadsheet. When your ‘Auto-refresh query’ box is unchecked, you can still use the object and fields selection drop downs, and then add the whole or a part of the query generated by them to the existing query that you are building without overwriting it.
Click the following button to append the whole query generated by the query builder controls at the end of the existing query:
Click this button to append only the filter part of the query generated by the query builder controls to the end of the existing query. The filter part of the query is everything after the WHERE clause:
Positioning your Data
By default, data pulled from your Salesforce.com database will start at column 1 row 1 of your spreadsheet. But you can put it elsewhere if you wish. Just enter the desired address of the data’s leftmost upper cell in the ‘First cell address’ field, or click the button next to it and choose the cell directly from your spreadsheet.
It is also possible to put your data columns into non contiguous columns (if you want to have formulas between columns, for example):
Enforcing Picklist Values
If you are pulling values from columns of ‘Pick list’ type in Salesforce, you have an option to create drop-down lists right inside of Excel in each row of the data for such columns. To enable this feature, you will need to check the ‘Enforce pick list values’ box in the Options tab of the Get Data dialog before running the query.
Populating your multi-select pick lists will be really easy too:
Additionally, all your pick list dependencies are observed if both dependent and controlling pick lists are pulled into the spreadsheet.
Naming Your Columns
By default the columns in your returned data will be named according to the field names in your query, however you can optionally specify the desired column name for each field by using the AS keyword, for example:
SELECT Id AS Opportunity Id, Name AS Opportunity Name, Account.Name AS Account Name, Amount as Opportunity Amount FROM Opportunity
XL-Connector lets you replace parts of SOQL with the following dynamic data:
- Currently logged in user information. Use $User, $Profile, and $UserRole to grab information about the currently logged in user. For example:
SELECT Id, Name FROM Opportunity WHERE Owner.Name = $User.Name
SELECT Id, Name FROM Opportunity WHERE LastModifiedBy.ProfileId = $Profile.Id
SELECT Id, Name FROM Opportunity WHERE Owner.UserRole.Name = $UserRole.Name
Any field of the User, Profile, and UserRole objects can be used in these queries. - 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] - A range of values. For example:
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 square 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. - All values in a given Data Table column. For example:
SELECT Id, Name FROM Opportunity WHERE Id IN [@TableName[ColumnName]]