I have a data table with multiple rows, now i want to loop through each row and convert each row to JSON String and it should include the duplicate rows and duplicate columns as well. Can some one please help me to get the solution for this?
you can first remove duplicate rows using the remove duplicate rows activity, then remove duplicate columns with an assign like
dtUniqueCols = dtInput.DefaultView.ToTable(True, dtInput.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).Distinct().ToArray())
after that loop through dtUniqueCols with for each row and inside use assign
jsonString = Newtonsoft.Json.JsonConvert.SerializeObject(row.Table.Columns.Cast(Of DataColumn).ToDictionary(Function(c) c.ColumnName, Function(c) row(c)))
this way each row is converted to json string without duplicate rows and duplicate columns. if you want to collect all json strings just create a list of string and add jsonString inside the loop.
Cheers
we would suggest to share with us
- sample input data
- expected output data
In general we would recommend to do the deduplication at Datatable level (e.g. with Activities, LINQ, GroupBy Approach) and then do the conversion into JSON
Also have a look here:
[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum
keep in mind, that a conversion of a data row also serializes the entire DataTable, as it is part of the datarow as a so called navigation property.
Maybe you are looking for a key/value representation of the datarow converted into a JSON which is close to the result e.g. of dictionary.
As mentioned above:
based on your input we can suggest more on the implementation approaches
1 Like
It should include Duplicate rows and Duplicate columns as well, so without removing duplicates will this work?
Follow this approach.
- Remove duplicate Columns (There is no chance you can have duplicate columns but if duplicate you mean values of the columns will be same and you want to remove those repetitive data columns, follow this)
Use Assign activity
filteredDt = dtInput.DefaultView.ToTable(True, "Name", "Age", "City")
- Remove Duplicate rows
UseAssignactivity
distinctDt = filteredDt.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDataTable()
- Iterate through all the rows and convert the row to JSON
Use For Each Data Row, Assign, For Each activities as mentioned below.
For Each row In distinctDt.Rows [For Each Data Row activity]
dict = New Dictionary(Of String, Object)() [Assign activity]
For Each col In distinctDt.Columns [For Each activity]
dict(col.ColumnName) = row(col.ColumnName)
Next
jsonString = JsonConvert.SerializeObject(dict) [Assign activity]
Next
Yes give it a try but this will not include the column names if you want the column names also be included then use this in assign activity inside the loop
jsonString = Newtonsoft.Json.JsonConvert.SerializeObject(
row.Table.Columns.Cast(Of DataColumn).Select(Function(c) New With {Key .Column = c.ColumnName, Key .Value = row(c)})
)
It’ll include the names also
