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
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.
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:
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
You can read in the excel with read range addHeaders Option is ticked on - dtData
dtCleansed = dtData.Clone
then loop over dtData
System.Text.RegularExpressions.Regex.Split(row(0).toString.Trim, "\ +")
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 |
Hi @nitesh.s
I have implemented the @ppr aaproach
Please have a look
SplitColumns.xaml (7.6 KB)
Thank You so much Varun It working!
Thank you So much NANI for the Solution
Thank you So much Peter for the Solution.
@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()
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.