Awk script used for end of term reporting

README.md

This script and example were prepared to show how to use the awk program to split the BlackBoard download to multiple files. This assumes that you selected format of results = By Question and By User, such that each row represents a single question for a single student. See the csv file as an example.

Downloading from BlackBoard (separate row per question)

Go to Grade Center > Full Grade Center > Navigate to the Assignment or test's Column > Click on the down arrow next to the column's title > Select Download Results. Select 'By Question and User'. This gives you a file named Test.downloading.csv

Splitting into individual files

To run the awk program file you can try the following command.

awk -v field=a 'FS=",";
 FNR == 1 { hdr = $0 ; next }
  a != $1 { a = $1; close(name); name = $1"_"$3"_"$2".csv";  gsub(/"/,"",name); print hdr >> name }
          { print $0 >> name; } ' Test.downloadlong.csv

or, if you save the awk file below, you may run it with the following command where -f specifies the program file. Replace the final part with your csv input file.

awk -v field=a -f bb_downloadlong_split.awk Test.downloadlong.csv

This command is repeated below with comments for readability:

awk -v field=curr '
# This program splits the input csv file into multiple files, based on the value in the first column. 
# We assume that the first column is in sorted order so that the same value is consecutively arranged.

    FS=",";                                  # Define the delimiter in the input file
    
# Define rule for header
    FNR == 1 {hdr = $0 ; next}

# Define rule for when value changes
    curr != $1 { curr = $1;                  # save value of first column to variable a for comparison
              close(name);                   # close the last file before creating a new one
              name = $1"_"$3"_"$2".csv";     # create filename,i.e. 12345678_Mickey_Mouse.csv
              gsub(/"/,"",name);             # remove the double quotes from variable name
              print hdr > name}              # print the header to the new file
              
# Define rule for all rows              
            { print $0 > name; }             # print out the full row ($0) to the filename
   ' Test.downloadlong.csv

References

• Aho, Alfred V.; Kernighan; Weinberger, Peter J. (1988). The AWK Programming Language. Addison-Wesley Publishing Company. ISBN 9780201079814.
• Wikipedia contributors. "AWK." Wikipedia, The Free Encyclopedia. Wikipedia, The Free Encyclopedia, 24 May. 2020. Web. 27 May. 2020.
• http://awklang.org/
• https://www.oreilly.com/library/view/mac-os-x/0596003706/re156.html
• https://www.grymoire.com/Unix/Awk.html#toc_Table_of_Contents

bb_downloadlong_split.awk

# To run the awk program file you can try the following command. 
# awk -v field=a -f bb_downloadlong_split.awk Test.downloadlong.csv

# This program splits the input csv file into multiple files, based on the value in the first column. 
# We assume that the first column is in sorted order so that the same value is consecutively arranged.

    FS=",";                                  # Define the delimiter in the input file
    
# Define rule for header
    FNR == 1 {hdr = $0 ; next}

# Define rule for when value changes
    curr != $1 { curr = $1;                  # save value of first column to variable a for comparison
              close(name);                   # close the last file before creating a new one
              name = $1"_"$3"_"$2".csv";     # create filename,i.e. 12345678_Mickey_Mouse.csv
              gsub(/"/,"",name);             # remove the double quotes from variable name
              print hdr > name}              # print the header to the new file
              
# Define rule for all rows              
            { print $0 > name; }             # print out the full row ($0) to the filename

Test.downloadlong.csv