How can i read data from the particular cells

Help Studio
, ,
1

Hi, I’m having two sample excels from that i need to extract Final Amt, But that is mentioned in two different cells
1.In Sheet1 it is mentioned in G7 and in sheet2 it is mentioned in G5.
So i need to extract that based on the Final Amt. Final Amt cell is also dynamic .
So while I’m using Read cell activity I’m
sheet1.xlsx (8.6 KB)
not able to read the particular data. As the cells are changing.
Can any one please help me resolve this issue.
sheet2.xlsx (8.5 KB)

2

Hi @Chippy_Kolot ,

Will the words Final Amt be in Column A always ? We could then find the matching row which contains these words and then retrieve the value from the Column G of the same row.

For a Start approach, give a try on using the Lookup Datatable Activity like below :

image
image1004×455 32.6 KB

3

Yes it will be in Column A

4

@Chippy_Kolot ,

You could try the above approach as provided in the Image using Lookup Datatable Activity.

Let us know if you are facing any difficulties.

5

Column Name Grand Total can vary, Some items it may be null

6

Hi,
In Lookup range target column is mentioned as “Grand Total” hence this won’t be an issue

7

Main.xaml (8.2 KB)

8

Sheet3.xlsx (8.9 KB)
If excel is coming like this how can i solve this.

9

Sheet3.xlsx (8.9 KB)
In some scenarios data is coming like 12345 Final Amt, this 12345 is excelNo.it can vary.

10

Sheet3.xlsx (8.9 KB)
Can you please explain in this scenario how will i able to fix this.
Eg:12345 Final Amt means 12345 is the excel sheet No, it can vary for different excels.

11

@Chippy_Kolot ,

Since we know it would be always Column G, we can use this info for converting to column Index using the Expression below and then use it in the Activity for the property ColumnIndex.

(UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnLetterToColumnIndex("G")-1)

image
image1018×492 34.5 KB

12

@Chippy_Kolot ,

Do you also need this value ? CustomerNo value ?

Again we would need to know whether it would always be in the same column or you could even try the same logic that we had applied for Final Amt

13

Hello @Chippy_Kolot
kindly refer to this Xaml file you may get some idea
Forum_LookupValue.zip (82.1 KB)

14

Hi, I’m having two sample excels from that i need to extract Final Amt, But that is mentioned in two different cells
1.In Sheet1 it is mentioned in G7 and in sheet2 it is mentioned in G5.

So i need to extract that based on the Final Amt. Final Amt cell is also dynamic .
So while I’m using Read cell activity I’m

not able to read the particular data. As the cells are changing.
In some scenarios data is coming like eg:
sheet2.xlsx (8.9 KB)
Sheet3.xlsx (8.9 KB)
12345 Final Amt, this 12345 is excelNo.it can vary.
Can any one please help me resolve this issue.

15

Hi @Chippy_Kolot

Read the excel file using Read Range

Then simply get the row where first column contains the Final Amt string
The column at 6th index contains the amount value

dt.AsEnumerable.Where(Function(r) r(0).ToString.Contains("Final Amt")).First()(6)
16

Duplicate Topic: How can i read data from the particular cells

17

Hello @Chippy_Kolot
kindly refer to this Xaml file you may get some idea
Forum_LookupValue.zip (82.1 KB)

18

12345 Final Amt, this will be in column “A” itself.
but 12345 that is excel No it can vary.

19

Hi,
In this Scenario I’m having column name as Final Amt Discount, So while I’m providing Final Amt in lookup range it is not coming to last column, In Write line it is writing as E6.Actually final Amt is in column A11.
“56456 Final Amt”-56456 may varies for every excel.Can you please help me to solve it.
Sheet3.xlsx (8.9 KB)

20

@Chippy_Kolot are you expecting E11?

image
image795×479 17.5 KB