Data Into Excel

Owned by Jodie Miners
Last updated: Jul 25, 2018

So I LOVE LOVE LOVE Excel Power Query, but recently I have been alerted to some other options. Now Google has released Data Connector for Salesforce too!

BEWARE!

 Just because you love Excel, it may not necessarily meant that you **should** get your data out of Salesforce and into Excel. With great power comes great responsibility. Don't use these tools to get around building things properly in Salesforce, or to use Excel to justify why Salesforce isn't working for you. If you are having issues with Salesforce that you think can only be solved by exporting the data into Excel, then at least talk to me first (details@thedetaildept.com.au), so we can work out if there is a better way.

Blockspring

https://www.blockspring.com/business

Cost : US$7/user/month. 

Pros:

  • Easy to use
  • Data comes directly into Excel
  • Can do SOQL and SOSL plus full objects. 
  • Can retrieve object Metadata, and that is hugely interesting. 
  • You can search through the full data, even searching in specific fields in specific objects eg Account(Name), Contact(Name,Email,MyField__c)
  • Heaps of connectors included
  • Nice formatting of the excel sheet
  • Really nice way of being able to change the query on the fly. 
  • You can build libraries to re-use code - not sure how that works at this stage, but it does look cool. https://www.youtube.com/watch?v=tLlp3k7tvzY (just be sure you can keep your code private though, unless you specifically want to make it public. 
  • There is a Query Builder - see Optional Parameters when doing a query. 

Cons:

  • Subject to the 2000 row limit from Salesforce, similar to Excel Power Query. 
  • The data isn't saved as text in the spreadsheet - it is in an array, so therefore you can't then use a search in Excel to find specific data. 

Use it for:

  • Mashing up data from various sources
  • Doing quick queries from Salesforce based on entering data into a cell 
  • Retrieving metadata into a really structured way really quickly.

What I would love:

  • I can select ID, Name, Body from Email Template where Name like "MyQuery" but due to the limitations of long text fields I can't do that on Body. Even if I retrieve all the Body data, I can't then search in Excel, without copying the data to a new sheet (yeah, you can't do that in Workbench even). 

Analytics Edge

http://www.analyticsedge.com/salesforce-connector/

Cost : US$6/computer/month however you have to buy the connectors separately at an additional US$4/computer/month, so if you want multiple sources it could get exy. 

Pros:

  • You can retrieve more than 2000 rows!!!!!! 
  • It has macros, so it is similar to Excel Power Query in that regard. 
  • You can query using Labels or field names
  • Has a query builder
    • You can filter by field type - nice. 
  • It imports the data directly into the spreadsheet
  • You can do formulas and things on the way in (with the right version). 
  • You can schedule exports! 
  • Very powerful overall! 
  • Watch the overview video https://www.youtube.com/watch?v=R-fvhgZGIz0 

Cons:

  • It feels quite clunky at times - it is definitely for the excel power user. 
  • I don't know how it's quite meant to work to bring back multiple queries into the one workbook, I need to do some more work on it. 
  • Their help is a little non-existent at the moment http://help.analyticsedge.com/salesforce/sf-fields/

Use it for:

  • Really detailed queries
  • Eg bring in all the email template text and then use excel filters or search to find where specific fields are used in emails. 

Other Options

These options are more expensive but also allow you to insert data into Salesforce