Splitting column into multiple columns- no delimiter

[haribabugv]

Hello All,

Is there any way to split a column into multiple columns? I cannot use subfield because there is no identifier.

I need to divide a column into multiple columns using number of characters. Every 3 characters belong to column

Input:

 my data structure will look this 

Product Code	Column1	Column2
A11	00A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A	00A00A00E00A00A00A00C00A00A00E00C00A00A00E00C00C00C00A
B11	00B00D00B00D00A00A00C00A00D00B00F00C00A00D00D00F00D00C	00G00G00E00G00G00G00H00G00G00E00H00G00G00E00H00H00H00G

00A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A

Each cell has 54 characters in one column

Now need to show in output, 18 columns with 3 characters in each cell.

Let me know if i can use subfield here as i am not sure what will be the delimiter.

Expected Output

Product

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

A11

Column1

00A

00A

00B

00A

00A

00A

00C

00A

00A

00B

00C

00A

00A

00B

00C

00C

00C

00A

A11

Column2

00A

00A

00E

00A

00A

00A

00C

00A

00A

00E

00C

00A

00A

00E

00C

00C

00C

00A

B11

Column1

00B

00D

00B

00D

00A

00A

00C

00A

00D

00B

00F

00C

00A

00D

00D

00F

00D

00C

B11

Column2

00G

00G

00E

00G

00G

00G

00H

00G

00G

00E

00H

00G

00G

00E

00H

00H

00H

00G

Every 3 characters form a cell in the output

00A

00A

00B

00A

00A

00A

00C

00A

00A

00B

00C

00A

00A

00B

00C

00C

00C

00A

Thanks a lot 

[JaMajka1 · Accepted solution]

Hello Haribabugv,

I am sending you a simple script for splitting columns by using fixed number of characters. You just have to transform the data in the structure you need, if you want to have them in columns.

[input]:
load * Inline
[productcode,data
A11,00A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A
B11,00B00D00B00D00A00A00C00A00D00B00F00C00A00D00D00F00D00C
];

[output]:
noconcatenate
load
productcode,
rowno() as row_id,
mid(data,iterno()*3-2,3) as X
resident input
while iterno()<19;

drop table input;

Hope it helps,

Maria 🙂

[Zhandos_Shotan]

Hi!

First, use Crosstable load to create Column1, Column2... dimension. Then define 18 fields with Mid() function.

Tab1:

CrossTable(Columns, Data) Load

*

from Split.xlsx;

Load

Product Code,

Columns,

Mid(Data, 1,3) as "1",

Mid(Data, 4,3) as "2",

Mid(Data, 7,3) as "3",

...

Mid(Data, 51,3) as "18",

Resident Tab1;

Its obvious and simple way but not smart 

[haribabugv]

Thanks Maria, it worked