DEV Community

Cover image for #29 — Group an Excel table and Store It as Multiple Files According to Part of The Values of Specified Cells
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#29 — Group an Excel table and Store It as Multiple Files According to Part of The Values of Specified Cells

Problem description & analysis:
The Excel table below is ordered by column A, whose values are strings separated by “-”. The first part of column A represents the category.

original table
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

Group AA

Group_BB.txt

Group BB

Group_CC.txt

Group CC

Solution:
Use SPL IDE to execute the code:



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


Enter fullscreen mode Exit fullscreen mode

Explanation:
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 detailed data in each group; ~ is the current member of the sequence, and #1 is the 1st column of the table.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Don't hesitate to try the FREE trial now⬇️
✨SPL download address: scudata.com/download-Desktop
✨Plugin Installation Method: c.scudata.com/article/1652061135502
✨References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
✨SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Feel free to reach out if you have any Excel tasks. Your problem might be featured in our Excel Hack series!
🫶🏻Discord: discord.gg/hgbKEvJ4
🫶🏻Reddit: reddit.com/r/esProc_Desktop/