APIs & Integrations

88atwilliams
Member

CSV to JSON for Bulk Import / Update

Hey Guys, I'm trying to use the bulk update API to update and create new contacts.
How could you format a csv file and convert it to this JSON format?

axios.post('https://api.hubapi.com/contacts/v1/contact/batch/?hapikey=;) )',
[{
"email": "abby88@gmail.com",
"properties": [{
"property": "firstname",
"value": "Abigail"
},
{
"property": "lastname",
"value": "Williams"
}
]
},

I've tried working backward from the example but I can't seem to figure out the nested value of properties.

0 Upvotes
6 Replies 6
Sapna_Girimalla
Member

CSV to JSON for Bulk Import / Update

Is there a way to convert the JSON response from hubspot to csv. I see that the json response is nested and has key value pairs. Any suggestions to convert it to csv? Thanks.

0 Upvotes
88atwilliams
Member

CSV to JSON for Bulk Import / Update

Hey Sapana, I would save your JSON response to a MongoDB Collection (I would use Mongoose to make it easier). Then, if you use MongoDB Compass Beta (Mongo GUI) - you can export a collection to CSV.

Best of Luck

0 Upvotes
Sapna_Girimalla
Member

CSV to JSON for Bulk Import / Update

Thanks for the suggestion William. Let me check if I can make this work.

0 Upvotes
Derek_Gervais
HubSpot Alumni
HubSpot Alumni

CSV to JSON for Bulk Import / Update

Hi @88atwilliams,

Glad to hear you got this working! Depending on the language you're working in, there are some libraries that help abstract some of these details away:

0 Upvotes
88atwilliams
Member

CSV to JSON for Bulk Import / Update

The solution we found is ugly but it works. We created a formula in excel that put everything we needed in one cell. Then could copy and paste it as valid JSON directly in our code base.

Here is the formula we used. This is how we connected contacts by email that aren't name@company.com.

=CONCATENATE("[",CHAR(10),"{",CHAR(10),"""email"": """,#REF!,""",",CHAR(10),"""properties"": [",CHAR(10),"{",CHAR(10),"""property"": ""associatedCompanyID"",",CHAR(10),"""value"": """,A3772,"""",CHAR(10),"}",CHAR(10),"]",CHAR(10),"}",CHAR(10)),IF(#REF!=250,CONCATENATE(B3771,",",CHAR(10),"{",CHAR(10),"""email"": """,#REF!,""",",CHAR(10),"""properties"": [",CHAR(10),"{",CHAR(10),"""property"": ""associatedCompanyID"",",CHAR(10),"""value"": """,A3772,"""",CHAR(10),"}",CHAR(10),"]",CHAR(10),"}",CHAR(10),"]"),CONCATENATE(B3771,",",CHAR(10),"{",CHAR(10),"""email"": """,#REF!,""",",CHAR(10),"""properties"": [",CHAR(10),"{",CHAR(10),"""property"": ""associatedCompanyID"",",CHAR(10),"""value"": """,A3772,"""",CHAR(10),"}",CHAR(10),"]",CHAR(10),"}",CHAR(10)))).

Best of luck to anyone else who runs into this problem.

88atwilliams
Member

CSV to JSON for Bulk Import / Update

Another way to ask this question is how to flatten the JSON to make it work? I can get a 202 with a flat JSON file but the contacts still wont update.

0 Upvotes