Can we create data tables dynamically?

Help
, , ,
1

Hi,

I have one excel file in which I have two sheets “Join1” & “Join2”.

I want the data from both sheets into two different data tables.

I know we can get the data from both sheets by usinge ReadRange activity 2 times.

But I want to do this using foreach loop, so in this case I have to write Readrange only 1 time. but I am facing problem with Datatable variable. If I use foreach loop then what should I provide as Datatable variable? If I hardcode variable then in this case both sheets data will come into this same datatable.

Can anyone please let me know how can I achieve my task? Can we create datatable dynamically, so we can pass it into ReadRange while foreach.

1 Like
2

You can add each datatable to a dataset.

Assign ds = new dataset("MyDataSet")
For each sheet in Excelworkbook
Read range for sheet and save as dt1
Assign dt1.TableName = sheet
Invoke method activity: TargetObject = ds.Tables  MethodName = Add  Parameters = In (of type datatable) = dt1
Next sheet

The above pseudo code will read each sheet in excel, create a table (and name the table after the sheet name), and then add it to your dataset. You can then iterate through this dataset, or you can reference individual tables by index ds.tables(0) or by table name ds.tables("Sheet1")

1 Like
3

Thanks @Dave.

I am very new in this UI path thing, can you please provide me the xaml for this so it will be more easy to understand how to do this.

Many thanks in advance.

4

Sure thing - this is exactly what i’ve written above in .XAML form ShenkiSinghai.xaml (9.6 KB)

2 Likes
5

Thank you so much @Dave.

It worked. But can you please also explain what first assign (dataset thing) and last invoke method is doing as I am new so not such familiar with both.

Thanks again.

6

Hi. Not sure why you want to do this, but another solution could be to create a list of Datatables and add them dynamically on each iteration of the foreach loop. That way you won’t need to write custom code. Sample attached.

Cheers,
Main.xaml (6.6 KB)

1 Like
7

First assign is just telling uipath that you want to create a new dataset variable. This is a vb.net ‘class’ and you can see more documentation here: DataSet Class (System.Data) | Microsoft Learn - it’s just a good way to store multiple tables. You could also store them in an array, a list, or any other type of collection of datatables. Using a dataset is my preferred method though so you can name your datatables and then call them by name instead of only by index.

Invoke method piece is utilizing the ‘Add’ method for datatable collections, which adds a datatable to the collection specified - see more documentation here: DataTableCollection.Add Method (System.Data) | Microsoft Learn

Each class (or type of variable) has things called ‘Properties’ and ‘Methods’ associated with them. Properties are attributes and can be changed in UiPath in assign activities. You say the property on the left hand side, then what you want to change the property to on the right hand side. Methods are actions to be taken on that variable. To do this in UiPath you have to use the Invoke Method activity. You supply the object you want to work with in the TargetObject, the activity you want to do in the MethodName, then parameters you specifiy any arguments required by the method. Search out individual methods using google or the microsoft documentation about the particular class you’re working with

@pduffy - using a list works just as well, but I would recommend utilizing a dataset when working with a collection of datatables as it allows you to reference by name instead of ONLY by index. This can make it much less confusing down the road when you want to reference individual tables within the collection. No custom code necessary

2 Likes
8

Thanks @pduffy.

My motto was to optimize the code as well also less manual efforts to create datatables. If we have to work on lets say 20 sheets and we want to add the data in of each sheet in different datatables then we have to manually define 20 variables of data tables. But by this solution we are letting it do by itself as per the number of sheets.

Many thanks for your help. Much appreciated.

9

Thank you so much @Dave. :slight_smile:

1 Like
10

Thanks Dave. Yes agreed there are multiple ways to skin a cat :slight_smile: … And actually I should have read your solution in more detail before commenting on custom code, I see now it was pseudocode :grin: A dictionary could also allow to reference by name … was just general advice to hopefully give some guidance towards working dynamic variable names. Thanks for your answer and feedback :+1:

11

@pduffy, thanks for the idea. I’m trying to create your suggestion of using dictionary but I think I’m doing something wrong. Do you have an example to share where instead of list of datatables, you have collection of dictionaries with each datatable addressed by a string label? (I assume that is what you meant)

12

Hi. Just create a dictionary variable with key=String, Val=Datatable. Remember to initialize it before you start the loop:

Assign Dict_Datatables = New Dictionary(Of String, DataTable)

Make life easy on yourself by installing this package:
Microsoft.Activities.Extensions

And then use the “Add To Dictionary” Activity. Set the key as the sheet name and the value to a New Datatable.

Hope this helps… let me know if you have any problems.

Cheers,

13

@himavad - keep in mind you can’t rely upon the order within a dictionary, so you can’t use index to reference the datatables anymore. If you want the flexibility (and have it be ever-so-slightly quicker than a dictionary) I would stick with datasets when using a collection of datatables :slight_smile:

14

Thanks both for the guidance. I tried out both methods and I think I get the hang of it now. Which one I use I guess will depend on the need of the day, but glad I can learn both ways. Uploading this XAML which contains both methods, credit to @Dave and @pduffy

DynamicDataTables.xaml (13.0 KB)

1 Like
15

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