"In some Excel files, the data is presented in the form of a primary-sub table, the data of the p .."

pjf RaqForum 22 No.
1 Reply • 320 View • 3 Years ago

Extract data of mixed-style structure in Excel

In some Excel files, the data is presented in the form of a primary-sub table, the data of the primary table is in free format, and the data of the sub-table is stored in a row-based structure with one record per row, and the number of rows is unfixed. When extracting data from such files, we need to append the primary table information to each sub-table record.

Example

The data in the order detail table order.xlsx is shown in the figure below:

The SPL script is:

	A
1	=file("E:/work/order.xlsx").xlsopen()
2	=A1.xlsimport@t(;1,5).select(Model)
3	=A2.derive(A1.xlscell("D2"):Name,A1.xlscell("F2"):Phone,A1.xlscell("D3"):Email,A1.xlscell("D4"):Address)

A1 Open the order.xlsx file as an Excel object.

A2 Read the row-based data starting from the fifth row in the first sheet of A1, and filter out rows where Model is null. The @t option means that the first row is column headers.

A3 Add 4 columns to A2: The value of Name column is the content of cell D2, the value of Phone column is the content of cell F2, the value of Email column is the content of cell D3, and the value of Address column is the content of cell D4.

The final result of A3 is shown in the figure below: