I am working on a automation… We would have to update the complex json(multiple nested) in a web application with the values from a excel sheet … Can you please tell me the complete process to do it… I am new to Uipath…
@sophi.priyanka
welcome to the forum
for giving an individual solution suggestion we would recommend to share the sample Excel and expected JSON out sample with us. Thanks for suppport
Hi @sophi.priyanka ,
Welcome!!
As suggested by Peter, please share the other relevant details.
Cheers
I might not be able to give u a excel sheet as it’s confidential…
The excel does have values for the keys which are available in the json as well… we need to update that values with the values from the excel…
Does this information be of any help ?
theres no problem, and we also suggest to keep confidential private data. But you can also prepare sample data instead. e.g
an email: abc@domain.de
a socialno: 12*******45
etc
its just important that the basic patterns which are relevant are derivable
Example would be Excel data
Key value
Name. Abcdefg
Age. 18
Location. Xyzzzz
Timezone est
Json file has
{
“Name”: “fghijkl”
“Age” : “12”
“Creation”:{
“Location”: “vbnmzxc”
“Timezone”: “cst”}
}
}
This is an example so I would have to update the values from the excel to the json…
My actual json has many nested array as well…
Does this help ?
Any suggestions would be helpful ?
we can do it on textfile base with a simple replace of placeholder
So we just replace within a loop the placeholder value with the value from excel.
as another option - JSON Update Approach:
Lets assume
- a JSON / JSON Template (e.g. like above)
- Update Values From Excel within Key/Value Cols (as mentioned above)
With the help of the SelectToken(s) method we can setup a dynamicly / configurable approach:
The Mapping/Path Config:
Flow:
- Mapping Config definition
- Read JSON Template File as Text - strJSON
- Parse strJSON into a JObject
Value Lookup Dictionary:
dictLKValues =
dtData.AsEnumerable.ToDictionary(Of String, Object)(Function (x) x("Key").toString, Function (x) x("Value"))
- loop over the MappingConfig Table
- using the Path for the retrival
- Updating the value with value retrived from Lookup Dictionary using the Key from Key Col
Visuals:
find starter help here:
JSONUpdate_FromExcel_ByPathMapConfig.xaml (13.1 KB)
data.json (117 Bytes)
Find some more help on syntax for the retrieval here:
JSONPath - XPath for JSON
Thank you for your reply…
am trying the same process but when I open the file there is a Missing activity after “Read Textfile”
Can you please let me know what that is ?
please add package UiPath.WebApi.Activities
Thank you for the information … I tried ur suggestion and am getting a error “Assign: An Item with the same key has already been added”
Can you please tell me why am I getting this error?
keys has to be unique in a dictionary.As You have the control over names, just adopt the key. Otherwise share more info on what you have done so far e.g. with screenshots
I did the same exact steps from the xaml file which was shared…
we cannot crosscheck without more shared details
is ensured that
- within the excel with the values each key is unique?
- the resulting datatable is not containing rows with blank values?
- this XAML is not involved within an invoke workflow
feel free to share your XAML with us
Yes the values are unique. Datatable does have blank values for which I put in null.
Ok, so we can help more when we do have a chance to review implementation. Decide by yourself on what you want to share with us additionally (e.g. screenshots from relevant implementation parts, XAML…)
we do see difference between the provided XAML and XAML from the screenshots. So there was a modification done.
we assume that the assign dictLKValues is failling (Check and confirm pls)
so in dtable1 all Keys have to be unique
ashokkarale
Anil_G
Yoichi
yangyq10
postwick
chandreshsinh.jadeja
aravindbalineni123
Parvathy
aya
PRASHANT_GABHANE