This article is for administrators of Zócalo. A run-through by Jen Holguin is recommended.
Prepare your Data: Alumni (from the Hyperion weekly report):
1) Delete last row in spreadsheet- it contains invalid data;
2) Open “Symplicity Alumni Update Template” and replace the data in the worksheet entitled, “Report” with your Hyperion report;
3) Save the spreadsheet on your desktop as a CSV (comma delimited) document type (Note: once you’ve done this, you can continue to save the document, but don’t close and re-open it. The CSV format does not maintain the extra zeros in front of the Student ID number. If you somehow do lose this formatting, highlight the Student ID column and right click. Choose “Format Cells.” In the “Number” tab, choose “Custom.” Replace the 0.00 in the “Type” field with 8 zeros, no decimals);
4) In Zócalo, open up any student record under the “Student” module. In the “Flags” box on the right side of the screen, type in a new flag name and click on “Add.” A good protocol would be your initials, followed by the date, followed by a short label if you’re doing more than one upload that day (e.g. “LM 1.1.10 Alumni”);
5) In the spreadsheet, change the column header labeled “Flag” to the new flag name you created in the student record.
The following steps are now incorporated into the Excel templates (“Symplicity Student Upload Template” and “Symplicity Alumni Upload Template” on the S drive. They are saved here in case the template ever becomes corrupted or in case we need to refer to the list of changes accomplished by the template:
6) Add three columns: “Alumnus(a),” “Applicant Type” and “Flag.” Copy “Yes” in the Alumnus(a) column for all records, and copy “Alumni” in the Applicant Type column for all records. In the Flag field, copy today’s date for all records. This will allow you to find the records you’ve uploaded (and batch delete them if needed!);
7) Insert the following formula in the Alumnus Degree field: =IF(Report!K2=”||||”,””,Report!K2). This will delete the “||||” from all of the empty data records;
8) Note that you could delete the extra columns (those denoted with *No Match* in the table below), but this is not necessary;
9) Save your changes.
Prepare your Data: Students:
1) In Hyperion, run the MIIS Symplicity Download report under Monterey, Student, General Student. The full term (ex. 201198) must be selected. Select Statuses AS, LP, PG and TW (see key below). Select all levels and click “Download to Excel.”
2) Open “Symplicity Student Update Template” and replace the data in the worksheet entitled, “Report” with your Hyperion report;
3) Save the spreadsheet on your desktop as a CSV (comma delimited) document type (Note: once you’ve done this, you can continue to save the document, but don’t close and re-open it. The CSV format does not maintain the extra zeros in front of the Student ID number. If you somehow do lose this formatting, highlight the ID column and right click. Choose “Format Cells.” In the “Number” tab, choose “Custom.” Replace the 0.00 in the “Type” field with 8 zeros, no decimals);
4) In Zócalo, open up any student record under the “Student” module. In the “Flags” box on the right side of the screen, type in a new flag name and click on “Add.” A good protocol would be your initials, followed by the date, followed by a short label if you’re doing more than one upload that day (e.g. “LM 1.1.10 Alumni”) (Lynn reports that you don’t have to do this, you can skip to step 3);
5) In the spreadsheet, change the column header labeled “Flag” to the new flag name you created in the student record.
The following steps are now incorporated into the template. They are saved here in case the template ever becomes corrupted or in case we need to refer to the list of changes accomplished by the template:
6) Hide or Delete the Concentration 1 (code) and Concentration 12 (code) fields to make viewing easier;
7) Insert a new column after Concentration 12 (desc) labeled Language;
8) Custom sort by Concentration 1 (desc) and then by Concentration 12 (desc);
9) Cut and paste all of the language information from these two columns (the data should be in either one or the other column) into the new Language field you’ve created. For GSIPM students, the format is generally “Language of Study-Spanish,” while for T&I students you will see “English-Spanish”;
10) If any data is left in the Concentration 12 (desc) column (there shouldn’t be any), cut and paste it into Concentration 1 (desc);
11) Insert a column after the Major (desc) column and entitle it, “Degree.” The degree name is a combination of the Program (desc) column and the Major (desc) column. To achieve this, use the Concatenation function. In Row two, type, “=CONCATENATE(P2,” “,R2)” for results that should look like this: “TILE Master of Arts TESOL.” Copy this formula to all records;
12) Add three columns at the end of your spreadsheet: “Alumnus(a),” “Applicant Type” and“Flag.” Copy “No” in the Alumnus(a) column for all records, and copy “Current Student” in the Applicant Type column for all records. In the Flag field, copy today’s date for all records. This will allow you to find the records you’ve uploaded (and batch delete them if needed!);
13) Note that you could delete the extra columns (those denoted with *No Match* in the table below), but this is not necessary;
14) Save your changes.
Import your Data: Students and Alumni:
1) Go to “Tools,” then “Import Data” from the left-hand tool bar;
2) Click on the “Symport” tab;
3) From the “Import Object” drop-down menu, choose “Students;”
4) A new drop-down menu will appear, entitled “Import Data.” Click on “Browse” to locate your spreadsheet file.
5) Once you have selected your file, a number of new fields will appear. In the first field, “File starts with Headers row?”change to “Yes;”
6) “File Type” should be “Delimited;”
7) “Delimiter” should be “Comma;”
8) “Treat consecutive delimiters as one?” should say “No;”
9) Click on “Upload” to be taken to the field mapping screen. The mapping should match up as follows. Note that as of summer 2011, the Banner Student ID now goes in the Alumni ID field and the Email goes in both the Email field and the Student ID field in Zócalo. This is already reflected in the upload template:
Alumni:
| Report Fields | Zócalo Fields |
| Alumni ID
Student ID |
Alumni ID
Student ID |
| ConstituencyGroup | Student Profile: Alumnus Degree |
| ClassYear | Student Profile: Alumnus Graduation Date |
| Last Name | Last Name |
| First Name | First Name |
| Preferred First Name | *No Match* |
| Middle Name | MI |
| Gender | Gender |
| Language of Study | Student Profile: Language of Study |
| Degree Long Description | Alumnus Degree |
| Alumnus(a) | Alumnus(a) |
| Applicant Type | Applicant Type |
| Flag | Flag |
Students:
| Report Fields | Zócalo Fields |
| Term | *No Match* |
| Id | Student ID |
| Last Name | Last Name |
| First Name | First Name |
| Preferred First Name | *No Match* |
| Middle Name | MI |
| Gender | Gender |
| Admit Term | Student Profile: Semester Admitted |
| Grad Term | Student Profile: Graduation Date |
| Last Term Attended | *No Match* |
| Status | *No Match* |
| Registered for Term | Student Profile: Registered for Semester? |
| Level | *No Match* |
| Program | *No Match* |
| Program (desc) | *No Match*(do not delete column) |
| Major (code) | *No Match* |
| Major (desc) | *No Match*(do not delete column) |
| Degree | Major |
| Concentration1 (desc) | Track |
| Concentration12 (desc) | *No Match* |
| Language | Student Profile: Language of Study |
| Primary Academic Advisor | Student Profile: Primary Advisor |
| Advisor 2 | Student Profile: Secondary Advisor |
| Advisor 3 | *No Match* |
| Advisor 4 | *No Match* |
| Type | *No Match* |
| Camp Code | *No Match* |
| Program 2 (code) | *No Match* |
| Program 2 (desc) | *No Match* |
| Major 2 (code) | *No Match* |
| Major 2 (desc) | *No Match* |
| Concentration 2 (code) | *No Match* |
| Concentration 2 (Desc) | *No Match* |
| Concentration 22 (code) | *No Match* |
| Concentration 22 (desc) | *No Match* |
| Admt Code | *No Match* |
| Admt Desc | *No Match* |
| Alumnus(a) | Alumnus(a) |
| Applicant Type | Applicant Type |
| Flag | Flag |
10) Once all fields are mapped, scroll to the far right and select “Key” for the “Student ID” row;
11) Select “Preview;”
12) If ANYTHING other than the message, “Student Match Found: (Name of first alumni record)” appears at the top of the pop-up preview screen, go back and re-map- something didn’t work! If it says “Duplicate Record Found,” for example, then it will create duplicate records in the system for each alumnus- a very messy thing to clean up!
13) Click on “Import Current.” If this is successful, the “Import All” button will appear. Click on this. I noticed with my last student import that the Major field data changed between the “Preview” view (correct degree info was shown) and the “Import Current” (“0048”). I’ll investigate, but the information that imported when I clicked “Import All” was correct.
14) You’re done! If uploading the entire alumni spreadsheet, your job may be queued, and you’ll receive an email when the job is complete.
Hyperion Dashboard Code Key for Student Reports:
| Code | Description |
| Term (note, if term doesn’t end in 8, it is used by Midd) | |
| 201028 | Spring 2010 |
| 201068 | Summer 2010 |
| 201098 | Fall 2010 |
| 201078/38/88 | Summer 2010 Language/Other |
| Status (AS, LP, PG and TW students should be uploaded to Zócalo) | |
| AS | Active/Admitted |
| IS | Inactive |
| LP | Leave for Peace Corps |
| NF | Forfeited deposits |
| PG | Pending grads (includes incomplete Boren’s, etc.) |
| TW | LOA’s, DLI TFL students (who may take courses intermittently) |
| WP | Withdrawals |
| Level (include all) | |
| CG | California graduate student |
| CN | California non-graduate student |
| Type (column in report, not on dashboard) | |
| I | Incoming/Initial Term |
| C | Continuing |
| R | ? |
