Regular expression for some pattern in excel

Learning & Development Programs Automation Starter
, ,
1

Hi all ,
Excel has extracted data
PT6A-67F
PT6A-67F
PW545C
PT6A-140AG
PW207E
PW206C
PT6C-67C
PT6C-67E
PW207E
PW207D1
PW120A
PW545C
PW207D
PU0315
PT6T-3B
PC2017
PW306D1
PW306C
PJ0596
PW545C
PW545C =Input
where it should remove the data other than this pattern => APS* , JT15* , PT6* , PW1* , PW2* , PW3* , PW5* , PW6* , PW8*.
Example ; PJ0596,PC2017,PU0315 has to be removed from excel row.
What changes i need to do. Please help
Regards,
Lakshmi

2

Hi,

How about the following expression?

prefixes = {"APS" , "JT15", "PT6" , "PW1" , "PW2" , "PW3" , "PW5" , "PW6" , "PW8"}

Then

dt = dt.AsEnumerable.Where(Function(r) prefixes.Any(Function(s) r(”yourColumn”).ToString.startsWith(s))).CopyToDataTable

Regards,

3

Hi @Yoichi ,
This is working but its removing the row which is empty. Empty row is also required.
What changes needs to be done.
Thanks,
Lakshmi

4

@Yoichi

image
image185Ă—642 4.13 KB

blanks rows are also required.
Its removing blank row.
image

Thanks,
Lakshmi

5

Hi,

Alright, can you try the following?

If your datatable has single column, the following will work.

dt  = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({if(prefixes.Any(Function(s) r("VA03 extract").ToString.startsWith(s)),r(0).ToString,"")},False)).CopyToDataTable

Note: If there are multiple columns in the datatable, need to modify argument of LoadDataRow

OR

Use InvokeCode Activity with the following code

dt.AsEnumerable.ToList.ForEach(Sub(r) 
    If(Not prefixes.Any(Function(s) r("VA03 extract").ToString.startsWith(s))) Then
	r("VA03 extract")=""
End If
End Sub
)

Note: dt is In/OUT DataTable type argument in InvokeCode
prefixes is In String array type argument

Regards,

1 Like
7

Hi @Yoichi ,

Thanks for helping and providing the solution.

Thanks & Regards,
Lakshmi

1 Like
8

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