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:

imagepng

Orders2.xlsx:

imagepng

Orders3.xlsx:

imagepng

The results after separating are as follows:

Orders.xlsx:

imagepng

OrderDetails.xlsx:

imagepng

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