I want to extract only the number from this column into a varibale.
example : what I need is 15826, 13584.
How can I acheive this?
TIA.
example : what I need is 15826, 13584.
First read the data using read range activity.
Then read the specific column value and use regex to get the value.
Regards,
Arivu
How about the following?
appointment_metadata=System.Text.RegularExpressions.Regex.Match(CurrentRow("appointment_metadata").ToString, "(?<=ClientNun"":""\s?)(\d+)").Value
Output:
Or
If you want the numbers seperate with , in a single variable try below
String.Join(",", DT.AsEnumerable() _
.Select(Function(row) System.Text.RegularExpressions.Regex.Match(row("appointment_metadata").ToString, "(?<=""ClientNun"":""\s?)(\d+)").Value))
Output:
Cheers!!
FYI , Another Approach
=> Read Range Workbook
Output → dt
=> Assign activity:
clientNumbers = (From row In dt.AsEnumerable()
Let jsonString = row.Field(Of String)("appointment_metadata")
Let match = System.Text.RegularExpressions.Regex.Match(jsonString, """ClientNun""\s*:\s*""\s*(\d+)")
Where match.Success
Select match.Groups(1).Value).ToList()
=> Message Box → String.Join(", ", clientNumbers)
Let me know if you have any queries
Regards
Thise are json strings…it is good and easy to use desrerialize json and then from the output jobject
Nust use jobj("ClientNun").ToString to get the required value
So process would be
- For each row in excel or datatable
- Deserialize json activity(download webapi packages) and send input as
currentrow("appointment_metadata").ToStringand output can be stored in another variable jobj jobj("ClientNun").ToStringWould give the required value
Cheers
@lrtetala Thank you for your assistance. However, I tried this but the output is Null.
@vrdabberu Sorry for the confusion. I want the Client numbers as seperate values, How can i get that. I will be using this Variable in aType into Activity.
@Anil_G Should I use JobJ in a assign activity?
Use the below syntax in Assign activity:
clientNumbers = (From row In dt.AsEnumerable()
Let jsonString = row.Field(Of String)("appointment_metadata")
Let match = System.Text.RegularExpressions.Regex.Match(jsonString, """ClientNun""\s*:\s*""\s*(\d+)")
Where match.Success
Select match.Groups(1).Value).ToList()
After that use For Each loop and pass clientNumbers
For Each currentText in clientNumbers
Assign -> clientNumber = currentText.ToString
Type Into -> clientNumber
End For Each
Regards
You need to convert this to list below are the Example @Robot2_Automation you need to change according to your value
Option 1
Below Post will help you
Option 2
You can use the regex which pulls your data
Option 3
You can convert to a data table and pull the data
JsonConvert.DeserializeObject(jsonString, new DataTableConverter());
Option 4
You can try to get the good knowledge
SELECT id, JSON_QUERY(jsonData, ‘$.key_word’) AS value FROM your_table
Option 5
By using string manipulation
You can achieve this by using all 4 options, some of the solutions already given by @Anil_G @vrdabberu
@Anil_G Thank you so much, this worked for me. However I also need to filter these values, Ex- I need to exclude number that begin with a G- (G-1418). what would be the best approach?
TIA
1 Like
You can use linq or if condition to check and get only those row swhich you need
As you are doing loop you can use if to check the value contains what you need
Cheers
Thank you so much.
Cheers!
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.