Several people have inquired about the ability to export their class rosters from Skyward in order to:
- create distribution lists in Outlook for emailing their students
- apply rights to files and folders with Google Drive
In this guide, I’ll show you how to export a class roster and convert student email addresses from that roster into copy-and-paste friendly lists for both Google Apps and Microsoft Outlook.
While this guide outlines specific processes for manipulating class roster data exported from Skyward, the ability to take text and reformat it to suit your needs is the core skill you should take away from this. That ability is useful in many scenarios outside the scope of this guide.
Exporting a Class Roster from Skyward
- Log into Skyward (D155 users, go to skyweb.d155.org).
- Open your gradebook (below, left), then click the Gradebook link that corresponds to the class you want a roster for (below, right). In this example, I’ll select the link corresponding to my 1st period Biology class.
- Click the Reports menu, then click Class Roster (as seen below).
- Select template “900 Student Name & Student Info” then click the Export to Excel button (as seen below).
- The report will be queued by the server. Once the report is completed, click Display Report (below, left). This will download an Excel file containing the data to your default download location (usually your desktop or your “Downloads folder”). The file will have a seemingly random alphanumeric string for a name (in my example “SKR9098944U7R8N9Y1181434”, below, right)
- Open up the Excel file by double-clicking it. You may be prompted with a dialog box informing you that the file you are trying to open is in a different format than specified by the file extension. If you get this error (as seen below), click Yes.
- Your roster data should now be open in Microsoft Excel (as seen below).
- Rows 1-2 contain information about the report.
- Rows 4-5 contain information about the class (teacher, course, section, room, etc.).
- Row 7 is the column header row, containing the titles for each of the columns of student data.
- Rows 8-n (depending on the number of students) contain columns of various student data (depending on the report template).
- At this point, I’d recommend saving the data in the standard Excel format, with a more applicable title than it has currently. In this example I’m using Office 2012; the process in earlier versions should be very similar, but may vary slightly.
- Click File then click Save As (as seen below).
- Select an applicable save location (in my example I’m using the desktop) and give the file an appropriate name by typing it into the “File name” text box (here I’m calling the file “Bio_Roster_P1”).
- Below the “File name” text box is a drop-down menu titled “Save as Type”. The file you exported from Skyward will be in the “XML Spreadsheet 2003 (*.xml)” version. Click this box and select Excel Woorkbook (*.xlsx) to save the file using the standard Excel file format (as seen below).
- Click Save to save the data in the new file name and type.
- Now that you have the data with the desired name and format, I’d suggest closing Excel and deleting the original file that you exported from Skyward (in this case “SKR9098944U7R8N9Y1181434.xls”). This will avoid confusion later and eliminate duplicate data.
- Click File then click Save As (as seen below).
Creating “Copy-and-Paste” Student Lists
You have your class list in a spreadsheet. This format is great for keeping the data organized, but if you want to send an email to or assign roles in Google Drive for your students, you’ll need a single line, delimited list of student email addresses.
Google uses comma delimited lists. If your organization is fully immersed in Google Apps, a comma delimited list is all you’ll need. If you are using Outlook for staff email however (as we are at District 155), you’ll also need to keep a semicolon delimited list (Outlook doesn’t recognize the comma as a separator).
The one I’m going to use in this example is textmechanic.com; specifically the Add-Remove Line Breaks function. This page allows you to paste the email address data fields from Excel (which will be line break delimited), perform a function to replace the line breaks with commas (and/or semicolons), and copy the resulting single-line list to be pasted back into your Excel file. It might sound complicated, but it is just a few mouse clicks.
- Open your Excel roster (in this example, “Bio_Roster_P1.xlsx”) and drag a box to highlight only the email address column for only the rows that contain your student data (as seen below). Once highlighted, right-click the data then left-click Copy from the contextual menu (as seen below).
- Leave your excel window open, we’ll use it to paste our reformatted data into shortly. Open your web browser (you are probably already in it if you are reading this) and go to the Add-Remove Line Breaks page mentioned above.
- Clear all the text in all fields by clicking the C button above the input box (above, A).
- Paste the student emails that you just copied from Excel into the input box (above, B).
Note: You may find the cursor on a blank line at the bottom of your data. If so, hit the Backspace key on the keyboard to remove it. The cursor should be at the end of the bottom email address before proceeding.
- Enter “, ” (comma followed by a single blank space) in the top-right field (above, C). In this box, you are specifying the delimiter to be used in between data (replacing the current delimiter, the line break).
- Click the Remove All Line Breaks button (above, D).
- The output box will contain a single line of every email address you provided, separated by commas (above, E).
- Click the S button to highlight the output text (above, F).
- Right-click the text, then left click Copy from the contextual menu (above, G).
- You now have all the email addresses from your class (separated by commas) in your clipboard. You’ll need to put that information in an appropriate and easy to access location. In this example, I’m going to put it right back into my existing Excel document.
- If you still have your Excel from step 1, switch back over to Excel. Leave your web browser open to the textmechanic web page (don’t clear out your data from the form; we’re going to use it again). If you closed Excel, reopen your file at this time.
- Enter “Google List” into column A on an available row below your class list. In my example, I left a blank line to separate my lists from the exported data.
- Paste the data from your clipboard into column B of the same row (as seen below).
- Save your changes (Remember this is Excel not Google Sheets. You still need to tell it to save changes).
- If you are intending to send email using Outlook or Outlook Web Access, you’ll need the same list separated by semicolons instead of commas.
- Go back to your the textmechanic “Remove Line Breaks” page in your web browser (it should still have your data in it).
- Repeat steps 2C through 2G above, instead using “; ” (semicolon followed by a single blank space) for step 2C.
- Repeat steps 3A through 3D, using the next available row, entering “Outlook List” (instead of “Google List”) in Column A of step 3B (as seen above).
You now have lists you can easily copy and paste when sharing items with your class using Google Drive, or when sending an email using Outlook (if needed). Just click the appropriate cell (containing the list), copy it, and paste it into the appropriate location. Repeat the above process for the rest of your classes. If you are storing your class-related files in Google Drive, you could upload and convert this roster to Google Sheet, then file it in an applicable location in your drive; you could have access to your roster from anywhere!
If you have any questions or comments about this process, please leave a reply below or as always just stop by and see me.