Reading the Excel rows

Help
1

Hello Everyone,

I would like to get the data rows in a Data Table up to, where the column contains “Closing Balance”

Jb1
Jb11172×114 4.15 KB

Using Read Range is it Possible? where the Closing Balance is not always at the row number 1929.

Thank you very much for your kind help in advance.

Regards
Vishnu

1 Like
2

yes of course it possible @winningvish
mention the range as this in your read range activity
“A1:J1929”
i dont know the last column here i just mentioned the column as J
you can mention based on the last column in your excel,
Cheers

3

Its not always at 1929 it may change for the other spreadsheets.

depending on “Closing Balance” in a column can we get all the records into a new data table?

4

Hello @winningvish,

Read your Excel sheet with read range activity.

And use Select Statement to ignore row contain the “Closing Balance”. Query Like
dt = dt.select(“[column Name] Not Like ‘Closing Balance’”).copytoDatatable

TanQ,
Michael Udhaya

2 Likes
5

Hi ,

Read the Excel sheet using read range activity then

variable = (dataTable.Select(“ LIKE ‘%ClosingBalance%’”).FirstOrDefault())(“Value”).ToString

1 Like
7

variable = (dataTable.Select(" LIKE ‘%ClosingBalance%’").FirstOrDefault())(“”).ToString

1 Like
8

if this Closing Balance is in the last row (though not clear at what row it would be)
then the read range will usually read till last row and last column only not the entire excel sheet…
even the output datatable will give us only the row and column till end cell which has value,
I think we dont need to worry on that and we can mention directly mention like “” in the read range without any range being mentioned
Cheers @winningvish

9

Hi @Palaniyappan,

Apologies for my question was not clearly written…

Thank you for your reply. But the closing Balance is not the last row. There are some unwanted rows after that as well.

So I want to eliminate those rows and get only the records up to Closing Balance in a new Data Table.

Regards
Vishnu

1 Like
10

Then it that case, we cannot handle with read range activity buddy
may be we can process the data till term CLOSING BALANCE inside a for each row loop with a if condition like
row(“yourcolumnname”).ToString.Equals(“Closing Balance”)
if this condition gets passed it will go to the THEN part of if condition where we can use
BREAK activity so that iteration will get terminated from the for each row loop once the row with term closing balance is met
Hope this would help you
Cheers @winningvish

1 Like
11

did that work buddy @winningvish

12

Thank you @Palaniyappan

After modifying the concept it worked.
First read the total excel file by using Read Range.
After that In for each row used If condition. we have to put the Counter to get number of rows for each item while checking for Closing Balance and again used the Read Range, passed the counter value in cell range.

Regards
Vishnu

1 Like
13

fantastic
Cheers @winningvish

1 Like
14

Thank you for the Idea @Palaniyappan… How can I become a Robot Master Like you?

1 Like
15

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