Convert datatable into json format in object?

Help Community
,
1

Can i convert data in excel sheet into json by this format?

Book1.xlsx (7.9 KB) excel sheet
test.json (239 Bytes) json format

the json result
{
“collections”:{
“Hello”:{
“1234”:{
“quantity”:“1”
},
“4567”:{
“quantity”:“2”
},
“4345”:{
“quantity”:“1”
}
}
}
}

thanks in advance

2

Hi @rpa13,

Yes you can convert excel into json format.
Sharing screenshot below for your reference:

image
image1541×740 53.6 KB

Also attaching json file
config - Copy.json (2.4 KB)

Regards
Sonali

3

I would suggest the following

  1. Read “Sheet” range
  2. Add the sheet range to a collection dictionary
  3. Serialize the collections dictionary to get a json.

Workflow file:

DataTableToJson
DataTableToJson387×597 14.5 KB

*Note: If you have more sheets, add the activities Read Range and Invoke Method(add in dictionary) inside a loop (For Each) to read each sheet and at the end serialize everything.

Implementation details

  1. Include UiPath.WebAPI.Activities or just Newtonsoft.Json package
    DataTableToJson - Package

  2. Import into sequence file:
    Newonsoft.Json
    DataTableToJson - Import

  3. Variables used in the sequence file:
    DataTableToJson - VariablesUsed

  4. Invoke Method

    • TargetObject: DictCollections
    • MethodName: Add
    • Parameters:
      DataTableToJson - InvokeMethod AddDictionary Parameters

  5. Invoke Method

    • TargetType: Newtonsoft.Json.JsonConvert
    • MethodName: SerializeObject
    • Parameters
      DataTableToJson - InvokeMethod Serialize Parameters
1 Like
5

@rpa13
I share source code in its minimum functional version (It doesn’t require anything additional to run).

DataTableToJson.zip (8.2 KB)

6

target-json-format.json (240 Bytes)
here is the json file format must be like this
your workflow give me another format
as i need each product in colum proudcts in sheet to be object with value quantity
like this “1234”:{“quantity”: 1}
@JesusFontalvoGomez

7

target-json-format.json (240 Bytes)
the target json file output
@sonaliaggarwal47

8

This being the case, you must manipulate the row to convert it into the desired object.

The solution I would give is:

  1. First convert the row to a dictionary of dictionaries (to emulate an object).
  2. Then add it to a list of objects (dictionaries).

DataTatatoJson_ManipulateRow
DataTatatoJson_ManipulateRow338×592 18.4 KB

There you would have your collection of Sheet1.

If you want to add more columns, you must add them at the time of converting the row to an object (Invoke Method ‘Add to DictRow’).

New Dictionary(Of String, String) From { 
	{"quantity", row("quantity").ToString}, 
	{"column3", row("column3").ToString}, 
	{"column4", row("column4").ToString}
}

Source code: DataTableToJson_ManipulateRow.zip (8.7 KB)

Likewise, as I said in a previous answer, if you are going to do the same thing for several sheets, you should contain all the object construction inside a loop that goes through all the sheets, and at the end of the construction, serialize.

2 Likes
9

format.json (238 Bytes)
please can i convert into json file same like this one ?
@JesusFontalvoGomez
@sonaliaggarwal47
@Adrian_Star
@AndersJensen

10

Hi @rpa13 ,
solution for You based in @JesusFontalvoGomez project in Data_Table_na_JSON.xaml
DataTableToJson_2.7z (8,5 KB)

2 Likes
11

Sequence.xaml (10.7 KB)

here is the workflow with target json format i need
thanks all for your support and helpful guys :slight_smile:

i use your workflow but i remove it from list as i need it in object @JesusFontalvoGomez your workflow help me alot thanks

1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.