RaqForum 22 No.
1 Reply  •  263 View  • 
     ...

Extract contiguous areas in Excel

 

Example

The data of book1.xlsx is shown in the figure below, where each record consists of 2 cells (the same color as shown in the figure is a fragment). The content of the upper cell in the fragment is the keyword k, and the content of the lower cell is the value of the keyword v. Now, we need to extract the data into a data set with two columns of k and v.

..

The SPL script is:

A

1

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

2

=create(k,v)

3

=A1.step(2,1).conj()

4

=A1.step(2,2).conj()

5

=A3.run(A2.record([~,A4(#)]))

A1 Read the data of book1.xlsx, and the @w option means to read the data as a sequence of sequences.

A2 Create a table sequence with two columns of k and v to store the extracted data.

A3 Retrieve the odd-numbered rows in A1 and concatenate them into a sequence, which is the keyword sequence.

A4 Retrieve the even-numbered rows in A1 and concatenate them into a sequence, which is a sequence of key values.

A5 Loop through each keyword in A3 and combine it with the value of the same sequence number in A4 to form a record in A2.


 
 •  263 View  •