How to extract values from json file and write into excel

Help Studio
1

hi,
i have json file below
json text.txt (831 Bytes)

{
“squadName”: “Super hero squad”,
“homeTown”: “Metro City”,
“formed”: 2016,
“secretBase”: “Super tower”,
“active”: true,
“members”: [
{
“name”: “Molecule Man”,
“age”: 29,
“secretIdentity”: “Dan Jukes”,
“powers”: [“Radiation resistance”, “Turning tiny”, “Radiation blast”]
},
{
“name”: “Madame Uppercut”,
“age”: 39,
“secretIdentity”: “Jane Wilson”,
“powers”: [
“Million tonne punch”,
“Damage resistance”,
“Superhuman reflexes”
]
},
{
“name”: “Eternal Flame”,
“age”: 1000000,
“secretIdentity”: “Unknown”,
“powers”: [
“Immortality”,
“Heat Immunity”,
“Inferno”,
“Teleportation”,
“Interdimensional travel”
]
}
]
}

i have to extract all name,age,secretIdentity,powers values
can anyone help ?

2

Hi @anand_kumar4 ,
You can try doing the below steps:

  1. Read the JSON file using Read Text File.
  2. Use Deserialize JSON activity.
  3. Get the members array:
    jsonObject(“members”)
  4. Loop through each member using For Each (JToken).
  5. Extract the values:
    member(“name”).ToString
    member(“age”).ToString
    member(“secretIdentity”).ToString
    String.Join(“,”, member(“powers”).Select(Function(p) p.ToString))

Thanks

3

i am getting error below while reading json array

image
image518×501 26.2 KB

4

@anand_kumar4

Please try this

  1. Read text to a variable str
  2. Deserialize JSON (Not Json Array) and pass str as input and take jobj as output
  3. For loop on Jobj("members")
  4. Inside loop you can get each item you need currentJToken("name").ToString ..for powers as there are multiple you can use string.Join(",", currentJToken("powers"))

Note: CurentJToken name might differ based on your loop variable name

sample

Screenshot 2026-08-06 at 3.37.46 PM
Screenshot 2026-08-06 at 3.37.46 PM1700×1854 244 KB

cheers

5

Hi @anand_kumar4,

You can try this approach:

  • Use Read Text File activity → output jsonString
  • Use Deserialize JSON activity → Input: jsonString, Output: jsonObj (type JObject)
  • Build Data Table → columns: Name, Age, SecretIdentity, Powers (all String, except Age can be Int32 or String)
  • Use For Each row in DirectCast(jsonObj("members"), Newtonsoft.Json.Linq.JArray) and the TypeArgument is JObject
  • Use Add Data Row inside the loop, with array { row("name").ToString(), row("age").ToString(), row("secretIdentity").ToString(), String.Join(", ", row("powers").Select(Function(p) p.ToString())) }
  • Use Write Range and write the DataTable to your target sheet.

Use this workflow directly, this performs the exact flow which is defined above.
Main.xaml (12.7 KB)

Cheers!

6

Thanks @Prashanth_D , @Anil_G