Import Multiple Tables From Excel

This example shows how to import data into multiple tables directly from an Excel workbook.

Importing a spreadsheet into a single Salesforce table is not a difficult task — it can be done with a 16 line SQLForce Python script or a popular data loader. However, loading data into multiple tables where records from the various tables must be linked together (e.g. Account to Contacts) is not so easy with a data loader. This example illustrates how loading multiple tables can be a fairly easy task using SQLForce and Python.

The Excel Workbook included with this information contains information about the St. Louis Cardinals baseball team, and includes lots of information:

• The Cardinals’ account and accounts for their farm teams.
• Accounts for a few of the Cardinals’ rivals.
• Rosters (e.g. Contacts) for the Cardinals.
• Stuff that baseball teams typically buy — beer, balls, bats, gloves (e.g. Products and Pricebook Entries).
• Purchases that the Cardinals made for spring training and the 2014 season (e.g. Opportunities and Line items).
• Problems that the Cardinal coaches had to fix (e.g. Cases).

In total there are nine tables where data is imported.

Each worksheet in the sample workbook contains data to import into a single table. For example, these are a few rows from the Contact workbook:

The first row contains the column descriptions:

• The first column contains an optional synthetic key for each record. The synthetic key can be used to refer to the record in other worksheets.
• The second and subsequent columns contain Salesforce column names. However, if a Salesforce column name is followed by a “->TableName” value then the data for the column is assumed to contain the synthetic key of a record in another table.

For example, in the sample Contact table the AccountId->Account column label for Lou Brock means that Lou should be linked to an Account with the synthetic key called “Cardinals” in the Account worksheet.

• Download the sample script and associated data here.
• Unzip the download.
• Open the sample file “TestSalesforceData/Baseball.xls” with Excel or an equivalent.
  • The data, except for the first worksheet “Reset” will look straightforward.
  • The “Reset” tab has different functionality, and is described in the Python script.
• Open the Python script “LoadTestSalesforceData.py” in a text editor and read the documentation at the top.
  • Note that the main() entry point is hard-coded to use a Capstorm specific Salesforce connection. You will need to modify it.
• Run the command “python LoadTestSalesforceData.py” and enjoy the St. Louis Cardinals.

Our expectation is that this sample script will provide you with the base knowledge needed to create an Excel->Salesforce import tool that works for your environment.