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.
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
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")
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.
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.
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
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.
Thanks Dave. Yes agreed there are multiple ways to skin a cat … And actually I should have read your solution in more detail before commenting on custom code, I see now it was pseudocode 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
@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)
@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
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