SCDIT Blog

There are some good automated service to match up the fields and move the data over; however, we have found that the data isn’t always entered correctly and needs to be manually evaluated and corrected before importing.

We did a full export form Salesforce and we got 140 separate .csv files. The ones were really concerned with moving were the following:

• Accounts
• Attachment
• Contact
• Email Message
• Entity Subscription
• Event Relation
• Feed Post
• Feed Tracked Change
• File Search Activity
• Lead
• News Feed
• Note
• Opportunity
• Opportunity Contact Role
• Opportunity History
• Task
• Task Relation
• User

These were the .csv files that had content in them. We started by opening the Contacts and reviewing the data and how the tables linked. The field “AccountId” links to the field “Id” in the “Accounts” .csv file. We reduced the number of contacts down to just 10 users and ran through the import process.

Once logged into Hubspot, in the top menu go to “Contacts >> Companies“. Next click on the “Import” button.

Go to “Start an import“.

Select “File from computer“.

Select “Companies“.

Upload your file. Since we were coming from SalesForce we did not check “Update existing companies using “Company ID” exported from HubSpot.

In SalesForce the Company has a unique id column called “ID“. This matches up with “Accounts ID” in the accounts table. While importing the Hubspot calls this column “Company ID“. The import wasn’t allowing us to pick so we created it. Seemed scarey at first but worked fine.

This import didn’t go as planned. The company was automatically created, but it didn’t link it to the contact. In addition, when reviewing the small import I discovered it put the the company in City that didn’t exist in that State. I have no idea where this data came from.

After some research I discovered this feature of importing two different .CSV’s files such as contacts and companies and matching a field to link the data. It is still in Beta. Here is a link to Hubspot’s how-to.

knowledge.hubspot.com/articles/kcs_article/contacts/associate-records-via-import

Found Another Issue

I tried a small import; however, nothing matched between the “ID” in “Accounts” export and “AccountId” in “Contacts“. I discovered the first 10 items I tried to import didn’t have a matching account with a matching contact.

I used Excel to find how many don’t match and create a new CSV with just the ones that matched.

I copied the Accounts sheet to the Contacts sheet by using the following method.

1. Right-Click on the tab of the sheet I want to copy and select “Move or Copy“.
2. Use the dropdown arrow to select the other workbook.
3. Select where you want it to go. I didn’t care so I choose “(move to end)“.
4. Make sure to check “Create a copy” or it will move the sheet out of the workbook.

The next step I used the help from this YouTube video to do the Match calculation.

Here is the calculation I ended up using.

=ISNUMBER(MATCH(E2,Account!$A$2:$A$49564,0))

I took those results and filtered them by going to Data >> Filter. I could then select just the matched results and export them.

This next article showed me how to just export the visible once I filtered my view to just the “True” or matched items.

https://www.extendoffice.com/documents/excel/3303-excel-save-filtered-data-to-csv.html

We then reduced both files down to just 15 matched rows to test. We had to make a common field to make the association so we changed “Id” in the “Accounts” to be “AccountId” like it is in the “Contacts“.

We logged into hubspot and started the import process but this time chose “Multiple objects with Associations“.

Selected “Contacts” and “Companies” and hit “Next“.

Drag and drop your .CSV files.

Next we had to associate the matching columns between the .CSVs and assign a “Unique Key“. Since this ID was originally the Accounts ID we associated it with “Company“.

When we got to the import screen for Account or Company the AccountID was asssociated with any hubspot property, so we created one.

Click “Create a new property“.

Then give it a name. We did “AccountID” so it would match.

We checked “Don’t import data in unmatched columns“.

This worked for the most part; however, if the address wasn’t in the “Accounts.csv”,  Hubspot automatically created the address which in some cases was way off. It had a business in Washington DC with an address in Chicago.

!IMPORTANT:
When importing an export .CSV I found it necessary to open the .CSV, copy everything, and paste it back into a new Excel sheet and save it as a .CSV. If I didn’t the import would crash or time out. I would get error #2006 - MySQL server has gone away


The large .CSV imports into MySQL through PHPmyAdmin were failing. I moved over to MySQL workbench and this worked quicker; however, it only imported 894 of 48,000 records. The is next article got me on the path to use “HeidiSQL“. Heidi knocked out the import in 5 seconds.

https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table

Steps for getting the MySQL Tables Ready for combining:

1. I took a working table and copied it with “Add AUTO_INCREMENT value”.
   
2. Adding an PRIMARY_KEY speeds up the query process to I needed to change the “AccountId” I was joining on. I went into “Structure”, checked the row I wanted to change and hit “change”.  I had to change the “AccountId” to a “varchar” and added the “value” of “255” and hit “Save”.
   
3.  I went into “Structure”, checked the row I wanted to change, scroll to the bottom and hit the “Primary” button.
   
4. This above method did not work.

I used the MySQL Workbench and “altered” the table and put primary keys and an index on the tables.

1. In the WorkBench click on the “wrench” icon.
   
2. I added a “Primary Key” and hit “apply”, but here I changed the “Alter” query to be add “index”.
   I get the following screen.
   
3. I changed the query so it was adding an “Index” and not a “Primary Key”. The new query looked like this “ALTER TABLE blog ADD INDEX(blogID);”
   

With this completed the queries would no longer time out and happened in seconds.

I ended up having to use SQL to join the data. I used the following query.

This is working:

UPDATE accounts AS a
INNER JOIN contacts AS c ON a.AccountId = c.AccountId
SET a.BillingStreet = c.MailingStreet,
a.BillingCity = c.MailingCity,
a.BillingState = c.MailingState,
a.BillingPostalCode = c.MailingPostalCode
WHERE a.BillingStreet = “”

This article was a huge help.

https://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables

The next step I used MySQL Workbench to export the table to a CSV.  I right-clicked on the Table name and selected “Table Data Export Wizard” and saved the file as a .CSV.  I then took this CSV files for Contacts and Accounts and went through the process of uploading multiple files in Hubspot.

There were a few errors but they were only related to the “Industry” field where it was trying to add a industry listed from Salesforce that didn’t exist in Hubspot. Not a big deal for us since we were only after the contact and companies.

Let us know if this helped you…