e2Campus
Instructions to Add New Students and Delete No Longer Enrolled Students
- In 2012 we moved e2Campus to an opt-out model. Therefore, we have the responsibility of getting all currently enrolled students into e2Campus. We also need to remove students who are no longer enrolled.
- In summer 2013 we added new students but did not delete anyone. Mark Vitalos wanted to wait to delete students until fall.
- Employees will continue to enroll themselves. If an employee puts themselves into the student group and they are not registered for the semester they will get removed from that group with our process. According to Security being in the employee group would get them every notification. Students do not get any different notifications than employees.
Upload Currently Enrolled Students for a Semester:
-
We will use a version 2.5 upload file for this process. This file format will only add new data. It will not modify anything. Therefore, if a student has modified their record those modifications will remain.
-
We will add these students to the student group.
-
We are pulling the data to upload from Jenzabar. Some students do not have cell phone numbers listed in Jenzabar. Therefore, we upload two files. One file is currently enrolled students who have a cell phone number and includes their email address. The second file is currently enrolled students who do not have a cell phone number but includes their Cedar Crest email address.
-
Timing: This should be done at the beginning of a semester, at the end of drop and add, and then periodically throughout the semester if we are offering any courses for which a student can enroll after drop and add.
- Use a SQL script called e2campus.sql. It is stored at \\ccccommstorage2\comm_it\Infotech\e2Campus. In the two select scripts change the year and term to the current semester.
- Run the first script. Copy the results with headers and paste into Excel. (Right click in the results pane in the block in the upper left where the rows and columns meet. Choose copy with headers.)
- In Excel save the file as a CSV. Save the file somewhere easy to remember so that you can find it to upload in a later step.
- Run the second script. Copy the results with headers and paste into Excel. (Right click in the results pane in the block in the upper left where the rows and columns meets. Choose copy with headers.)
- In Excel save the file as a CSV. Save the file somewhere easy to remember so that you can find it to upload in a later step.
- Log into e2Campus. Go www.cedarcrest.edu. At the bottom of the page click the site A-Z link. Scroll down to E and click the E2Campus link. Follow the link for Administrative Login. Or go to http://www.cedarcrest.edu/ca/e2campus/e2campus_admin.shtm.
- Choose Tools, then Subscriber Import.
- Use the browse button to find the first file that you created in Step 3.
- Click Upload File when you are ready to start the process. Julie’s email will receive an email with the upload results.
- Repeat to upload the second file.
Delete Records for Students Not Currently Enrolled:
- We use a version 3.5 file format for this file. The file simply contains the command delete_user and the user name of the record being deleted.
- We will export a list of all users in the Student group. Then compare this list to those students registered for the current semester. Anyone in the Student group who is not enrolled will be deleted. As mentioned above if an employee put themselves in the student group they will be removed by this process if they are not enrolled.
- Timing: Run this process after drop/add is over for the Fall and Spring semesters.
- Log into e2Campus. Julie’s account has the rights to the file upload tools. (Maybe Bruce has these rights) Go www.cedarcrest.edu. At the bottom of the page click the site A-Z link. Scroll down to E and click the E2Campus link. Follow the link for Administrative Login. Or go to http://www.cedarcrest.edu/ca/e2campus/e2campus_admin.shtm. Julie’s login is jahobert and 101jahit.
- Choose Tools then Export. Scroll down to Subscribers in Group. Choose the Student group and click the Begin Export button. The file shows up in the Downloads area. Find your file and click on it to get to the Download file button.
- On the NAS in the InfoTech folder in the e2campus folder there is an Access database called e2Campus. Import the file you just saved as a table and call it Student_Group. Choose the text file import. The file is a .csv file with header, comma delimiter and double quote text qualifiers. You can let Access add a primary key.
- Change the year and term in the Currently Enrolled query to the correct year and term for the current semester. Save the changes.
- Run the query called In Student Group but not Enrolled. These are the students who are in e2Campus but are no longer enrolled at Cedar Crest.
- Export the query as a .csv file with no headers, comma delimiters and no text qualifiers. Each row of the file should have the command delete_user, then a comma, and then the user name of the former student. An example is delete_user, jahobert. Save the file somewhere easy to locate. I found that the file exported from Access is in the correct format, but has a .txt extension. I could just change the extension.
- Log into e2Campus as admin.
- Choose Tools, then Subscriber Import.
- Use the browse button to find the file that you created in Step 7.
- Click Upload File when you are ready to start the process. Julie will receive an email with the upload results.
Use these two select scripts to create CSV files that get uploaded into e2Campus.
- The files include only students enrolled for the current semester. The scripts removed employees
- IMPORTANT: Change the year and term in each script as necessary
- Have cell phone numbers
select SUBSTRING(stareml.ADDR_LINE_1, 1, LEN(stareml.ADDR_LINE_1)-15) as user_name, sts.ID_NUM as password,
ltrim(n.FIRST_NAME) as first_name, LTRIM(n.last_name) as last_name,
--CONVERT(varchar(11), GETDATE() + 90, 101) as expire_date,
n.MOBILE_PHONE as phone_1, 'AT&T' as carrier_1,
n.EMAIL_ADDRESS as email_1,
'Student' as group_1
from STUDENT_TERM_SUM sts join ADDRESS_MASTER stareml
on sts.ID_NUM = stareml.ID_NUM and stareml.ADDR_CDE = '*EML' and stareml.ADDR_LINE_1 like '%@cedarcrest.edu'
join NAME_MASTER n
on sts.ID_NUM = n.ID_NUM and n.MOBILE_PHONE is not null --Exclude any records without a mobile phone
join BIOGRAPH_MASTER b --Do not include college employees
on sts.ID_NUM = b.ID_NUM and (b.EMPLOYEE_OF_COLLEG = 'N' or b.EMPLOYEE_OF_COLLEG is null)
where sts.YR_CDE = '2013' and sts.TRM_CDE = '30'
and sts.TRANSACTION_STS in ('C', 'H', 'R')
--and sts.ID_NUM in (1892711, 2020655, 1669040, 1803801)
order by n.LAST_NAME, n.FIRST_NAME
--No cell phone numbers
select SUBSTRING(stareml.ADDR_LINE_1, 1, LEN(stareml.ADDR_LINE_1)-15) as user_name, sts.ID_NUM as password,
ltrim(n.FIRST_NAME) as first_name, LTRIM(n.last_name) as last_name,
--CONVERT(varchar(11), GETDATE() + 90, 101) as expire_date,
n.EMAIL_ADDRESS as email_1,
'Student' as group_1
from STUDENT_TERM_SUM sts join ADDRESS_MASTER stareml
on sts.ID_NUM = stareml.ID_NUM and stareml.ADDR_CDE = '*EML' and stareml.ADDR_LINE_1 like '%@cedarcrest.edu'
join NAME_MASTER n
on sts.ID_NUM = n.ID_NUM and n.MOBILE_PHONE is null --Only include records without a mobile phone
join BIOGRAPH_MASTER b --Do not include college employees
on sts.ID_NUM = b.ID_NUM and (b.EMPLOYEE_OF_COLLEG = 'N' or b.EMPLOYEE_OF_COLLEG is null)
where sts.YR_CDE = '2013' and sts.TRM_CDE = '30'
and sts.TRANSACTION_STS in ('C', 'H', 'R')
--and sts.ID_NUM in (1892711, 2020655, 1669040, 1803801)
order by NUM_OF_CRS