Concatenation Columns and remove Special Characters in Column

Help Activities
,
1

Hi Experts,

I have an Excel file with contain PAN No, Invoice Number, Invoice date.

  1. First thing, i need to remove all special characters which are all involved in Invoice number
  2. After removed special characters in Invoice number column, I need to concatenate PAN No +Invoice_Number+ Invoice Date in other new Column.
    output should be as PAN No_Invoice_NUmber_Invoice Date in new Concat column

these are all performed by LINQ … Please advice me to achieve this.
Input.xlsx (9.8 KB)

2

@Balachander_Pandian
could you please share sample input and desired output

3

Input.xlsx (9.8 KB)

4

@Balachander_Pandian

can you provide any example special characters

i think there are no special characters in Invoice Column

cheers

5

Hi,

How about the following sample?

image
image783×431 18.9 KB

dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r(0),r(1),System.Text.RegularExpressions.Regex.Replace(r(2).ToString,"[^A-Za-z0-9]",""),r(3),r(1).ToString+"_"+System.Text.RegularExpressions.Regex.Replace(r(2).ToString,"[^A-Za-z0-9]","")+"_"+CDate(r(3).ToString).ToString("M-yy")},False)).CopyToDataTable

Sample
Sample20231010-5L.zip (10.0 KB)

Regards,

1 Like
6

ExccelDt.AsEnumerable.Select(Function(x) ExccelDt.Clone.LoadDataRow({x(0),x(1),System.Text.RegularExpressions.Regex.Replace(x(2).ToString,“\W”,“”),x(3),x(1).ToString+““+System.Text.RegularExpressions.Regex.Replace(x(2).ToString,”\W",“”)+"”+CDate(x(3).ToString).ToString(“MMM-yy”)},False)).CopyToDataTable

image
image1739×614 54.8 KB

7

Hi @Balachander_Pandian

Another way you can give a try

  1. Read the excel using read range and store in Datatable. Let’s say dt1

  2. Add a datatcolumn with name of concat column, let’s say concat_col to dt1.

  3. Now use invoke code activity and create a in and out argument named dt1 with value passed to oylt as variable dt1. Select the language type as vb.net

Use below code in the invoke code activity:

 dt1.AsEnumerable().ToList().For each(Sub(r) r("concat_col")= r("PAN No").ToString.Trim+System.Text.RegularExpressions.Regex.Replace(r("Invoice Number").To string.Trim,"[^A-Za-z0-9]","")+r("Invoice Date").To string.Trim)

Hope this helps

Thanks and Regards
Nived N

8

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