Datatable split column by fixed length & assign header

Help
1

Suppose I have a datatable with 1 column called “FullData”, the value is as below:
(note there is space in the data)

Header: FullData
1st row: JohnnyM1519880201
2nd row: MaryF 719901222
3rd row: JasonM 219781122

I would like to seperate the datatable to 4 columns by length of character and assign a header to each seperated column, how should I do?

E.g.:
New header: Name (length = 6) || Sex (length = 1) || Age (length = 2) || Date (length = 8)
(note, the space is removed after it seperated to columns)

1st row: Johnny || M || 15 || 19880201
2nd row: Mary || F || 7 || 19901222
3rd row: Jason || M || 2 || 19781122

2

@Haha

As per your input, This kind of output is not possible. Because length of the name will change every time. you specified name length 6 but if you see length of the second row name is 4. If is there any space between that text then its possible to do that.

Eg: Input: Johnny M 15 19880201

In this case, we can split text based on space as delimiter.

2 Likes
3

The character is fixed ( include space as 1 character)
Therefore for 2nd row, there are 2 space before Mary in my original datatable column, making it 6 character.
Same as the age column, there is a space before the age if age <10, making it to have fixed length of 2 character

If I do it by excel, I can just use text to column with fixed length delimiter to seperate the columns, but datatable I am not sure how to do

1 Like
4

Hey @Haha

It is possible to get the output what you are expecting. You can’t split it from Left to Right but you can do it from Right to Left. like First extract Date and then Age and then Sex at last Name. In this way we no need to bother about Name length.

Attached workflow which will give you desired output and which will write into sheet2 of SampleInput file.

SplitDataAndStoreInExcel.zip (17.5 KB)

Hope this will help you. :slight_smile:

Regards,
Vijay.

5

Hi @Haha

Did you tried with above code. is it working as you expected …?

Regards,
Vijay.

1 Like
6

Just back from holiday sorry for the late reply.
Yes!! This is exactly what i want, thanks so much!

7

You are welcome … Happy Automation … :slight_smile:

8

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