How to remove DataTable rows having empty column cell

Hi Guys,

I want to remove specific rows which is having empty cell in a datatable.
In the attachment, The salary column 3 and 5 cells are empty. Therefore i want to delete row number 3 and 5.Plesase find the attachmentCapture

1 Like

Load the entire into datatable, Then remove the rows where salary= empty by filter datatable activity then again store the value into excel.

@Mahesh5491

QQ.
Should we consider only Salary Column values to be empty or any cell in a particular row/Column

You can try the below Query

Datatable variable = (From x In Datatable.AsEnumerable()
Where Not String.IsNullOrEmpty(Convert.ToString(x(“Salary”)))
Select x).CopyToDataTable

Thanks for the reply. Yes , if salary column cells are empty, the entire row should be removed.

HI @Mahesh5491
we can use Filter datatable activity buddy
–use a excel application scope and pass the file path as input
–use a read range activty and get the output as out_dt a datatable variable
–use a filter datatable activity and pass the above variable as input and in condition mention as
"Salary" = Nothing
and enable Remove radio button in filter wizard window, which would remove the empty cell rows and put the output datatable with a new variable named final_dt created in the variable panel with default value of new System.Data.Datatable
image

Cheers @Mahesh5491

@Mahesh5491

You can try the below Query in assign activity

Datatable variable = (From x In DatatableVariable.AsEnumerable()
Where Not String.IsNullOrEmpty(Convert.ToString(x(“Salary”)))
Select x).CopyToDataTable

error
Please look the attached example and if any issues luk.

1 Like

Is “x” is a DataTable variable?

Nope, its a range variable you need not worry about it use it as it is

Thank you very much.

@muralipa, This one is working. Is it possible to remove empty cell row using DataTable.Select Method?

@Palaniyappan This one will work along with “Salary” isEmpty. Is it possible to achieve this using DataTable.Select Method?

yes of course
like this
Final_dt = out_dt.Select("[Salary] is Null").CopyToDatatable()

Cheers @Mahesh5491

Thanks alot @Palaniyappan. The above code works, It returns empty cell rows. For removing empty cell rows we have to prefix “NOT” in front of null.

aaaha
sorry
it must be like this
Final_dt = out_dt.Select("[Salary] is Not Null").CopyToDatatable()

Cheers @Mahesh5491

3 Likes

so did this get resolved buddy
any issue still
Cheers @Mahesh5491

Yes it Did…Thank you…

Fantastic
Cheers @Mahesh5491

what happened @Mahesh5491

All the answers are right. Thanks to everybody.