...

Group an Excel table and store as multiple files according to part of value of the specified cell

 

The Excel table below is ordered by column A, whose values are strings separated by "-". The first part of column A represents the category.


A

B

C

D

E

F

G

H

I

J

1

AA-01-02

9

7

2

3

5

7

7

6

9

2

AA-01-03

8

6

6

2

2

1

2

10

8

3

AA-01-04

3

2

3

6

6

7

9

8

8

4

BB-01-05

4

5

2

8

8

6

1

5

1

5

BB-01-06

7

6

1

2

2

9

6

2

5

6

BB-01-07

3

3

7

8

8

7

5

7

5

7

CC-01-08

10

8

6

6

6

7

3

2

7

8

CC-01-09

5

3

7

2

2

1

2

3

6

9

CC-01-10

10

2

2

2

3

9

8

6

10

Task: Store the table as multiple tab-separated txt files according to different categories. The file name format is Group_Category.txt.

Group_AA.txt

AA-01-02 9 7 2 3 5 7 7 6 9

AA-01-03 8 6 6 2 2 1 2 10 8

AA-01-04 3 2 3 6 6 7 9 8 8

Group_BB.txt

BB-01-05 4 5 2 8 8 6 1 5 1

BB-01-06 7 6 1 2 2 9 6 2 5

BB-01-07 3 3 7 8 8 7 5 7 5

Group_CC.txt

CC-01-08 10 8 6 6 6 7 3 2 7

CC-01-09 5 3 7 2 2 1 2 3 6

CC-01-10 10 2 2 2 3 9 8 6 10

Use SPL IDE to execute the code:

=T@b("data.xlsx").group(#1.split("-")(1);~).(T@b("Group_" / #1 / ".txt": #2))

The T()function reads files as a table and writes a table to files; @b means that column names are not included. group() function groups rows according to the specified rule and retains the detail data in each group; ~ is the current member of the sequence, and #1 is the 1st column of the table.

Source:https://stackoverflow.com/questions/78229428/how-do-i-save-text-files-by-group-using-the-area-code-of-the-account-number


 
 •  71 View  •