Spilt Column Value in data table and add to other columns by using LINQ

Hello Everyone,
I have a data table with 6 columns I need to split the First Column values and add those values in to rest 5 Columns.
Sample Excel Data

Required Output

Thanks in advance

Hey!

I am not familiar with LINQ.

But I can suggest you one thing

Use Regex to get the particular data use add data row and pass this variables in the array of row

If you need i will give you the regex for this

if yes, need one row value to get the text

Regards,
NaNi

Hi THIRU_NANI,

Yes,
PFI
06/10/2018 19:01 0000000000005113 317 - INVALID PHONE NUMBER 000002134123456 0221 9876

Thanks.

@nitesh.s Can you share a sample input file

Hey!

Date:

Assign Date = System.Text.RegularExpressions.RegEx.Match(CurrentRow(0).ToString.Trim,".*(?<=:).{0,2}").ToString

PAN:

Assign PAN = System.Text.RegularExpressions.RegEx.Match(CurrentRow(0).ToString.Trim,"\d{16}").ToString

ResultCode:

Assign ResultCode = System.Text.RegularExpressions.RegEx.Match(CurrentRow(0).ToString.Trim,"(?<=\d{16}).*(?<= INVALID PHONE NUMBER)").ToString

OR: (RESULTCODE)

Assign ResultCode = System.Text.RegularExpressions.RegEx.Match(CurrentRow(0).ToString.Trim,"(?<=\d{16}).*(?=\d{15})").ToString

PhoneNumber:

Assign PhoneNumber = System.Text.RegularExpressions.RegEx.Match(CurrentRow(0).ToString.Trim,"(?<= NUMBER ).\d+").ToString

ExpDate:

Assign ExpDate= System.Text.RegularExpressions.RegEx.Match(CurrentRow(0).ToString.Trim,"(?<=\d{15} )\d{4}").ToString

SSN:

Assign SSN= System.Text.RegularExpressions.RegEx.Match(CurrentRow(0).ToString.Trim,"\d{4}$").ToString

Follow the steps:

  1. Create one build data table
  2. Read Range output as → Dt_Out
  3. For each row pass the Dt_Out

Take Required assigns

Create 6 String Variables

And pass those Regex values

Now take one add data row activity

In the Array of String

Mention the variables like this

{Date,PAN,ResultCode,PhoneNumber,ExpDate,SSN}

Note:

The column Names which is created using build data table the position should match with the passed variables in Array of row

Try this and let me know’

Regards,
NaNi

1 Like

You can read in the excel with read range addHeaders Option is ticked on - dtData
dtCleansed = dtData.Clone
then loop over dtData

  • use the first column value, Split with regex all multiple spaces and add it as rowArray to dtCleansed
  • Add Datarow Activitiy - Datable: dtCleanse - RowArray:
    System.Text.RegularExpressions.Regex.Split(row(0).toString.Trim, "\ +")
    Demo:
    grafik

Another strategy is to use regex replace and different replace patterns to set some delimeters and then later split it on base of the delimiter e.g. a |


Edited: used Varuns pattern for this as we can do it with a single pattern

Hi Ushu,

Please Find the attachment
TestFile.xlsx (8.4 KB)

Thank You.

Hi @nitesh.s

I have implemented the @ppr aaproach
Please have a look

image

image

Output

SplitColumns.xaml (7.6 KB)

3 Likes

Thank You so much Varun It working! :slight_smile:

Thank you So much NANI for the Solution :slight_smile:

1 Like

Thank you So much Peter for the Solution.:slight_smile:

@nitesh.s
for clarity of Linq statement, you may use LoadDataRow()

create a clone of dt_Output → “dt_outputclone”

have dedicated regex patternn declared as variables like DateTimeRegexPattern
panRegexPatttern

and invoke it in assign stament as below

(From row In dt_input.AsEnumerable()
Select dt_outputclone.LoadDataRow(New Object() {
Regex.Match(row.Field(Of String)(“Date/Time”).ToString,DateTimeRegexPattern,RegexOptions.Compiled).Groups(1).ToString,
Regex.Match(row.Field(Of String)(“Date/Time”).ToString,PanRegexPattern,RegexOptions.Compiled).Groups(1).ToString
},True)).CopyToDataTable()

@nitesh.s Try attached workflow

Example.zip (20.5 KB)

Input

Output

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