Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Migration from Salesforce to SugarCRM

  1. #1
    swarrick is offline Member
    Join Date
    Mar 2008
    Posts
    9

    Default Migration from Salesforce to SugarCRM

    I am currently looking at migrating from a mature Salesforce installation which has been growing with data for at least 2 years to using SugarCRM hosted on our own servers.

    The reasons for this are many, but at the root of it, we need more cost efficient control of the use of our CRM system and SugarCRM looks to be a good match for our needs.

    I am now failing at the first hurdle.

    Migrating Salesforce Accounts to SugarCRM 5.0 is failing on import.

    I've discovered that the ID fields from Salesforce are causing a problem when imported to SugarCRM as SugarCRM thinks there are duplicate IDs when clearly there are not. The problem seems to be that the ID fields are case insensitive in SugarCRM whereas they are case sensitive in Salesforce.

    This is causing many of the account records to fail to load and I've so far been unable to find a way to resolve this. Has anyone else had similar issues and found a resolution?

  2. #2
    JVWay is offline Sugar Community Member
    Join Date
    Sep 2007
    Location
    Corvallis, Oregon
    Posts
    452

    Default Re: Migration from Salesforce to SugarCRM

    I just completed a Salesforce so Sugar migration. The only problem I had was account names that had apostrophes in the name when importing contacts.

    By ID field are you somehow trying to push the salesforce record ID into the Sugar ID field? Sugar will create its own ID's. To try in put salesforce record ID's (database record ID's) is not something you want to do. Just put account name to the account field and other needed data and you should be fine.

    JW
    Jerry Way
    Business Process Administrator

    Sugar 6.1.4 Professional
    (Testing 6.1.2)
    LAMP on Centos 5
    PHP 5
    MySQL 5
    Apache 2.2

  3. #3
    swarrick is offline Member
    Join Date
    Mar 2008
    Posts
    9

    Default Re: Migration from Salesforce to SugarCRM

    That is precisely what I am trying to do...

    Maybe I am misunderstanding the purpose of the ID field...?

    My assumption was that this is the unique reference for an account which will be the same reference used to link contacts to the accounts etc.

    Within my list of accounts there are several non-unique account names which are only differentiated by location being different for different offices of the same company.

    From what you are suggesting - the Name field is (should be) unique to link it to the contacts, contracts and so on. I had assumed that the ID field was used for that purpose. May be I am wrong?

    As an aside, I am surprised to find an index on an ID field is designed to be case insensitive but maybe that is just something I am not used to and there is some good reason for that, which I am not yet aware of. I didn't realise until today that MySQL operates that way by default which seems dangerous to me.

    Thanks

    Simon

  4. #4
    JVWay is offline Sugar Community Member
    Join Date
    Sep 2007
    Location
    Corvallis, Oregon
    Posts
    452

    Default Re: Migration from Salesforce to SugarCRM

    I'm assuming you're using the sugar interface to import. If so it will create the ID when you import. It will require unique account names so you'll have to append something to the account name like a city. I think I've read about people inserting into MySQL directly and then you can create your own ID. But new records will use the Sugar GUID concept.

    Here's what I did. I cleaned up all my Salesforce data. There were some duplicate account names. We changed the to reflect location. I used Sugar to import them. The contacts report I created from Salesforce had the account name in there. I imported them. Did the same with leads. The Opportunity import is problematic. It's most picky about the date format. Enter a test record in Sugar. Look directly at a MySQL query to determine date format in the database. This is critical. An ODBC connection will sometimes localize the date format. Cost me a couple of days figuring this out.

    If you've got parent/child relationships on your accounts you'll need to make separate data sets. Import the parents. Export and relate your records in the next data set to that you have a field with the parent ID tied to the children. Then import that set but when you do this you'll need to map the parent ID field in your sugar import. So, it'll be the account ID field mapped to the parent_id field in Sugar.

    Hope this helps,
    JW
    Jerry Way
    Business Process Administrator

    Sugar 6.1.4 Professional
    (Testing 6.1.2)
    LAMP on Centos 5
    PHP 5
    MySQL 5
    Apache 2.2

  5. #5
    swarrick is offline Member
    Join Date
    Mar 2008
    Posts
    9

    Default Re: Migration from Salesforce to SugarCRM

    Good to learn from your experience. I'll give those ideas a try tomorrow and publish back here what works for anyone else following in these footprints...

    Thanks very much

    Simon

  6. #6
    swarrick is offline Member
    Join Date
    Mar 2008
    Posts
    9

    Default Re: Migration from Salesforce to SugarCRM

    Hello Jerry,

    I have tried to do what you suggest.

    I exported all the account information and ensured that I have unique account names.

    I also exported all the contacts roughly double the number of contacts to accounts which was as expected and included in this the account name.

    I imported all the accounts with no issues.

    I then imported the contacts without problem.

    However NONE of the accounts are connected to the contacts and vice versa...

    So it would appear that the connection between account and contact isn't maintained when importing in this way.

    How did you get around this?

  7. #7
    JVWay is offline Sugar Community Member
    Join Date
    Sep 2007
    Location
    Corvallis, Oregon
    Posts
    452

    Default Re: Migration from Salesforce to SugarCRM

    Hmm,
    I had no problems doing that.
    Checklist:
    You were doing it through the Sugar interface.
    Each contact record had the account name it's associated with in it.
    When you imported the contacts you mapped the account name at that time as well.

    That's all I did so it's hard to imagine why it didn't work if those things were followed.

    Let me know what else might be of consideration. What exact version of Sugar are you using. I did all my imports on 5.0a.

    JW
    Jerry Way
    Business Process Administrator

    Sugar 6.1.4 Professional
    (Testing 6.1.2)
    LAMP on Centos 5
    PHP 5
    MySQL 5
    Apache 2.2

  8. #8
    swarrick is offline Member
    Join Date
    Mar 2008
    Posts
    9

    Default Re: Migration from Salesforce to SugarCRM

    Hello Jerry,

    Thanks for your patience.

    I re-visited it again from first principles, sorted all the data in the CSV' files and checked through "by hand" that I could map an account to a contact via the account name and it was then I spotted something in what you had said.

    The very first entry in the contact list was for an account with an apostrophe in the name.... and this caused everything else to fail to import which totally threw me.

    Inadvertently the second time I tried, I'd missed the account name off the mapped fields list (doh) which allowed everything to be imported but failed to connect any contacts with an account.. so all I could see was a binary it either failed completely or doesn't connect anything together. My mistake on the latter which I didn't spot until I loaded the saved mapping for a second time.

    I'm now re-writing all names with apostrophes in to some other character which we can replace later. There are a lot of these. This should be logged as a "bug" somewhere I guess for data import. I'll apply the same principle everywhere for apostrophes and hope it doesn't trip me up again.

    I'm not too concerned about "opportunities" as we're going to review all of them anyway and will probably do them manually from a report.

    All the best and nearly there

    Simon

  9. #9
    kuske's Avatar
    kuske is offline Sugar Community Member
    Join Date
    Oct 2007
    Location
    Germany
    Posts
    2,678

    Default Re: Migration from Salesforce to SugarCRM

    SalesForce has old 15 chars long case sensitive IDs and since 2005 they have 18 chars long case insensitive IDs.
    For Sugar you can only take these 18 chars longs IDs.
    They are exported in the weekly data export of SalesForce or at the API interface.
    In SalesForce API refernce there is a sentence
    "If you need information on the algorithm used to convert 15.character IDs to 18-character IDs, please contact Customer Support."
    ... or you can google for it and find http://www.logixbiz.com/blog/index.p...force-Ids.html
    Last edited by kuske; 2008-03-11 at 04:58 PM.

  10. #10
    kuske's Avatar
    kuske is offline Sugar Community Member
    Join Date
    Oct 2007
    Location
    Germany
    Posts
    2,678

    Default Re: Migration from Salesforce to SugarCRM

    To convert SalesForce char15 Ids to char18 Ids you can use the folowing mySQL stored function:

    Code:
    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `sugarcrm`.`sf_id15_id18` $$
    CREATE FUNCTION `sf_id15_id18`(char15id varchar(18)) 
    RETURNS varchar(18) CHARSET utf8
    BEGIN
    
    
    declare i integer;
    declare idx integer;
    declare inpos integer;
    declare part varchar(5);
    declare suff varchar(3);
    
    
    set suff = '';
    set inpos = 1;
    
    repeat
       set part = substr(char15id,inpos,5);
       set i = 5;
       set idx = 0;
    
       repeat
          if ord(substr(part,i,1)) <> ord(lower(substr(part,i,1)))
          then set idx = idx * 2 + 1;
          else set idx = idx * 2;
          end if;
          set i = i - 1;
       until i=0 end repeat;
    
       set inpos = inpos + 5;
       set suff = concat(suff,
            substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345',idx+1,1));
    
    until inpos>=16 end repeat;
    
    return (concat(substr(char15id,1,15),suff));
    
    END $$
    
    DELIMITER ;
    Last edited by kuske; 2008-03-11 at 07:43 PM.

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Email IMAP SSL issue ( with error log )
    By clearlink in forum Help
    Replies: 16
    Last Post: 2009-11-16, 01:50 AM
  2. Enable Logging
    By vidtechsteve in forum Help
    Replies: 12
    Last Post: 2009-11-09, 02:54 PM
  3. Replies: 5
    Last Post: 2007-11-13, 02:51 PM
  4. Salesforce for 1 year then migrate to SugarCRM?
    By Stace in forum General Discussion
    Replies: 7
    Last Post: 2007-03-06, 04:15 PM
  5. Replies: 0
    Last Post: 2006-01-04, 11:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •