How to get last Row from specific Excel column

Help Studio
1

Hello,

I am trying to get last used row for specific column from Excel file. for me it is giving output as per entire data table last row.

As per my current Code it is giving me output as 9 as per A Column data. But I want to update Status in B Column after each transaction of A Column, So want to get last Used Row from B Column only.

image
image159×193 2.46 KB

2

Hi @nilesh.mahajan

You can make use of REframework with Datarows and then just update the transaction item in set transaction state. This would be more efficient.

Else
Read the excel to a datatable, using read range/ read range workbook
Use for each row in datatable activity
and update the CurrentRow(“Status”) = “Printed” // Any conditions using if

and finally write back the datatable to excel, using write range workbook

Do mark it as solution if it helps :innocent: !

3

Hi @nilesh.mahajan

Try this,
lastUsedRow_B = dt_Input.AsEnumerable().Where(Function(row) Not String.IsNullOrWhiteSpace(row(“B”).ToString)).Count

Update this as per your dt & columns.This counts only the non-empty rows in column B of your DataTable and gives the correct last used row based on that column.

If helpful, mark as solution. Happy automation with UiPath

4

Hello,
Thanks for giving solution.
I have tried with this logic, but it gives me output as 1. ideally it should give 3, because 2nd is Last used row in B Column.

Tried below.
Input_DT.AsEnumerable().Where(Function(row) Not String.IsNullOrWhiteSpace(row(“Status”).ToString)).Count

image
image155×113 1.89 KB

5

@nilesh.mahajan

Can you try this,

Input_DT.AsEnumerable().
Where(Function(row) Not String.IsNullOrWhiteSpace(row(“Status”).ToString)).
Select(Function(row) Input_DT.Rows.IndexOf(row)).
DefaultIfEmpty(-1).
Max()

OR

Input_DT.AsEnumerable().
Where(Function(row) Not String.IsNullOrWhiteSpace(row(“B”).ToString)).
Select(Function(row) Input_DT.Rows.IndexOf(row)).
Max() + 1

6

@nilesh.mahajan

You can also use inbuilt excel activity Find first/last row data row

The image shows an Excel automation workflow in UiPath, specifying steps to find the first and last data row in an Excel file "Inputfile.xlsx," particularly focusing on the "Status" column in "Sheet1." (Captioned by AI)
The image shows an Excel automation workflow in UiPath, specifying steps to find the first and last data row in an Excel file "Inputfile.xlsx," particularly focusing on the "Status" column in "Sheet1." (Captioned by AI)606×719 24.8 KB

and you can use it as num+1 to get next cell and use one

Hope this helps!

7

@nilesh.mahajan,

Follow this solution

Input file:

The image shows a Microsoft Excel spreadsheet listing purchase order (PO) numbers, with a "Printed" status for some entries. (Captioned by AI)
The image shows a Microsoft Excel spreadsheet listing purchase order (PO) numbers, with a "Printed" status for some entries. (Captioned by AI)952×1259 60.4 KB

Code:

The image shows a process flow diagram for an Excel automation task, involving using an Excel file, finding the first and last data rows based on the "Status" column, and logging the last row number. (Captioned by AI)
The image shows a process flow diagram for an Excel automation task, involving using an Excel file, finding the first and last data rows based on the "Status" column, and logging the last row number. (Captioned by AI)594×1038 39.3 KB

Output:

The image shows a debug output panel displaying the start and end times of a "ForumDemo" execution and indicates that an Excel file located at "C:sersshokneDriveocumentsiPathorumDemoataest.xlsx" was used, with a focus on the number "6". (Captioned by AI)
The image shows a debug output panel displaying the start and end times of a "ForumDemo" execution and indicates that an Excel file located at "C:sersshokneDriveocumentsiPathorumDemoataest.xlsx" was used, with a focus on the number "6". (Captioned by AI)1007×337 20.8 KB

8

Hello Ashok,
Congratulations for MVP award & Trophy…

Thanks for Suggestion. But I am not able to find Excel Process scope in my activities. even in Manage Packages also not able to get.
Can you please guide me on this.

9

Thanks a lot @nilesh.mahajan! :heart_eyes:

Install the UiPath.Excel.Activities package and check the activities filter you selected all the options like this:

The image shows the "Activities" panel in a software interface with various filters checked in a dropdown menu, highlighting options for filtering by availability, type, and category. (Captioned by AI)
The image shows the "Activities" panel in a software interface with various filters checked in a dropdown menu, highlighting options for filtering by availability, type, and category. (Captioned by AI)516×527 49.2 KB

10

Hi @nilesh.mahajan

A spreadsheet shows a list of items with the status "Printed" marked for some entries. (Captioned by AI)
A spreadsheet shows a list of items with the status "Printed" marked for some entries. (Captioned by AI)352×303 6.83 KB

The image displays a UiPath Studio workflow sequence for reading an Excel Workbook, assigning values, and outputting results, with properties and debug information shown. (Captioned by AI)
The image displays a UiPath Studio workflow sequence for reading an Excel Workbook, assigning values, and outputting results, with properties and debug information shown. (Captioned by AI)1096×563 30.8 KB

Use
LastRow = Input_DT.AsEnumerable().Reverse().FirstOrDefault(Function(row) Not String.IsNullOrWhiteSpace(row(“Status”).ToString()))

LastRowIndex = If(LastRow IsNot Nothing, (Input_DT.Rows.IndexOf(LastRow) + 1).ToString(), “0”)

If helpful, mark as solution. Happy automation with UiPath

11

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