JSON in excel to push into database

Help Robot
,
1

Hello All,

I have an excel sheet with the data in json format how can i push it into database?

Kindly share your thoughts

2

@Anusuya_Ravi

Read the data and use insert query

you can use bulk insert as well

cheers

3

Hi Anil,

My question is how to read json data in excel and store them as datatable. Below is my data in excel, hope you got it now.

image
a

5

@Anusuya_Ravi

As mentioned above you can deserialize the json and then use jobj(“Key”).ToString to get the specific value and store using add data row

if it is a json array then in deserialize json activity change type argument to datatable then it would automatically load to datatable

cheers

6

I am getting an error while deseriaize JSON

Excel JSON Data :{“First Name”:“Stan”,"Last Name ":“Hamm”,“Company Name”:“Sugarwell”}

I’m reading this data and convert into string to pass into Deserialize JSON

Output data table output : “{”“First Name”“:”“Stan”“,”“Last Name “”:”“Hamm”“,”“Company Name”“:”“Sugarwell”“}”

So you could see while coverting datatable into txt there is some extra “” getting added.

So JSON activity is throwing this error - Deserialize JSON: Deserialized JSON type ‘Newtonsoft.Json.Linq.JValue’ is not compatible with expected type ‘Newtonsoft.Json.Linq.JObject’. Path ‘’, line 1, position 3.

image

7

@Anusuya_Ravi

Can you say the error please…those are the escape characters I believe…They are not extra quotes but for formatting it shows like that

cheers

8

@Anil_G

image

9

@Anusuya_Ravi After you have successfully read the “Excel” data , using “read cell” or “read range” ; I believe you should also try “read cell” ; perhaps the data is in cell “A1”.

  • You should be able to convert the “jsonResult” obtained after “deserialize” activity using below approach , directly into “datatable”.

    image
    image1251×328 17.7 KB

  • Now , Iterate “datatable” using “for each row” and call “insert” or “update” query whichever is applicable as per requirement.

  • Following is the code to convert the “JsonResult” to “Datatable” JsonConvert.DeserializeObject(Of DataTable)(jsonResult(“value”).ToString)

Let me know if this helps.

10

Perfect… This solution works well… Thank you

11

But how about the input JSON data in excel on below format, what would be the solution for this

image

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.