Adding distinct excel column name along with all filetered rows values of another column to one transaction in queue

[loozea]

I have an excel with 2 colmuns - name and numbers. For every distinct name there are multiple rows available
john 112
Mike 118
john 113
john 114
Mike 732

Req: need to filter distinct values from name column and based on filtered data have to copy all numbers .This data I have to add to queue like below format :

Transaction 1 should be
Name: John
Numbers: 112 113 114

Transaction 2
Name: Mike
Numbers: 118 732

Problem statement:
I have created a collection for name and based on distinct values in collection, have done filtering on data table . After that I created a new collection to add all filtered numbers to it. It’s working fine, I am getting distinct names with all filtered numbers

However I am not able to send all numbers with dictinct name in one transaction to queue. Tried few approaches but no luck so far, need your help

[Anthony_Humphries]

1. Use Read Range to get the data

2. Use a Dictionary(Of String, List(Of String)) to store the names as keys and the values as lists of numbers.

3. Iterate over your datatable from the Excel file using For Each Row, and use Add to Collection to add the number to the appropriate collection at the name key.

[loozea]

distinct name filtering and copying of all filtered numbers to collection is done, but I am not able to add distinct name with its all list of numbers to queue in one transaction. Like below

Name: John
Numbers: 112 113 114

[Anthony_Humphries]

Data is sent to the queue using Add Queue Item under ItemInformation. Here you can specify the name of each item you want to add to the queue (Name and Numbers), and the datatype. Your numbers will likely be left as a list datatype.

Also, depending on your requirements, it may be best to send the Name as a Reference instead, which can be specified in the Reference field for Add Queue Item. Since Name is unique, this can be done, and it makes it easier to tell what is in each transaction item when looking at the queue in orchestrator.

[loozea]

Getting below error

[Anthony_Humphries]

Queues might not work well with putting Lists in directly.

Create a String object set to this:

Newtonsoft.Json.JsonConvert.SerializeObject(MyList), where MyList is the list related to the name.

When you need to fetch this data from the queue, you’ll assign a list variable to:

Newtonsoft.Json.JsonConvert.DeserializeObject(Of List(Of String))(MyQueueItem.SpecificContent("Numbers")), where MyQueueItem is the queue item from Orchestrator, and “Numbers” is the name I’m assuming you’ve given this content when you’re loading it to the queue.

[loozea]

Thankyou it’s working