...

Split different types of characters from texts

 

Example

There is the attendance data shown in the following figure in the Excel file book1.xlsx. A certain employee has multiple attendances on a certain day. For example, A means late, the number after A means late time, B means early leave, and the number after B means early leave time , C means leave, the number after C means leave time, and so on. Summarize various situations, respectively summarize the numbers behind each letter, and fill in the Total area in the figure.

undefined

Write SPL script:

A

1

=clipboard().split@t("\t")

2

=create(type,value)

3

=A1.(~.words@wp().run(if(#%2==1,A2.record([~,number(~[1])]))))

4

=A2.groups(type;sum(value))

5

=clipboard(A4.export())

A1 After reading the data from the clipboard, the text in each cell is split into a sequence with \t as a separator, and the option @t means to delete the blanks on both sides after splitting

 

A2 Create a table sequence with two columns of type and value

 

A3 Loop the text of each cell and split the text into words. The option @w means to split all characters, Chinese characters or symbols into single characters, and English or numbers into words; option p means that the beginning of the data will be recognized as a whole , Number or date. Loop through the sequence with the run function, if the current member number is singular, take the current member and its next member and convert it into a value to form a record and save it in A2

 

A4 Group A2 by type and calculate the sum of value

 

A5 Convert A4 into text and put it on the clipboard

 

Open the file book1.xlsx, select data A3:G3, and then press Ctrl+C to copy to the clipboard. Run the SPL script in esProc, return to the Excel file, select B7, and press Ctrl+V to paste the calculation results in the clipboard.


 
 •  217 View  •