Grouping multiple excel rows in to single queue item

Help Studio
, , , ,
1

IMG_20230919_213232
IMG_20230919_2132321080×372 91.6 KB

IMG_20230919_213244
IMG_20230919_213244873×306 75.8 KB

In this excel file, Instead of adding each row as a queue item, I want to add multiple rows in to single queue item.
For example : In the Product column, All Lenovo rows need to be added in single queue item, Then in the process state i need to process the grouped rows in queue item one by one.
Is there any way to achieve this ?
Attached the excel file.

Grouping.xlsx (11.9 KB)

2

Hi @Praveen_Vs

Please check on this thread

Hope this helps!!

1 Like
3

One option is to write the desired data to a file (ie CSV or Excel) and then put into the queue item the path and filename of that file.

4

@Praveen_Vs

If serial number is unique…then add all the serials for lenovo for example as comma separated valeus and then when retrieved get the data from excel using serial

Or

You can add the row by serializing all the rows together and then add the serialized string output to queue item

Cheers

5

Let us summarize, what we have understood so far:

Scenario

  • input: Excel
  • data needs to be grouped by 1 column
  • the grouped data should be uploaded to WorkQueue
    • 1 QueueItem = 1 Set of grouped members
  • QueueItem get processed and the grouped members will be used for

For clearing the common understanding of grouped data:

  • input sample:
Col1 Col2 … ColX
A 10 … …
B 10 … …
C 10 … …
D 20 … …
E 30 … …
F 20 … …

Group Member Sets:

Col1 Col2 … ColX
A 10 … …
B 10 … …
C 10 … …
Col1 Col2 … ColX
D 20 … …
F 20 … …
Col1 Col2 … ColX
E 30 … …

Options

So lets have a quick look on the options we do have

  • #A - CSV String on QueueItem

    • converting the Group members to a csv String,
    • store the csv string at the QueueItem (SpecificContent)

  • #B - inputData Upload to Storage Bucket, using List of IDs for referencing the Groupmember set

    • use a discrete column, add an ID column to the input datatable
    • use a discrete name and write the input datatable as CSV Text to the Storage Bucket (Write Storage Text Activity)
    • store the Storage Bucket Path, the List of Group Member Ids on the QueueItem

  • c - JSON Data of Group member Data as serialized DataTable

    • we have this option, but it will create a lot of overhead, due the nature of JSON representing a DataTable

Also we can combine #A and #B as we can store each Group Member Set in the Storage Bucket and use the Storage Bucket FullFilePath on the QueueItem

Kindly Note:

  • We decided for CSV data, as we can write, read the Text directly and do not need to download the data on filesystem before consumption
  • Working with the option of using the storage Bucket, let us reduce the size of Data, that we have to store on the QueueItems

Data Grouping

So lets close by having look at the options to group data:

NON-LINQ Approach

  • For each Row | row in dtDataOrig.DefaultView.ToTable(true, {“YourGroupCol”}
    • Filter DataTable | in=dtDataOrig, out=dtFiltered, Condition: GroupColName = row(GroupColName).toString

Each interation will create a group member set table

LINQ Approach
Assign Activity
Groups | List(Of DataTable) =
(From d in dtDataOrig.AsEnumerable
Group d by k=d(GroupColNameOrIndex).toString.ToUpper.Trim into grp=Group
Select t=grp.CopyToDataTable).toList

Also Non-LINQ / LINQ can be combined into an hybride approach

Also have a look here: