Need LINQ for a requirement

Help Studio
, ,
1

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

2

data looks something like this

image
image382Ă—672 12 KB

3

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
4

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()

Regards,
Arivu

1 Like
5

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

6

Hi @micheal1 ,
use below syntax to update the value
row(0)=row(0).ToString("D7")

Regards,
Arivu

7

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 :
image

Output :
image

1 Like
8

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

9

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

003
178
78996
Bc566
890

output
00000003
00000178
00078996
Bc566 ( as it is)
00000890

Thank you

10

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

11

Thanks @supermanPunch
but the data would be huge sometimes

so i recommend linq

12

Hi @micheal1 ,

Is this the expected output?
image

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

13

This was throwing an error like cannot convert string to integer

@ashwin.ashok

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))

14

Hi @micheal1 ,

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

Kind Regards,
Ashwin A.K

15

Thanks @ashwin.ashok

its working

16

@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
17

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