4.20 Format conversion - merge multiple primary-sub table files to form two row-based tables
There are multiple primary-sub tables. Now we want to separate the primary tables to form a row-based table, and separate the sub-tables to form another row-based table.
An example of the primary-sub table files is as follows:
Orders1.xlsx:
Orders2.xlsx:
Orders3.xlsx:
The results after separating are as follows:
Orders.xlsx：
OrderDetails.xlsx：
Script:
| A | B | |
|---|---|---|
| 1 | =directory@p(“Orders/Order*.xlsx”).(file(~).xlsopen()) | |
| 2 | =create(OrderID,OrderDate,CustomerID,EmployeeID,Consignee,ShipAddress) | |
| 3 | =create(OrderID,ProductID,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice) | |
| 4 | for A1 | =A4.xlsimport@t(;1,6).select(ProductID).derive(A4.xlscell(“B1”):OrderID) |
| 5 | >A2.insert(0,A4.xlscell(“B1”):OrderID,A4.xlscell(“E1”):OrderDate,A4.xlscell(“B2”):CustomerID,A4.xlscell(“E2”):EmployeeID,A4.xlscell(“B3”):Consignee,A4.xlscell(“B4”):ShipAddress) | |
| 6 | >A3.insert@f(0:B4) | |
| 7 | =T(“Orders/Orders.xlsx”,A2) | |
| 8 | =T(“Orders/OrderDetails.xlsx”,A3) | |
A1: List the .xlsx files starting with Order in the Orders directory and open them one by one
A2: Create the table sequence of primary tables
A3: Create the table sequence of sub tables
A4: Loop through the files in A1 one by one
B4: Read the data starting from the sixth row in the file, return them to the table sequence, and add the column OrderID
B5: Read the data of the primary tables in the file one by one, and insert them into A2
B6: Insert the data of sub tables read in B4 into A3
A7: Write the table sequence of primary tables created in A2 to the file Orders.xlsx
A8: Write the table sequence of sub tables created in A3 to the file OrderDetails.xlsx
esProc Desktop and Excel Processing
4.19 Format conversion - merge multiple card-style files to form one row-based table
4.21 Aggregate files - same rows and columns
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
