Salesforce Account ID doesn't match across the Salesforce Integration and a Salesforce Export

[katherineladue]

We were planning to use .csv files to import Activity data from Salesforce into HubSpot, and use the Salesforce Account ID in that activity file in order to associate the data to the right companies in HubSpot.

So we imported all company data into HubSpot from the integration (including Account ID), then exported all companies from HubSpot with their HubSpot Record ID so we could import those with the Activity data file as a multi-object, multi-file import of companies and notes. The common column between the two is Salesforce Account ID.

However, the Account IDs that are included in our Activity export file are just slightly different than what the integration has populated in the company records in HubSpot. They're all off by 3 digits at the end - the digits are missing in the export file. 

Has anyone else run into this and if so, how did you get around it? I'm trying to avoid having to completely delete and re-import all companies with the version of the Salesforce Account ID that shows in the export file, but I'm not sure what else we can do since the integration version of the Account ID is different.

Really defeats the purpose of having a unique identifier! ❤️ 

[christopher-RVO · Accepted solution]

@katherineladue  It sounds like you need to some excel manipulation here - the Record ID that you are exporting is likely the HubSpot record ID and not the SFDC Account ID. 

If you export all companies in the CRM with Account ID (Salesforce), and Record ID (HubSpot), then sort from A > Z by SFDC ID on the export AND the activity file, you could combine the sheets and re-import it. 

Ensure that you're mapping to Record ID with your companies in the multi-object import. 

Let me know if you have any questions. 

[CDeveloper · Accepted solution]

Hi! I think you're misunderstanding me. The Salesforce Account ID is different in the export file than what's in our records and what came through the integration, and also different from what's in the actual record URL within Salesforce - and they're all off by 3 letters. 

I'll give you an example: In our export file from Salesforce, Company A has a Salesforce Account ID of "001C000001E3jSq". In HubSpot, the Salesforce Account ID that came through the integration (and what shows in the record URL) is "001C000001E3jSqIAJ". The IAJ on the end is not present in the export file - and this happened to 20,000+ company records.

I think we figured out why though - for some stupid reason, Salesforce has both a 15 character ID and an 18 character ID, and you have to use a Record ID converter script to change them. https://help.salesforce.com/s/articleView?id=000385008&type=1 

[christopher-RVO · Accepted solution]

@katherineladue  It sounds like you need to some excel manipulation here - the Record ID that you are exporting is likely the HubSpot record ID and not the SFDC Account ID. 

If you export all companies in the CRM with Account ID (Salesforce), and Record ID (HubSpot), then sort from A > Z by SFDC ID on the export AND the activity file, you could combine the sheets and re-import it. 

Ensure that you're mapping to Record ID with your companies in the multi-object import. 

Let me know if you have any questions. 

[katherineladue]

Sorry like no hate or anything but this is not the solution to my question which is why I never marked it as the solution. Someone else did over the weekend but just wanted to leave this comment here for the future if anyone references this

[CDeveloper · Accepted solution]

Hi! I think you're misunderstanding me. The Salesforce Account ID is different in the export file than what's in our records and what came through the integration, and also different from what's in the actual record URL within Salesforce - and they're all off by 3 letters. 

I'll give you an example: In our export file from Salesforce, Company A has a Salesforce Account ID of "001C000001E3jSq". In HubSpot, the Salesforce Account ID that came through the integration (and what shows in the record URL) is "001C000001E3jSqIAJ". The IAJ on the end is not present in the export file - and this happened to 20,000+ company records.

I think we figured out why though - for some stupid reason, Salesforce has both a 15 character ID and an 18 character ID, and you have to use a Record ID converter script to change them. https://help.salesforce.com/s/articleView?id=000385008&type=1 

[christopher-RVO]

@CDeveloper  Ahh that makes sense, that is a very strange thing to have to run a script on. Hope my post helped at all LOL!

[katherineladue · Accepted solution]

Sorry I responded from the wrong account! But we ended up actually just using the Excel "text to columns" function to remove the extra 3 characters from the Account ID in the *Company* file, so then it matched what was in the Activity file from Salesforce, and now we have both the 18 and the 15 character Salesforce Account ID in our HubSpot. It worked! 

[Malcolm_Chiu]

Please be note based on the technical article, that you NEED the extra 3 characters to covert the 15 character back to "Case-sensitive" if you haven't done it, there is chance you may have "false" matches the way you are doing it now.

[christopher-RVO]

Oh great! Glad that worked out - if you're feeling generous feel free to mark this as an accepted solution. Happy HubSpotting!

[Malcolm_Chiu]

Hi Katherine, I am an indepedent consultant and has recently done some data syn up work, I am be able to assist if you can provide me with some sample data.  Please feel free to message me back directly.