11.7 Traverse all files in a directory

 

Traverse all files under a specified directory recursively.
According to the following survey table of a primary school’s online learning terminals, calculate proportion of each terminal.

imagepng

ID STUDENT_NAME TERMINAL
1 Rebecca Moore Phone
2 Ashley Wilson Phone,PC,Pad
3 Rachel Johnson Phone,PC,Pad
4 Emily Smith PC,Pad
5 Ashley Smith PC
6 Matthew Johnson Phone
7 Alexis Smith Phone,PC
8 Megan Wilson Phone,PC,Pad

SPL has directory@s() function to search for files recursively.

SPL script:

A B
1 =directory@ps(“D:/Primary School”)
2 >totalCount=0
3 for A1 =T(A3)
4 =B3.conj(TERMINAL.split@c())|B4
5 >totalCount+=B3.len()
6 =B4.groups(~:TERMINAL;count(~)/totalCount:PERCENTAGE)

A1 Traverse all files in the directory and list them.
A2 Define variable totalCount for storing total records.
B3 Import the Excel survey files in loop.
B4 Split each TERMINAL value according to comma and concatenate them to B4’s sequence.
B5 Add number of students in each class to totalCount.
A6 Group B4’s sequence by terminal and calculate proportion of each terminal.

Execution result:

A1:

Member
D:\Primary School\Grade1\Class1\Investigation.xlsx
D:\Primary School\Grade1\Class2\Investigation.xlsx
D:\Primary School\Grade1\Class3\Investigation.xlsx

B4:

Member
Phone
Phone
PC

A6:

TERMINAL PERCENTAGE
PC 0.7
Pad 0.567
Phone 0.933