Creating Dynamic JSON on basis of excel data

[Pogboom]

I have this txt file in json format :-

I want the value 1109,A,B,C should come for excel sheet and the values will change everytime so it will run in loop. After that i am using HTTPS request to get some data

String manipulation will work ? or any other approach

[Nitish_Singh]

String manipulation can be a viable approach for extracting specific JSON values, especially when dealing with simple and consistent JSON structures. However, for more complex and dynamic scenarios, utilizing a dedicated JSON parsing approach offers greater robustness and error resilience.

Recommended Approach: Using Deserialize JSON Activity
This method offers flexibility and helps prevent issues that can arise from changing values or formats within the JSON.

• Read JSON File
• Deserialize JSON
• Extract Values
• Write to Excel
• Loop: Wrap this process in a loop if you have multiple entries to process
• HTTP Request: Once the values are saved to Excel, proceed with the HTTP request for further data retrieval.

[SorenB]

Hello @Pogboom

If you want to edit the Json-values with the data from your Excel file, then you can simply use an Assign activity to define the wanted vales.

Eg.:

Assign jobj_JsonObject("priceList")("id") = "1234"

Regards
Soren

[Pogboom]

i want to put data from excel in json there are multiple columns and entries which i need to add in that text file after reading it

[Pogboom]

First i need to read text file then deserialize it and then use multiple assign depending on the data in excel?

[SorenB]

Hello @Pogboom

If you want to edit the values in your text file that is formatted like JSON, then yes.

1. Read text file
2. Deserialize Json
3. For each row in Excel file

If the column name is the same as you json keys, then you could loop these, to save space on the Assign activities.

4. For each dt_ExcelFile.Columns

Assign jobj_JsonObject("priceList")(currentColumnName) = currentDataRow(currentColumnName)

Regards
Soren

[SorenB]

@Pogboom

Please have a look at attched example.
You can download the files below.

Initial Json File

ExcelFile

JsonResult

Download:
Excel to Json.xaml (11.0 KB)
ExcelFile.xlsx (8.1 KB)
jsonfile.json (128 Bytes)

Regards
Soren

[Pogboom]

instead of writing it in txt file can we skip that step and just process updated json directly to https activity because i will again need to read that file and process it further will that be possibile?

[SorenB]

Sure you can do that
Then simply replace the Write Text File with your Http Request activity.

And if you have several lines in your Excel File, and want to execute an Http Request per row, then simply drag the activity below the For Each dt_ExcelData.Columns activity.

Regards
Soren

[Pogboom]

Lets say i have more than 10 fields that needs to be changed i will need to use 10 mutliple assigns? any shorter way to do this?

[SorenB]

Hello @Pogboom

You simply create the columns in you Excel file that matches keys in your json file.

The For Each dt_ExcelData.Columns will read all available columns and try to update the corresponding json key/value.

Regards
Soren

[Chaitanya_Doneparthi]

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!

Best,
Chaitanya