Need LINQ for a requirement

Hi Guys,
i have a small requirement where i have a column with almost 5000 rows in excel

so i have to do some operation , which is check whether there are empty rows, if so eliminate them

next to check for number of digits , so lets say in row 1 i had value as 234 ( I need to make as 8 digits by always adding 0 to left side) -00000234
adding 0 to left side is always default only if number is less than 8 digits

if its 7 digits we have to add one zero to left side

can you help me here

also if the row has any alphabets in it , we hv to skip …no need to perform above task

@ppr @Yoichi expecting ur inputs

data looks something like this

Hi @micheal1 ,

Keeping it simple, if you have just that one column, you could use this to perform the conversion →

dt.AsEnumerable().Where(Function(w) Not(IsNothing(w("Unique ID")) OrElse String.IsNullOrEmpty(w("Unique ID").ToString))).Select(Function(s) dt.Clone.LoadDataRow({s("Unique ID").ToString("D8")},False))

A Where clause is used to retain those rows which are not empty, and the D8 is invoked to pad zeros towards the left.

If there are more columns, we can simply modify the code and pass it into an Invoke Code Activity.

Kind Regards,
Ashwin A.K

1 Like

Hi @micheal1 ,
To Remove empty rows use below syntax
DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()


1 Like

Thanks for your Reply

No my objective is to perform this operation for only one column,

yes i have other few columns but its not required for me @ashwin.ashok
and how does D8 work ?

as i had asked if there is a alphabet in any row its should not perform the operation

Hi @micheal1 ,
use below syntax to update the value


Hi @micheal1 ,

Could you Check the Below Workflow :

DT_Update_RemoveEmptyRows.xaml (10.3 KB)

I have used a For Each Row instead of a Linq to Update the Values. Just Check if it does solve the case. We could later Check if it is really required to be converted to a Linq Update.

Also, It is an Example workflow, The Column Names are not the same as your Input

Input :

Output :

1 Like

Hi @micheal1 ,

D8 simply pads zeros towards the left until there are 8 characters in total.
If you want seven or 9, then simply add that in place of the 8, i.e.,

D6, D7...etc

As for testing eliminating rows containing alphabets, could you given this a try?

dt.AsEnumerable().Where(Function(w) Not(IsNothing(w("Unique ID")) OrElse String.IsNullOrEmpty(w("Unique ID").ToString) OrElse Not w("Unique ID").ToString.IsNumeric)).Select(Function(s) dt.Clone.LoadDataRow({s("Unique ID").ToString("D8")},False))

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok
instead of eliminating can i keep them as it is ?

also D8 would hold good fr all rows here?

my input will be


Bc566 ( as it is)

Thank you

could you help me here guys @ashwin.ashok @ppr @Yoichi

Thanks @supermanPunch
but the data would be huge sometimes

so i recommend linq

Hi @micheal1 ,

Is this the expected output?

If so then here is a sequence with the logic for achieving the outcome along with the codes →

For Filtering Out Empty Rows →

dt.AsEnumerable().Where(Function(w) Not(IsNothing(w("Unique ID")) OrElse String.IsNullOrEmpty(w("Unique ID").ToString))).CopyToDataTable()

For Padding(Invoke Code) →

dt.AsEnumerable().ToList().ForEach(Sub(row) _
	row("Unique ID") = If(row("Unique ID").ToString.IsNumeric,
	row("Unique ID").ToString.PadLeft(8,CChar("0")),
	row("Unique ID").ToString))

RemoveEmptyRowsandPad.xaml (7.4 KB)
Kind Regards,
Ashwin A.K

This was throwing an error like cannot convert string to integer


dt.AsEnumerable().Where(Function(w) Not(IsNothing(w(“Unique ID”)) OrElse String.IsNullOrEmpty(w(“Unique ID”).ToString) OrElse Not w(“Unique ID”).ToString.IsNumeric)).Select(Function(s) dt.Clone.LoadDataRow({s(“Unique ID”).ToString(“D8”)},False))

Hi @micheal1 ,

I’ve included an updated workflow, please let me know if it works as expected.

Kind Regards,
Ashwin A.K

Thanks @ashwin.ashok

its working

@micheal1 ,

If the Concern is for the Execution Time (5000 rows), we would like to ask you to Check/Compare the Time Taken using a For Each Row and a Linq Update Query.

Then Choose the Appropriate one as you would want to.

1 Like

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