Skip to content

Instantly share code, notes, and snippets.

@cptncrnch

cptncrnch/parse-rosters.py

Created August 31, 2017 04:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cptncrnch/7f8176ecaf31928f55ce2be2a7b7f628 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cptncrnch/7f8176ecaf31928f55ce2be2a7b7f628 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. cptncrnch created this gist Aug 31, 2017.
    49 changes: 49 additions & 0 deletions parse-rosters.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,49 @@
    # Parses all Excel files that matches with "R*" in the same directory for names and emails.
    # Creates CSV files by undergraduate or graduate or no email found.
    # Script doesn't account for duplicate entries.
    ###
    import os
    import xlrd
    import csv
    # Open CSV files for writing
    fileUndergrad = open('list_undergrad_withduplicates.csv','wb')
    fileGraduate = open('list_graduate_withduplicates.csv','wb')
    fileNoEmail = open('list_noemail_withduplicates.csv','wb')
    ugrad = csv.writer(fileUndergrad)
    grad = csv.writer(fileGraduate)
    noemail = csv.writer(fileNoEmail)
    # Write headers to CSV files
    ugrad.writerow(['Last Name','First Name','Email'])
    grad.writerow(['Last Name','First Name','Email'])
    noemail.writerow(['UID','Last Name','First Name','Major','Classification','Grade Type','Section / Status'])
    # Begin parsing Excel files with filename matching "R*"
    for file in os.listdir('.'):
    if os.path.isfile(file) and file[0]=="R":
    # Open Roster file and first Sheet
    wb = xlrd.open_workbook(file)
    ws = wb.sheet_by_index(0)
    # Initial row = 2, Initial col = 1, 2, 4
    row = 2
    # Read all filled rows in worksheet (nrows)
    while row < ws.nrows:
    # Split name into Last and First
    fullName = ws.cell(row,1).value.title()
    lastName = fullName.split(',',1)[0]
    firstName = fullName.split(',',1)[1][1:].strip('\"')
    # Make email addresses lower-case
    email = ws.cell(row,2).value.lower()
    # If no email found, write to list_noemail_withduplicates
    if email == "":
    noemail.writerow([ws.cell(row,0).value,lastName,firstName,ws.cell(row,3).value,ws.cell(row,4).value,ws.cell(row,5).value,ws.cell(row,6).value])
    # If undergraduate student and has email, write to list_undergrad_withduplicates
    elif ws.cell(row,4).value[0] == 'U':
    ugrad.writerow([lastName,firstName,email,file])
    # If graduate student and has email, write to list_graduate_withduplicates
    else:
    grad.writerow([lastName,firstName,email, file])
    row+=1
    print "Finished reading %s and writing to csv" % file
    fileUndergrad.close()
    fileGraduate.close()
    fileNoemail.close()
    print "Complete rosters written to csv"