Migrate Sugarcrm Data To Salesforce
The following post will describe how to migrate all Accounts, Contacts, Tasks and Notes from SugarCRM to SalesForce whilst maintaining their relationships with one another. More specifically; preparing the .csv import files for importing into the Jitterbit data loader.
Step 1. Create a custom field against the Account object
Name the custom field ‘sugarId’. This field is very important and will be used to hold a text value which is the SugarCRM ID for the Account, and will allow the Account to retain its relationship with Contacts, Notes and Tasks.
Step 2. Export all Accounts from SugarCRM
The following SQL script will pull the Accounts data from the SugarCRM database, and will also perform any data manipulation/transformation that may be required to match up data with relevant Account custom fields that may exist.
At this point, it is worth mentioning that the most important part of the migration process is making sure that the data is correctly formatted and relevant. This is a good opportunity to also disregard any unnecessary or old data that doesn’t need to be migrated.
select 'SugarId__c', 'Name', 'Website', 'Type', 'Description', 'Phone', 'Incumbent__c', 'Lead_Status__c', 'Courier__c', 'Interested_In__c', 'Campaign__c', 'E_commerce_Platforms__c' union all select a.id SugarId__c, ifnull(a.name, '') Name, ifnull(a.website, '') Website, ifnull(a.account_type, '') Type, ifnull(replace(a.description,'"',''''), '') Description, ifnull(a.phone_office, '') Phone, ifnull(ac.incumbent_c, '') Incumbunt__c, ifnull(ac.lead_status_c, '') Lead_Status__c, ifnull( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace(ac.courier2_c, '^',''), ',',';'), 'ups', 'UPS'), 'tnt', 'TNT'), 'dhl', 'DHL'), 'apc', 'APC'), 'usps', 'USPS'), 'yodel', 'Yodel'), 'ukmail', 'UK Mail'), 'geopost', 'GeoPost (DPD)'), 'hermes', 'Hermes'), 'metapack', 'MetaPack'), 'parcel_force', 'Parcel Force'), 'interlink_express', 'Interlink Express'), 'royal_mail', 'Royal Mail'), '') Courier__c, ifnull( replace( replace( replace( replace( replace( replace( replace( replace(ac.interested_in_c, '^',''), ',',';'), 'linnworks','Linnworks'), 'ebay','Ebay'), 'amazon','Amazon'), 'openerp','Open ERP'), 'sap','SAP'), 'sage','Sage'), '') Interested_In__c, ifnull(ac.campaign_c, '') Campaign__c, ifnull( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace(ac.cart2_c, '^',''), ',',';'), 'woocommerce','WooCommerce'), 'ebay','EBay'), 'actinic','Actinic'), 'visualsoft','Visual Soft'), 'erol','EROL'), 'retail_store','Retail Store'), 'inhouse_development','Inhouse Development'), 'shopify','Shopify'), 'magento','Magento'), '') E_commerce_Platforms__c from accounts a left join accounts_cstm ac on ac.id_c = a.id where a.deleted = false into outfile '/var/lib/mysql-files/Accounts.csv' fields terminated by ',' optionally enclosed by '"' escaped by '' lines terminated by '\n';
Step 3. Import the Accounts.csv file into SalesForce using the Jitterbit data loader.
Step 4. Export the Accounts 'id' and 'sugarId_c' values from SalesForce using the Jitterbit data loader.
We now have the SalesForce and SugarCRM ID’s for each Accounts.
Step 5. Import the Accounts IDs into the SugarCRM database
Create a temporary table in the SugarCRM database.
create table accounts_mapping ( sugarId varchar(255), salesForceId varchar(255) );
Import the .csv file containing the Accounts ID mappings.
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/Accounts_id_mapping.csv' INTO TABLE accounts_mapping FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" (salesForceId,sugarId);
Note that you may need to enable to LOAD DATA. This can be done using the command line flag ”--local-infile". Also, you may need to remove a carriage return that may have been added to the SugarCRM ID values. You can do this with the following query: update accounts_mapping set sugarId = replace(sugarId, '\r','');
Step 6. Export SugarCRM Tasks using a join on the temporary table to retain the relationship with the Account
select 'Status', 'Description', 'Priority', 'Subject', 'WHATID' union all select 'Completed' Status, ifnull(t.description,'') Description, t.priority Priority, ifnull(t.name,' ') Subject, am.salesForceId WHATID from tasks t join accounts_mapping am on t.parent_id = am.sugarId where deleted = false into outfile '/var/lib/mysql-files/Tasks.csv' fields terminated by ',' OPTIONALLY enclosed by '"' lines terminated by '\n';
Step 7. Import the Tasks into SalesForce using the Jitterbit data loader.
Step 8. Export SugarCRM Notes using a join on the temporary table to retain the relationship with the Account
select 'Body', 'isPrivate', 'OwnerId', 'ParentId', 'Title' union all select ifnull(replace(n.description,'"',''''), 'n/a') Body, 0 isPrivate, '0050Y000001SnN2QAK' OwnerId, am.salesForceId WHATID, ifnull(n.name, 'n/a') Title from notes n join accounts_mapping am on n.parent_id = am.sugarId where n.deleted = false into outfile '/var/lib/mysql-files/Notes.csv' fields terminated by ',' optionally enclosed by '"' escaped by '' lines terminated by '\n';
Note that in the example above I have hard coded the SalesForce User ID. But you could also map users in a similar way to how we have already mapped the Account IDs. So you can retain the correct user/author for the Notes.
Step 9. Import the Notes into SalesForce using the Jitterbit data loader.
The SugarCRM Note object refers to the SalesForce Notes & Attachments object in SalesForce (not the ContentNote object) which is not displayed on the Accounts page by default if you are using the new “Lightning Experience” theme. You can add the “Notes & Attachments” section to the Accounts page from the Setup > Page Layout settings.
Step 10. Export SugarCRM Contacts using a join on the temporary table to retain the relationship with the Account
select 'Title', 'Department', 'Email', 'MobilePhone', 'Phone', 'Salutation', 'FirstName', 'LastName', 'MailingStreet', 'MailingCity','MailingState','MailingPostalCode','MailingCountry', 'AccountId' union all select ifnull(c.title, 'n/a') Title, ifnull(c.department, 'n/a') Department, ifnull(e.email_address, '') Email, ifnull(c.phone_mobile, 'n/a') MobilePhone, ifnull(c.phone_work, 'n/a') Phone, ifnull(c.salutation, 'n/a') Salutation, ifnull(c.first_name, 'n/a') FirstName, ifnull(c.last_name, 'n/a') LastName, ifnull(c.primary_address_street, 'n/a') MailingStreet, ifnull(c.primary_address_city, 'n/a') MailingCity, ifnull(c.primary_address_state, 'n/a') MailingState, ifnull(c.primary_address_postalcode, 'n/a') MailingPostalCode, ifnull(c.primary_address_country, 'n/a') MailingCountry, ifnull(am.salesForceId, '') AccountId from contacts c left join email_addr_bean_rel er on er.bean_id = c.id left join email_addresses e on e.id = er.email_address_id left join accounts_contacts ac on ac.contact_id = c.id and ac.deleted = false left join accounts a on ac.account_id = a.id and a.deleted = false left join accounts_mapping am on a.id = am.sugarId where c.deleted = false into outfile '/var/lib/mysql-files/Contacts.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';