CSV to JSON for Bulk Import / Update

[88atwilliams]

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.

[Sapna_Girimalla]

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.

[88atwilliams]

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

[Sapna_Girimalla]

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

[Derek_Gervais]

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:

npm

csvtojson

A tool concentrating on converting csv data to JSON with customised parser supporting

stackoverflow.com

directly convert CSV file to JSON file using the Jackson library

java, json, csv, jackson

asked by Daniel Ruf on 10:58AM - 04 Nov 13

[88atwilliams]

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]

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.