Converting Cell value

Help Activities
, , , , , ,
1

Hi

Is there any way to convert this cell value from - “[
{
““value””: ““Waterproof””,
““label””: ““Waterproof””
}
]”

in excel to “Waterproof” and I want to do it in Bulk for each row in one go.

Regards
Naman

1 Like
2

Hey @Naman_Arora
I think the easiest way will be:

  1. Read Range to DataTable from Excel file.
  2. For Each Row in DataTable:
  • Assign (Deserialize JSON from the cell and extract the desired part).
  • Assign (Update the cell in the DataRow).
    JObject.Parse(currentRow(“YourColumnName”).ToString)(“value”).ToString()
  1. Write Range with the updated DataTable to Excel.
1 Like
3

@Naman_Arora
For Each Row In DataTable
Deserialize JSON
Input: row(“YourColumnName”).ToString()
Output: jsonResult (JsonObject)

Assign
    value = jsonResult(0)("value").ToString()

Write Cell or Write Range
    Cell: row("YourColumnName").ColumnName + "_Result" (or a new column)
    Value: value
1 Like
4

@Krishna_Raj & @pikorpa

I’m Getting this error.

image
image787×307 12.1 KB

Can anyone or you share the workflow example for better understanding ?

Regards
Naman

1 Like
5

@Naman_Arora
here you will find example workflow.
BlankProcess111.zip (8.8 KB)
It is correct expression:
Newtonsoft.Json.Linq.JArray.Parse(currentRow("YourColumnName").ToString)(0)("value").ToString()

image
image731×755 57.1 KB

3 Likes
6

@pikorpa

Hi

I’m getting this error when I tried to the sample workflow you shared.

image
image955×532 39.5 KB

1 Like
7

@Naman_Arora
This project is working fine. Maybe we have different version of Studio ¯_(ツ)_/¯
Anyway.
import this:
image
and use
Newtonsoft.Json.Linq.JArray.Parse(currentRow("YourColumnName").ToString)(0)("value").ToString()
like on my screenshot:

image
image646×594 48.9 KB

2 Likes
8

1. Using Text Manipulation Techniques:*

Assuming all cells have a similar structure as the example, here’s a method using string extraction and removal:

  • Read Range:** Read your Excel into a DataTable variable (let’s call it ‘dataTable’).
  • For Each Row:** Use a ‘For Each Row’ activity to iterate through ‘dataTable’.
  • Extract Value:** Inside the loop, use string manipulation like this:

updatedValue = row(“YourColumnName”).ToString.Substring(row(“YourColumnName”).ToString.IndexOf(“:”) + 3, 11)

Replace Cell Value:* Update the current row with:

row(“YourColumnName”) = updatedValue

1 Like
9

@pikorpa

Bot works fine with the steps you shared. But when bot completes it’s execution. I got this error.

image
image691×295 9.83 KB

Am I getting this error because the column has some blank rows as well ?

1 Like
10

@Naman_Arora
try to use if activity:
Not String.IsNullOrWhiteSpace(currentRow("YourColumnName").ToString)

11

Hi ,

The error message “Error reading JArray from JsonReader. Path ‘’, line 0, position 0.” suggests that there is an issue with parsing the JSON data. This error typically occurs when the JSON data is malformed, missing, or not properly formatted as a JSON array.

  1. Validate JSON Format*: Ensure that the JSON string is correctly formatted. It should start with [ and end with ] for a JSON array. Each JSON object within the array should be enclosed in curly braces {} and separated by commas.
  2. Escape Double Quotes*: Make sure that all double quotes in the JSON string are properly escaped. For example, "{\"value\": \"Waterproof\"}".
  3. Check for Empty or Null Strings*: Before attempting to parse the JSON, check if the string is empty or null. This error can occur if you try to parse an empty string as JSON.
  4. Use JSON Activities*: If you’re using UiPath’s built-in JSON activities, ensure that you’re using the correct activity for parsing a JSON array (JArray.Parse) and not for a JSON object (JObject.Parse).
  5. Error Handling*: Implement try-catch blocks around the JSON parsing logic to handle any exceptions and to provide more detailed error information.