Converting From SugarCRM to SalesForce

I recently had to complete a migration from SugarCRM to SalesForce and I wanted to share some of my experience here in hopes that you’ll find it useful. These instructions are pretty technical. If you have trouble understanding them then you’re not the right person for the task. Find someone that has experience working with databases.

We wanted to export Accounts, Opportunities, Leads, Contacts, Calls, Notes, Emails, Meetings and Tasks.

In SalesForce add a SugarID custom field and make sure to always populate this with the ID column of what you are importing from Sugar. You’ll need this information to be populated later so that you can relate/connect certain record types to each other.

Add custom fields if Sugar has a field that SalesForce does not. Make sure to make text fields long enough.

Export your data from your SugarCRM database using a database access program such as Aqua Data Studio. Data Studio allows you to run a SQL query, then save the results as an Excel worksheet.

Activate Data Loader. The Data Loader is a program in the AppExchange that allows you to export and import data from SalesForce. If you don’t have an Enterprise account then you can ask your account rep to turn it on for you temporarily. I believe we got 30 days to use it.

When dealing with large Excel files (bigger than 3mb) always open files referenced by VLOOKUP functions in advance of opening the file containing the VLOOKUP function. I’ve found that Excel is much happier if you do this. It can still take a while for it to recalculate the spreadsheet. Just be patient.

Watch out for Excel’s trigger-happy auto-formatting. Any long phone numbers get automatically converted to scientific notation and that’s what goes in the CSV file when you save. Just apply text formatting to the entire column for phone number columns.

Add extra columns in the Excel worksheet to populate information in the format the Data Loader expects. For example the date needs to be formatted the right way for the Data Loader to import it correctly. You can reformat a date in Excel by using ‘=DATE(B2, “YYYY-MM-DD HH:MM:SS”)’.

Add an OwnerID column. This is the ID of a user in SalesForce. To find out what a user’s ID is go to Setup, Users, Manage Users and click on a user. In the address bar you’ll see the user ID like ’12345678901UsTG’; populate that in the OwnerID column. If you have multiple users make a spreadsheet to map these values and use VLOOKUP to look up the value as explained below.

To relate records to each other you must have a column in your data that contains the SalesForce ID of the related record. For example, when importing Contacts the AccountID column must contain the SalesForce ID of the related Account. One way to populate this in all your records is to use the VLOOKUP function in Excel. First import your Accounts, remembering to populate Sugar ID. Then use the Data Loader to export the SalesForce ID and Sugar ID of all your Account records to accounts.csv. Open accounts.csv in Excel. Make the Sugar ID the first column and the SalesForce ID the second column and sort the records by Sugar ID (important). In the Contacts spreadsheet add a column named ACCOUNTID1 with a value like ‘=VLOOKUP($I2,’[accounts.csv]accounts’!$A$1:$B$1000, 2,FALSE)’. If an ID can’t be found for a record then #N/A will be displayed and Data Loader will not import the record. Add another column named ACCOUNTID with value ‘=IF(ISNA(K2), “”, K2)’ where K2 is the cell with the VLOOKUP. Finally save your worksheet first as an Excel file, then as a CSV file.

In Data Loader select File, Insert, then follow the wizard to import your data. If there are errors open the CSV file with the errors, fix the problems and try to import the fixed CSV file.

All of the above listed data records can be imported using the same mechanism. We found that the types translated from Sugar to SalesForce the following way:

  • Call > Task
    Set the Status for future tasks to Not Started. Set the Status for past calls to Completed.
  • Note > Note
  • Email > Note
    In this case I prepended From, To and Subject to the Body and I also prepended “Email <DATE> ” to the subject so that you could glance at the note and see that it’s an email and when it was sent.
  • Meeting > Event

If you do mess up you can use Upsert to update/insert and avoid creating duplicate records.

Let me know if you have any questions.

4 responses

  1. sarel,

    this is an excellent post – thanks for documenting your work. I’m doing the same type of conversion now (not that many companies have moved from Sugar to SF…). 2 questions if you don’t mind:
    1. now that it’s been a few weeks, what have users commented on? formatting of notes held up ok?

    2. when you imported email history and notes, did the data loader preserve the modified date of the note or did everything come over as the date you imported?

    thanks in advance.

    -jeremy

  2. Great information… I’m going through a conversion from Sugar to SFDC right now and I’m frustrated with the lack of documentation from SFDC on the data migration. Did you also migrate attachments? In Sugar the attachments are stored as a flat file and they range from a few K to full 5M documents. I think some of them I don’t care about as they’re probably e-mail signatures of Logos, but I would need to reformat to the native file type to determine that. Also, It’s not clear how to map the attachments to new cases in SFDC.

  3. Jeremy,

    I’m sorry, but we did not have any attachments to convert over. I have no idea if there’s a way to do that with the tool or not. If not, you can probably use the SF API to do it. Of course, you would need to know how to program a little.

    Sarel

  4. Bob,

    I’m sorry, but we did not have any attachments to convert over. I have no idea if there’s a way to do that with the tool or not. If not, you can probably use the SF API to do it. Of course, you would need to know how to program a little.

    Sarel

Leave a Reply