How to read data last row to first row from excel

[zaid.ahmad (Qazi Zaid Ahmad)]

Hello Everyone,

I have an excel dump file, there are multiple column but i need to get only 3 column from that excel for example-

Date\Time	Latest Time	Color Name
13-03-2024	09:30:00	Red
14-03-2024	10:30:00	Black
15-03-2024	11:30:00	White
16-03-2024	12:30:00	Red
17-03-2024	13:30:00	White
18-03-2024	14:30:00	Red
19-03-2024	15:30:00	Black
20-03-2024	16:30:00	pink
21-03-2024	17:30:00	White
22-03-2024	18:30:00	Red
23-03-2024	19:30:00	Black
24-03-2024	20:30:00	Blue
25-03-2024	21:30:00	White

In this color Name column there are lots of color available but my requirement is only get the last, 1st “Black” and one more things is there we need to get the top first “White” values row.
For eg:-
|15-03-2024|11:30:00|White|

|23-03-2024|19:30:00|Black|

Please help me how to get that values from an excel

Thanks and Regards
Zaid Qazi

[lrtetala (Lakshman Reddy)]

Hi @zaid.ahmad

Can you try the below

lastBlackRow = dt.AsEnumerable().LastOrDefault(Function(row) row.Field(Of String)("Color Name") = "Black")


firstWhiteRow = dt.AsEnumerable().FirstOrDefault(Function(row) row.Field(Of String)("Color Name") = "White")

Cheers!!

[Jayesh_678 (Jayesh)]

Hi,

Output:-

Xaml File:-
read data rows forum.zip (9.6 KB)

If this works for you, please mark this as a solution, so others can refer the same.

Thanks

[zaid.ahmad (Qazi Zaid Ahmad)]

Hello Jayesh,

We dont have permission to download the xml file
can you share screenshot

[zaid.ahmad (Qazi Zaid Ahmad)]

Hello @lrtetala
When i ran the code and take assign activity, got the below data type message in write line

System.Data.DataRow

[Jayesh_678 (Jayesh)]

Hi,

Input File:-

Code screenshot:-

Code:-
LastBlackRow= inputDt.AsEnumerable().LastOrDefault(Function(row) row.Field(Of String)(“Color Name”) = “Black”)

firstWhiteRow = inputDt.AsEnumerable().FirstOrDefault(Function(row) row.Field(Of String)(“Color Name”) = “White”)

Thanks

[zaid.ahmad (Qazi Zaid Ahmad)]

Hii @Jayesh_678

How to write this output in excel, coz when i try to write the output it will get data type as a System.Data.DataRow not a value

it would be very useful for me thanks

[Jayesh_678 (Jayesh)]

Hi,

Try like below:-

In add data row:-
Array row:- { firstWhiteRow(0),firstWhiteRow(1), firstWhiteRow(2)}

In next add data row:-
Array row:- { lastBlackRow(0),lastBlackRow(1), lastBlackRow(2)}

Thanks

[zaid.ahmad (Qazi Zaid Ahmad)]

Hello @Jayesh_678

Now working fine but in case in color name values “white” or “Black” is not there so it will get the error like “object reference is not set to be instance” so what to do in that situation,

I am not getting understand the condition how give this condition

Thanks and regards
Zaid Qazi

[Jayesh_678 (Jayesh)]

Hi,

If “Black” or “White” is not present in a column “color” then Bot will create a null data row instead of throwing error.
Try below syntax:-

lastBlackRow = inputDt.AsEnumerable().Where(Function(row) row.Field(Of String)(“Color Name”) = “Black”).DefaultIfEmpty().LastOrDefault()

firstWhiteRow = inputDt.AsEnumerable().Where(Function(row) row.Field(Of String)(“Color Name”) = “White”).DefaultIfEmpty().FirstOrDefault()

If this works for you, please mark this post as a solution, so others can refer the same…
Thanks