How to read the Excel based on scenario

Help Activities
, ,
1

I have one excel file. Please refer below screenshot.

image
image484×207 3.92 KB

I will get column 1 value from other input(PDF):

  1. if i received input as xy$z(L1) then i need read from B2:E2
  2. If i received input as c#er then i need read the excel B4:E5.

How to achieve this in Excel automation.

2

@balanirmalkumar.s,

Use If activity with condition like this:

inputVariable="xy$z(L1)"
Read Range activity with Range "B2:E2"
Else
Read Range activity with Range "B4:E5"
3

@balanirmalkumar.s

use lookup excel activity and get the row number you need then can use that in range

"B" + + IdentifiedNumber.ToString ":E" + IdentifiedNumber.ToString

cheers

4

I have multiple records like this. so i can’t hardcode the range value

5

In Scenario 2 this logic wont work. and also i have multiple records few column a values i need to 5 or 6 rows from Column B to E.

6

Hi @balanirmalkumar.s ,
If I understood you correctly, load the excel data into a datatable. Iterate through the rows, if the first column value matches your expected value, then grab values of other columns of the same row. I’m wondering why this is difficult?

7

if first column (A) value matches i need to pick the value from Column (B) to Column (E).

But few Column (A) values having multiple rows (column B to Column E).

Like this i have more than 100 records in same sheet. How to acheive this in one single read range activity.
Ex:

  1. If i received input as c#er then i need read the excel B4:E5.
    In this scenario i have 2 rows
8

Use “Filter Datatable”

9

If i received input as c#er then i need read the excel B4:E5.

In A4 i have input value (c#er) and A5 is empty.
Expected output is need to read tha values from B4:E5.

Here i cant use filter datatable

11

Use “For each row” and use the CurrentRowIndex + 1 to grab values off the next row.

12

@balanirmalkumar.s

follow the steps

  1. read whole data into datatable…
  2. Use look up datatable and get the row number where the required value exists
  3. Use assign and give dt = dt.AsEnumerable.Skip(RowNumberFrom step 2).CopyToDataTable
  4. Now use for each row in datatable and pass dt
  5. use if condition with String.IsNullOrEmpty(currentRow(2).ToString) and more columsn if you feel one column would not decide
  6. on then side exit loop
  7. on else side use assign and save the currentindex value into a variable
  8. Nw after loop use dt = dt.AsEnumerable.Take(indexSaved+1).CopyToDatatable

cheers

13

Doing everything within excel is bit difficult and may become confusing based on the amount of data manipulation you want to do.
My advise would be to read the excel sheet data into a datatable using excel ReadRange or workbook ReadRange activity.

assuming:
your datatable name = dtInputData
And, Column1, Column2 are entries in the first row in your excel sheet

The following command would provide you the whole row of the datatable

selectedRow = dtInputData.AsEnumerable.Select(function(x) x(“Column1”).ToString.Equals("C#er))

Here you can replace “C#er” with the variable holding your input from pdf document

Now you can get whichever column value by using
selectedRow(“Column2”).ToString would return value “123rt”
similarly selectedRow(“Column4”).ToString would return “6”