Reading the Excel rows

Hello Everyone,

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

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.


1 Like

yes of course it possible @winningvish
mention the range as this in your read range activity
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,

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?

Hello @winningvish,

Read your Excel sheet with read range activity.

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

Michael Udhaya


Hi ,

Read the Excel sheet using read range activity then

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

1 Like

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

1 Like

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

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.


1 Like

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

did that work buddy @winningvish

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.


1 Like

Cheers @winningvish

1 Like

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

1 Like

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