How to store the values of each item in sepearate variable while running in for each loop- refer the sheet

how to store the values of each item in separate variable while running in for each loop- refer the sheet

image

1 Like

If that the case dont use foreeach use the index and asign to variable example:tablerows(0).tostring=variable1 and so on hope it make sense.

cheers
Happy learning

3 Likes

Yes of course that’s possible
We can use dictionary of type (of string, string)
— so use a excel application scope and pass the file path as input
— use a read range activity and get the datatable as output
— use a for each row loop and pass the above variable as input
— inside this loop use a assign activity like this
Out_dictionary(row(“Question”).Tostring) = row(“Answer”).ToString

Where Out_dictionary is a variable of type
System.Collections.Generic.Dictionary(of string, string) define it in the variable panel with a default value like New Dictionary(of string, string)

Now as you wish each value (of answer) gets assigned to a key (question)in the dictionary variable
— so If we want to access the value of each question then we can simply write as for example
Out_dictionary(“Client Name”).ToString. // output will be Onida

Like wise we can get any value we want
Simple isn’t it

Or

If we are trying to access all the three columns
Then we can get them by just mentioning their column index inside the for each row loop like
This
Out_value1 = row(“column1name”).ToString
Out_value2 = row(“column2name”).ToString
Out_value3 = row(“column3name”).ToString

Now we can use these three string variables any where

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @KavithaManohar

2 Likes

@KavithaManohar
@pattyricarte is right in this case you should be used index instead of for each
But it not good if Excel file has more record
So I suggest create array of string …use for loop to iteration and store each value in string to increase index

Thanks

2 Likes

So did that work buddy
Were we able to store and access them
Cheers @KavithaManohar

i got the below error. could you please look into it.

InputDatasheet.xlsx (14.3 KB) Sequence.xaml (7.2 KB)

image

1 Like

can u please share me one sample .xaml file…i am facing issues so.

Kindly change the sheet name
image
from Sheet1 to Sheet4 (as Sheet1 has Client name and not Client Name)
only Sheet4 has that key

Cheers @KavithaManohar

Issue is resolved…Thanks a lot

1 Like

kindly change the message box
as
Out_dictionary(“Client name”).ToString
Cheers @KavithaManohar

But can u tell me, how can I save these answers in seperate varibles for all these questions… can u share me the sample .xaml file pls

for example…instead of ‘Client name’— how can i refer to this using row id… i need the format.

1 Like

@KavithaManohar
Hello use this custom activity
https://go.uipath.com/component/convert-excel-to-dictionary-key-value-pair
This activity store Excel in dictionary key value pair. it’s easy to access .
Thanks

3 Likes

but i couldn’t find this activity…

@KavithaManohar
You need to download nuget package from uipath.go
Click on https://go.uipath.com/component/convert-excel-to-dictionary-key-value-pair
This link
Then download nuget package

2 Likes

After that follows this step

Thanks

3 Likes

but dictionary is not handling the data in sheet2, there is no option to specify the extra sheet name under properties, it is fetching only sheet1 data…correct me if I am wrong

Yes this is for only sheets
I made this activity for read configuration file

1 Like

then how to handle the data in other sheets…any suggestions ?

1 Like

@KavithaManohar
There is many way to read Excel file

  • if number of sheet and name of the sheet is fixed then you can use read range activity to read Excel file through passing sheet name and save in datatable and use it.
    – if number and name of sheet are not fixed then you can use cutom activity to get sheets name and for each sheet you can use read range activity to read Excel
    https://go.uipath.com/component/get-sheets-name-from-excels

Thanks

1 Like