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
image

1 Like

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

2 Likes

ok but how to know whats lastrow number ?

1 Like

@Vikram212,
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

ie,
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(0).ToString
or
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
DT.Rows.Count-1

i am getting incorrect number as this
image

@Vikram212,
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

@Vikram212
Is there any unique value columns in the excel?

yes unique is - column name called -'Total ’

1 Like

@Vikram212,
You have 2 choices,
First,
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
image

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

Second,
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
image

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