How to split text and add sequence no in another column in Datatable in Linq Query? Please Help

Help
1

Please help me to solve this.
I need to split text by 4 characters in col 2 and add sequence nos in col 3 in Datatable.

What I have:
Datatable:
image

What I need:
In a New Datatable:
image

Since dealing with datatable with large data, I am trying in LinQ Query instead of For Each loop:

Please help me

1 Like
2

@GuhanTM Can you check this workflow :
JoinDatatables.xaml (12.6 KB) (Don’t Mind the File Name) :sweat_smile:

The Output is Expected. But it does make use of 1 For Each Row. But check if you find this useful. I’ll also check to see if it can be implemented in a Single Query and try to implement it, but mostly we’ll need to use a For Each.

If it is acceptable. Check for Different Data and provide a Feedback if have found any errors.

1 Like
3

@GuhanTM
If a certain complexity is allowed then it can be done with the help of cartesian product and tuple within a single LINQ:

(From d In dtData.AsEnumerable
Let seg = Enumerable.Range(0,CInt(Math.Ceiling(d(1).ToString.Length)/4)).toArray
From t In seg.Select(Function (x) Tuple.Create(x, New String(d(1).ToString.Skip(x*4).Take(4).toArray))).toArray
Let ra = New Object(){d(0),t.Item2, t.Item1+1}
Select dtResult.Rows.Add(ra)).CopyToDataTable

is done on a cloned / prepared DataTable

and gives following output:
grafik

Find demo XAML here:
CreateColValSplits_AddSplitCountInfo.xaml (7.4 KB)

2 Likes
4

@supermanPunch, Thanks for your reply.
It is not capturing the last split for the below input. How to rectify it? Please help me

image

5

@GuhanTM
it is not taking the last one as CINT will take the lowest Int from the division double does mean:
2,5 to int = 2

Have a look to my approach provided above it is handled with the Math Ceiling statement

1 Like
6

@GuhanTM Yes. :sweat_smile: As @ppr mentioned I need to make some minor changes in the tempArray assignment. If you still want to use the Solution that I have provided, you can replace the value provided for tempArray with the below :

Enumerable.Range(0,Cint(Math.Ceiling(row(“Col 2”).ToString.Length/4))).Select(Function(x)String.Join(“”,row(“Col 2”).ToString.Skip(x*4).Take(4).ToArray)).ToArray

That should be able to Solve it. But again test it with all possible data.

7

@GuhanTM Also what would you want to do if the Column Value is Empty like in the below Screenshot :
em

The FF row has no Col 2 value.

8

@supermanPunch, Empty values will be removed before applying the sequence No, hence no blanks will be available in Col 2.

1 Like
9

@ppr, Thanks for your reply. It is not capturing the last split for the Col 2 value “Sample Tex”. Kindly help me.

10

@GuhanTM What is the Output you get when you use it ?
I have updated my Previous Workflow to use only a Single Linq Query for the operation. Check if it meets your requirements :

JoinDatatables.xaml (16.5 KB)

11

@GuhanTM
when i am running my xaml with input:
grafik

result is:
grafik

So I can’t identify a missing split. On what are you refering?

12

@ppr Use Col 2 text as “Sample Tex”, and by using, last split of ‘ex’ is not get captured in the output.

13

show input table

14

@ppr, Please find the input Datatable
image

15

@GuhanTM Can you check if that data works with the workflow I have provided :sweat_smile: It worked for me. Can you confirm in your case as well ?

16

@GuhanTM
it looks like a inner Bug in LINQ execution, as isolated statements are working correctly. go for this workaraound:

(From d In dtData.AsEnumerable
Let seg = Enumerable.Range(0,(d(1).ToString.Length\4)+1).toArray
From t In seg.Select(Function (x) Tuple.Create(x, New String(d(1).ToString.Skip(x*4).Take(4).toArray))).toArray
Let ra = New Object(){d(0),t.Item2, t.Item1+1}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Edited:

(From d In dtData.AsEnumerable
From s In Enumerable.Range(0,CInt(Math.Ceiling(d(1).ToString.Length / 4))).toArray
Let t = New String(d(1).toString.Skip(s*4).Take(4).toArray)
Let ra = New Object(){d(0), t, s+1}
Select dtResult.Rows.Add(ra)).CopyToDataTable

doesn’t confuse from following from

1 Like
17

@ppr Both Linq queries seems to be same. Have you posted the uploaded linq query

18

had a paste issue. Just refer to the edited post: e.g the length \ 4)+1 part is reworked

19

@supermanPunch I checked with sample data and it worked well. Thanks a lot for the code.

1 Like
20

@ppr if we use exactly multiples of 4-character length text ex: (Samp, SampText,…) then one more additional row got inserted in the output. Also the sequence number is starting with 0 instead of 1.

ex:
Input DT
Col1 Col2
AA 1234
EE 12345678

Output DT
Col1 Col2 Col3
AA 1234 0
AA          1

BB 1234 0
BB 5678 1
BB          2

But the Required Output is:
Col1 Col2 Col3
AA 1234 1

BB 1234 1
BB 5678 2

Please help me