How to update json body in particular value

[Raja.G (Mr.RPABot)]

Hi Team,

I have json file i want to update particular value, Input account no in excel i should pass to json body
“AccountNo” to replace “22222”,“88556”,“88955”,“485844”

Input excel:

json below,
{
“data”:{
“XXX” : “hhhh”,
“yyyy” : “OOO”,
“Accounts”:

     "AccountNo"

}
}

expected output:
{
“data”:{
“XXX” : “hhhh”,
“yyyy” : “OOO”,
“Accounts”:

     "22222",
    "88556",
   "88955",
  "485844"

}
}

Please anyone please help me for this

Regards,
Raja G

[Raja.G (Mr.RPABot)]

Hi Team,

Please anyone help me for this.

Regards,
Raja G

[supermanPunch (Arpan)]

Hi @Raja.G ,

Could you try the below Steps :

1. Read the Json Data using Read Text file activity and deserialize it using Deserialize Json activity to a Json Object.
   

   

2. Next, we can Read the Excel sheet data and convert the Account No column values into an Array of String like below :

accountNoValues = DT.AsEnumerable.Select(Function(x)x("Account no").ToString).ToArray

Here, DT is the datatable read from the Excel sheet and accountNoValues is an Array of String type variable.

3. Next, we can prepare the JArray values like below :

jArr = new JArray(accountNoValues)

For Simplicity, I have used direct array values :

4. At last we can assign the JArray values to the JObject as shown below :

jObj("data")("Accounts") = jArr

Output :

Note: The Output representation provided by you doesn’t include Array Brackets [ ], which would make the Json an Invalid one.

Maybe we can also try a Shortcut method, by replacing the "AccountNo" after reading the text file itself with the Array value like below :

strText = strText.Replace("""AccountNo""",Newtonsoft.Json.JsonConvert.SerializeObject(accountNoValues))

Here, strText is a string type which contains the text data read from a Json text file, accountNoValues is an Array of String type variable which contains the account no. values read from Excel sheet as provided in the above steps. In the image below I have used direct array for simplicity.

You should then be able to deserialize it using Deserialize Json Activity.

[Raja.G (Mr.RPABot)]

Hi @supermanPunch ,

I want to pass data table to add collection that list i need to pass that accountno

Account no is 50 or above those things i need to pass

For Simplicity, I have used direct array values :
Assign
JArr=new Jarray({“2222”,“88556”)}— so how to pass list

Regards,
Raja G

[supermanPunch (Arpan)]

@Raja.G

JArr=new Jarray({“2222”,“88556”)}— so how to pass list

@supermanPunch

1. Next, we can Read the Excel sheet data and convert the Account No column values into an Array of String like below :

accountNoValues = DT.AsEnumerable.Select(Function(x)x("Account no").ToString).ToArray

Here, DT is the datatable read from the Excel sheet and accountNoValues is an Array of String type variable.

@Raja.G ,
Already Mentioned in Step 2. We are assuming that you are already reading the Excel sheet as a Datatable using Read Range activity.

[Raja.G (Mr.RPABot)]

Hi @supermanPunch ,

Working fine thank you