...

Extract column-direction data in Excel

 

It is very common that the records of Excel data tables are in the row direction, that is, all data of one record are in the same row. But there are also some special Excel tables where the data records are located in the column direction. To extract data from this kind of table, we need to first read the data into a sequence of sequences, then use the transpose function to convert it into a common row-based table, and then use it to create a data set.

 

Example

The data of the course start date table book1.xlsx is shown in the figure below:

..

The SPL script is:

A

1

=file("E:/work/book1.xlsx").xlsimport@w()

2

=transpose(A1)

3

=create(${A2(1).concat@c()}).record(A2.to(2,).conj())

A1 Import the data of book1.xlsx, and the @w option means to read the data as the sequence of a sequence, as shown in the figure below:

..

A2 Convert the sequence of A1 from row to column, and the result is shown in the figure below

..

A3 Use the first member in A2 as the column name to create the table sequence, and the other members are appended to the table sequence as records.


 
 •  223 View  •