How to find lastrow in excel using UIpath or SQL query

hi i wanted to know is there any way how to find lastrow in excel using UIpath or SQL query

i dont want to use macro

1 Like

Hi @Vikram212,
You can use read range activity to read excel to a datatable.
Then access the last row by datatable.rows(datatable.rows.count-1)

1 Like

not getting the answer @shankm
only getting this in output

1 Like

Yes, the code will return a datarow object.
You can access the values by using column index or column name
datatable.rows(datatable.rows.count-1).item(“col name”).tostring


ok but how to know whats lastrow number ?

1 Like

when we read the excel using read range, we get a datatable - let’s assume it’s name as DT.

we can find the number of rows by DT.Rows.Count
we know that in programming the index starts from 0, and the count starts from 1.
So, to get the last row of DT, we use rowcount - 1

DT.Rows(DT.Rows.Count-1) --> This will return a DataRow object
we can access the values by
DT.Rows(DT.Rows.Count-1).Item(“col name”).ToString

your last row number will be DT.Rows.Count-1

1 Like

Still not getting right answer @shankm
my table looks like this

and even after applying this formula

i am getting incorrect number as this

can you please share the excel and expected output

as per above screenshot of excel expected output is last row number- 19
it can change dynamically when the new report comes, table can start from anywhere and can end anywhere

1 Like

@Vikram212 Just for a clarification
You want the last row data or last row number?

lastrow number

1 Like

Is there any unique value columns in the excel?

yes unique is - column name called -'Total ’

1 Like

You have 2 choices,
get a unique value from the last row by
DT.Rows( DT.Rows.Count-1 ).Item(“unique value col name”).ToString
and use lookup range activity to find its range

then you will get a value Something like B15 , here 15 will be the last row number

you need to use vb script
vba.txt (386 Bytes)

change the extension of file vba.txt to vba.vbs
then inside the excel scope activity
use Invoke VBA activity as follows

It’s output will be the last row number, and this is the easiest method
But you must enable macro’s to use second method

1 Like