Button Click Data Conversion

This article will talk a little bit about a few data conversion best practices and then showcase a few data conversion tools. ​

1) Get familiar with the Salesforce data structure. Generally, most clients I work with need to import Accounts, Contacts, Leads, Opportunities, Products, the general CRM objects. Salesforce has posted an excellent resource here http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_erd_majors.htm outlining how the records tie together. Take a few minutes looking at the different layouts and you will see the standard Salesforce objects that you’re familiar with, as well as some system objects you don’t need to worry so much about. Each box is a Salesforce object and the lines connecting them are “Joins”. A “Join” is what we consider a lookup relationship field or a master detail relationship field.

2) Figure out what you will be importing. Write a “mapping” spreadsheet and determine which fields from the legacy system will be brought into Salesforce. Write out, column by column what each field name is in both systems and what data type it is. Pay special attention to your ID fields. If you are importing related objects (say, Accounts and Contacts), there should be a field on the Contact object that has the Account ID. This is often called a “Foreign Key”.

3) Configure Salesforce. Add the custom fields necessary referring to the mapping spreadsheet you wrote. If there are any Foreign Key fields, when setting up the field, use the “External Identifier” option. It is documented here in Salesforce help to learn more: https://help.salesforce.com/apex/HTViewHelpDoc?id=faq_import_general_what_is_an_external.htm&language=en 

4) Clean your data. Make sure number fields only contain number characters. If a field length is 40, make sure you don’t have data that is longer than 40. Is there anything that a simple find and replace will make cleaner before import? If a field is required in Salesforce, make sure there are no blank values in your proposed import.

5) Import to a Sandbox.Time for a test run. I’ll talk about some tools below. FIRST – Review any validation rules or workflow rules on the object you will be importing. Turn them off it makes sense. I once forgot to turn off a workflow rule and imported 22,000 records. This generated 22,000 emails to a customer’s Blackberry and they were not happy about it. Using your preferred import tool, import the data to a Sandbox first. Start with the highest level objects and work your way down. This means start with Account. I generally import in this order: Leads, Account, Contact, Contact Role, Products, Opportunity, Opportunity Contact Role. If you have foreign keys, starting at the top means that when you import the child objects, you can relate them back to the parent.

6) Write it down. I find it very helpful to keep a log of the order of my activities during the test import. Which mapping files am I using, what exact order am I going in? Did I do anything else that will need to be repeated? WRITE EVERYTHING DOWN. If you do, you’ll have a log of your activity, as well as a roadmap for your production import.

7) Verify the Import. Did any of the imports have errors? If you had 147 accounts in your data store, are there 147 accounts in Salesforce? Did everything land in the correct field? Did your relationships work. If an account has 18 contacts tied to it, does it have 18 contacts tied to it in Salesforce?

8) Back up your production data. If you have data in your production instance to back up, back it up now.

9) Run the production import. Hopefully you worked out all the bugs during your trial run. If so, this should be easy. Follow your logged instructions and just repeat all of the steps you already did, and verify the results.

Salesforce Canned Wizards – Simple is sometimes best and the Salesforce Wizards do a great job at facilitating the above process. They are available from Setup > Administration Setup > Data Management > Import XYZ. They offer a step by step process to walk through the import process. Thumbs up for being easy, quick, and easily traininable. If you have a Sales manager who periodically imports lead lists, these wizards are perfect. Another advantage is they try to dedupe for your on import. It’s not perfect but they do use a few algorithms to try and block duplicate entries. Disadvanatage is they are not repeatable, you have to remap the fields every time and they are not very flexible. You get a certain set of fields to map and they are challenging when dealing with more than one relationship. Note – This tool is limited to 50K records.

Apex Data Loader – I consider this application the “Dump Truck” data mover app. It’s ugly, kind of rough to ride in, but gets the job done. I do a majority of my work with the Data Loader, the reason being that is highly customizable and powerful. It allows for easy building of mapping files (.sdl) and outputs verbose error logs if imports fail. It handles joins easily. Thumbs up for being very powerful and fast, and able to handle millions of records. It’s free and there is a download link inside ever Salesforce Org. Thumbs down for being slightly clunky in the UI department. Note – This product requires usage of the Salesforce API, which is only available on Enterprise or Unlimited editions. However, if you are a new Professional implementation, you can request temporary access to the API so your organization can get its data loaded into Salesforce.

CRM Fusion DemandTools – If the Data Loader is a Dump Truck, this application is a fully loaded Peterbuilt with sleeper cab. It’s big, has a lot of options, but takes some training to operate. It has a host of options above and beyond the data loader. There are multiple wizards and very complex data operations are turned into beautiful button click screens. On top of simply importing data, there is bar none my favorite deduping tool, and an update scripter where you can save yourself a lot of importing and exporting to update fields. Thumbs up for being powerful and having a lot of options. Thumbs down for complexity, sometimes the UI is a little tough to understand, and it is also not free (unless you’re a 501(c)3, in which case CRMFusion has a generous donation program).

Thanks everyone for reading the article. So a few questions for comments – Do you have any data manipulation tools that you like? Any data conversion war stories you care to share with us to help everyone else learn and maybe get a few laughs?

-Garry
Note: This blog first appeared on http://buttonclickadmin.com/ ​