Friday, March 28, 2014

Load Baseball Teams into SFDC without DataLoader

It's spring and I wanted to populate a sandbox Salesforce with major league basedball teams, their rosters, and a few opportunities and cases (made up of course).  Given the hassle of managing all the links between related objects with DataLoader, I knew I want something a lot simpler.

My approach: With a short Python script and an Excel workbook (one sheet per SFDC table) I managed to import teams and rosters as quickly as I could type them into Excel.

My source data in Excel was simple:

  • the first row contained SFDC column names EXCEPT for column 1.
  • Column #1 contained a synthetic primary key that I used to refer to the row from other spreadsheets.
For example: The St. Louis Cardinals Account row looked like:
CardsSt. Louis CardinalsSt LouisBaseball

Contact records referred to their associated team using synthetic keys. For example, here is a player from the Cardinals.


The special syntax AccountId->Account tells the Python import script to look up the parent record id at run time based on the SFDC id given to the synthetic key.

Is this clever? Probably not so much. But it did make it possible to import a lot of interrelated data into Salesforce with the hassle of importing CSV's one at a time and keeping track of SFDC ids.

For the record, the Python script is in Python 3 and uses two extension modules:

  • xlrd for reading Excel workbooks
  • SQLForce for talking to Salesforce.