Creating Dynamic JSON on basis of excel data

Help Activities
, , ,
12

Hey @Pogboom,

Yes, you can achieve this by creating a dynamic JSON string based on Excel data. Here’s a solution using UiPath that leverages string manipulation along with reading Excel data.

Step-by-Step Solution:

  1. Read Excel Data:

    • Use the Read Range activity to read your Excel sheet into a DataTable (let’s call it dtData).

  2. Loop Through Excel Data:

    • Use a For Each Row in DataTable activity to iterate through each row of dtData.

  3. Construct JSON String:

    • Inside the loop, use an Assign activity to build a JSON string dynamically. For example:
      jsonString = $"{{""priceList"":{{""id"":""{row("ID").ToString}"",""currency"":""{row("Currency").ToString}"",""name"":""{row("Name").ToString}"",""description"":""{row("Description").ToString}""}}}}"

  4. Send HTTP Request:

    • Use the HTTP Request activity to send the constructed JSON as part of your request body.

Example JSON Output:

{
  "priceList": {
    "id": "1109",
    "currency": "A",
    "name": "B",
    "description": "C"
  }
}

Additional Tips:

  • If the JSON structure is more complex, consider using the Newtonsoft.Json package to serialize your DataTable rows into JSON objects.
  • You can also use Build Data Table activity for quick testing.

Please mark this as a solution if it helps! :blush:

Best,
Chaitanya