How To: Loading Opportunity Product Data

Data migration using the Salesforce.com Apex Data Loader is pretty straight forward once you understand how to do it unless you need to load individual opportunity line items. For those who have loaded opportunity product data into Salesforce and for those who haven’t had the pleasure to yet, this blog is a nice little reference to stash away in your Evernote account.

Step 1: Price Book Setup

As with all data loading with Salesforce the sequencing of the data load is the most important.  In this case it is no different.  The first step in the process is the Price Book.  This is done in Salesforce under the Products Tab by going to the “Manage Price Books” link located in the bottom right-hand corner of the page.  There is no limit to how many you can add but the Standard Price Book MUST be active.

Step 2: Insert Products

Next step in the process is to load your products.  Both Step 2 and 3 can be done manually in Salesforce but in case you have a lot, here is the process to load them by the Data Loader. Start by setting up a .csv file with all the products you want to load matching your column headers to the fields in Salesforce. You need to make sure all Products have a Product Name as it is required and if you use Record Types you must get the appropriate 18 digit ID.  This can be retrieved by exporting the “Record Type” table using the Data Loader. Once you have the file ready you can start the Data Loader and insert your products into the “Products2” table.  Make sure you save the success file in a folder as you will need it for reference later.

Step 3: Load Products into Price Books

In this step you are basically associating a given product to a price book.  For each price book you are associating to a given product you must have a separate row in your file.  There are 3 key fields you will need to have for this to work.

1. The Price Book2 ID which can be retrieved by exporting the the “Price Book 2” Table from the Data Loader.

2. The Product2 ID which is the ID’s you got from Step 2.

3. The 3rd is the unit price which is the price that you want the Price Book to associate to the product. If you are using a custom price book, then you must also set the Use Standard Price field to false.  Once the file is prepared, you start the Data Loader and insert your file into the “Price Book Entry” table. Make sure you save the success file in a folder as you will need it for reference for loading the opportunity line items.

Step 4: Load Opportunity Line Items

The final step in the process is to load your Opportunities and Opportunity Product Line Items. First, you want to prepare your file to load all your Opportunities.  Match all your columns to the fields in Salesforce and make sure you have an Account ID and an Opportunity Name as both are required.  Load the file in the “Opportunity” table and make sure to save the success file in a folder.  Now you are ready to load the Opportunity Product Line Items. Feel free to export the “Opportunity Line Item” table to use as a template.  First, you copy and paste all the Opportunity ID’s from your “Opportunity” table success file into the Opportunity ID column.  Second, add the Price Book Entry ID’s from your “Price Book Entry” success file to each row to associate the Product and the Price Book to the Opportunity. Lastly, add all the appropriate data to match the columns in the file like Quantity. Once the file is prepared, you start the Data Loader and insert your file to the “Opportunity Line Item” table. Make sure you test some of the opportunities in Salesforce to make sure everything is loaded properly. This may not be something you do everyday or even every month but I can assure you there will be time you will have to do this process and unfortunately there is not much documentation. Hopefully this blog post will help.

If you would like to reach out to me and discuss this in more detail please feel free to comment on our Facebook page, or tweet at me at www.twitter.com/Salvatoriello.