Get value from the last non empty cell in an excel row

datatable
excel
activities

#1

I have an excel file which consists of a table which is unstructured and I’m trying to extract the value from last non empty cell of each row. I tried using Get Row Item activity with column index as finalDT.Columns.Count - 1 in For each row, but this returns empty values as well. Any help would be appreciated. Please find the attached excel file (Sheet 2).

invoice.xlsx (9.3 KB)


#2

hi @Sripadraj, Try to read the excel file first and get the row count from it so that you can able to know the last cell value. by that you can fetch last non empty value from the cell.


#3

hi @pavan.vuntla, I meant the last cell of each row which is not empty :slight_smile:


#4

Hi @andrzej.kniola ,
Can we perform this process using Lambda and LINQ ?

Thanks in advance


#5

Hi @Sripadraj

This may not be efficient though but you can get the last non empty cell value in a row.
Last_not_empty_RowValue.xaml (9.5 KB)


#6

@Sripadraj
It would be easier to get it from Sheet1, before it’s split:

foreach (row in dt)
{
	string lastCell = row(0).ToString().Split(","c).Last()
}

@ranjith
Probably you could, but I don’t think it’s worth the trouble.
It’s clearer to just use a normal loop.

That said:
row(col) is nothing or row(col).Equals("")
There’s a better way to test for null or empty:
String.IsNullOrEmpty(row(col))

Another thing is that or operator tests both sides, which means that if your second check involves a method call and the object is null, it will still throw a null reference. Use OrElse in these situations.
See here for more info.


#7

@ranjith Neat, :slight_smile:
How can I read other values in other cells? say the last but one cell of each row? can we pass index to access other cells?


#8

Hi @Sripadraj,

You can read the specific range of cells in a column by giving the index range in “read range” activity.

:slight_smile:

Include this expression instead in IF statement of that workflow for better solution.

Regards,
Ranjith.


#9

hi guys please help me in getting data row by row in for each loop for a single column


#10

@aishwarya.a.kulkarni

Use for each row activity to iterate every row in a datatable and fetch the column value either by passing its column index or column name like row(column_index) or row(“column_name”).


#11

Main.xaml (10.4 KB)

Getting this error in getting data from rows, i tried fetching the row index as well,
its throwing an exception when i tried using Get Row Item as column dont belong to the table, though column name is perfectly added
i’m a beginner, please help in fixing,

Thanks a lot in advance