And now for something completely different! DevArt's Excel Add-In

posted May 11, 2016, 8:02 AM by Ezra Kenigsberg   [ updated May 11, 2016, 8:36 AM ]
DevArt asked me to look at their Excel Add-In for Salesforce, yet one more professional take on the homebrew Excel Connector.*

How well does it work? Pretty well. I'd give it an A-minus.** Breaking down the Good, the Bad, and the Interesting:

  • Connects to many data sources. The Add-In has connectors that enable it to talk to many cloud services and databases, not just Salesforce. I just tested the Salesforce capability for this review, but the prospect of comparing, say, live SQL Server data in tab 1 with live Salesforce data in tab 2 is mighty appealing!
  • Two methods of logging in. Nice! I connect to Salesforce using either 
    • Username+Password+Security Token, or
    • OAuth (ie, granting access in a browser window).
  • Point-and-click query UI. If I don't like writing SOQL, the Add-In offers a pretty good query window that helps...
...choose fields from multiple objects,
...specify filters,
...order results, and/or
...limit the number of results returned:

  • Preview data table before import. If I want, I can inspect a query preview before pulling a crapton of data:
  • Autofit results' rows and/or columns. I'm a big fan of this! I can tell the Add-In to autosize rows and columns to fit the data.
  • Excel Tables option. I can return results in an Excel Table, which gives me nice formatting.
  • Read-only fields in italics. When I flip the Add-In to Edit Mode, it distinguishes editable fields from read-only.
  • Fields I modify are automatically highlighted. When I modify values in Excel, they're colored yellow to indicate they'll be updated.
  • Enables adding records anywhere in the dataset. By inserting a row in the middle of the dataset, I can copy neighboring values easily.
  • Handles updates, errors, and rollback well. I love getting the confirm window like the one below, and the Add-In has a few options for how it should behave when it encounters errors.
  • Inserts. As far as I can figure, this tool is simply not made for adding more than one record at a time. If I want to add a lot of data at once, I've got to use something else.
  • ID lookup. The Add-In does allow me to pull in fields from other tables, but it doesn't have have a way to automatically convert lookup or master-detail fields to their corresponding Names.
  • The WHERE clause's UI can be confusing. Which Contacts are returned by the WHERE clause below?

    The answer is "Contacts with a Mailing Country of 'France' AND a Mailing City of 'New York'", which is difficult to discern from the UI.
  • Doesn't support TLS 1.1. As far as I could tell, I've got to shut off TLS 1.1 in "Critical Updates" if I want the Add-In to connect to Salesforce data.
  • Address fields concatenate in wrong order. The built-in Address fields on the Lead, Account, and Contact objects are displayed in the wrong order (City, Country, Postal Code, State/Province, Street), without line breaks.
  • Generates SQL, not SOQL. The query UI shows SQL, not SOQL, which is interesting. It must be translating its SQL to SOQL somewhere behind the scenes. A Contact query can show a field like "AccountId.Name", which is SQL-ishly correct, but we SOQL sticklers point out it should read "Account.Name".
  • "Strictly enforce picklist values" capability is enforced. It is enforced for standard fields like Account.Industry (which actually doesn't have a "Strictly enforce"), and enforced for custom fields regardless whether the "Strictly enforce picklist values" checkbox is active or not. And then it's not enforced for Contact.Salutation. Kooky!
I haven't kept up with all the latest edit-Salesforce-data-in-Excel tools (ah, a topic for a future post!), but this Add-In is quite competitive with Enabler4Excel, with some distinguishing pluses (eg, multiple data sources, better point-and-click query interface).

. . . and lookit that, Enabler4Excel now has a free version! Time to put it back on the Free Tools list.

Normally I wouldn't, because they don't offer a free version--only a trial version.

But I am a huge fan of their Skyvia tool, which does have a free edition, and which really helped me on a recent engagement. 

So I agreed to try it out, with the understanding that it won't go onto the Tools list (again, it's not free). They also offered to give me a free license, but I declined a
fter some thought. From here on out, I'm committing to not accepting free licenses.

** In my first draft, I called it a B-plus/A-minus, but forgot about the multiple data sources--I think that makes it a solid A-minus.