Imports & Exports
ConexED is an enterprise software that is designed to be integrated with a Student Information System to reap the benefits of the full suite of ConexED features and services including Case Management. The availability of student data such as contact information, demographics, and meeting history give student service staff the ability to conduct outreach, create appointments, and manage their cohorts with streamlined and unified processes. In this lesson, the IT Professional will learn about the options for importing student data to ConexED and exporting data from ConexED using three different methods. B
Lesson GoalsIdentify two methods of SIS integration.
By the end of this lesson, you should be able to recall the steps for self-service import.
FTP Flat File Import and Export
By far the most standard method of SIS integration is the FTP method that uses a flat file import and export. A comma delimited CSV flat file can be imported and exported with ConexED using a Secure File Transfer Program (SFTP). ConexED creates an SFTP account for your institution with import and export folders and then uses a REST API to import the data you upload to the Import folder and to export the students’ and users’ meeting data to the Export folder.
CSV Import Data
ConexED imports data using a standard comma delimited file format. All data fields are text and do not need to be wrapped in single or double quotes. A single header row is required. Table 1 illustrates the required and optional data that can be imported for the purpose of student services. Additional optional data can be imported by adding more columns with column headers. The example data table follows the guidelines for OneRoster CSV Users Import Format or users.csv.
Tabel 1
OneRoster CSV Users Table Import Format and Required Data
| Column Header | Required | Description |
|---|---|---|
| partner_userid | Yes | Partner user name. Corresponding to the SAML2, this field is the same as the SAML2 partner username. |
| student_id | Yes | SIS user ID. Unique ID for the user. This ID is from the institution’s Student Information System and is used as a reference key in the ConexED export file. |
| login_id | No | LDAP username |
| first_name | Yes | User’s first name |
| middle_initial | No | User’s middle initial |
| last_name | Yes | User’s surname (used to merge applicant records which are not yet in the SIS) |
| Yes | Email address for the user (used to merge applicant records that are not yet in the SIS) | |
| birthday | No | User’s birthday (can be used to merge applicant records which are not yet in the SIS) |
| phone | No | Phone number for the user (can be used to merge applicant records which are not yet in the SIS) |
An example of the comma delimited data import format is given in Table 2.
Table 2
Example of Comma Delimited Data Format
| partner_userid,user_id,login_id,first_name,last_name,email,phone |
| me123,0000187,mpompa,Michael,Pompa,mpompa@craniumcollege.edu,8013363434 |
| me123,0001176,ihollman,Irene,Hollman,ihollman@craniumcollege.edu,8013343434 |
| me123,0001672,pstone,Pamela,Stone,pstone@craniumcollege.edu,8013353634 |
| me123,0001836,sjames,Stanley,James,sjames@craniumcollege.edu,8016343484 |
| me123,0002071,pgrosse,Perry,Grosse,pgrosse@craniumcollege.edu,8017343434 |
| me123,0002236,vchavez,Victor,Chavez,vchavez@craniumcollege.edu,8013348434 |
CSV Export Data
ConexED exports three fixed width formatted files nightly: appointment_data shown in Table 3, appointment-data-details shown in Table 4, and appointment-notes shown in Table 5. These data files can also be exported as tab or comma delimited and with or without a header row. All fields are text and are not wrapped in single or double quotes.
Table 3
Appointment Data: List of Appointments Between Staff and Students
| Column Field Header | Format | Description |
|---|---|---|
| Appointment Sequence Number | Alphanumeric | Unique ID for the appointment. This ID is created by ConexED. |
| SIS ID | Alphanumeric | Student Information System ID for the student. Used as a reference key for exporting data back to the institution’s SIS |
| Action Date | Date | MM/DD/YYY formatted date when the appointment was created |
| Appointment Date | Date | MM/DD/YYY formatted date when the appointment was held |
| Start Time | Time | HH:MM:SS formatted time when the appointment started |
| End Time | Time | HH:MM:SS formatted time when the appointment ended |
| Appointment Type | Char | WALKIN or blank (we do not export the appointment for no shows) |
| Counselor ID | Char | EmployeeID or SIS ID (depending on config) of the counselor |
| Department Code | Char | Department Code associated with this meeting. Translates to a group’s Department Code in the edit group UI. |
| Reason Code | Char | Reason Code for the meeting. Translates to the Reason Code field in the Edit Meeting Reasons UI dialog. |
| Last Updated By | Char | EmployeeID or SIS ID of the last person to edit this meeting |
Table 4
Appointment Data Details
| Column Field Header | Format | Description |
|---|---|---|
| Appointment Sequence Number | Alphanumeric | Unique ID for the appointment. This ID is created by ConexED. |
| Counselor ID | Alphanumeric | EmployeeID or SIS ID (depending on config) of the counselor |
| Department Code | Alphanumeric | Department Code associated with this meeting. Translates to a group’s Department Code in the edit group UI. |
| Appointment Reason Code | Char | Reason Code for the meeting. Translates to the Reason Code field in the Edit Meeting Reasons UI dialog. |
Table 5
Appointment Notes on Meetings with Students
| Column Field Header | Format | Description |
|---|---|---|
| SIS ID | Alphanumeric | Student Information System ID for the student. Used as a reference key for exporting data back to the institution’s SIS |
| Note Sequence Number | Alphanumeric | Unique ID for the note. This ID is created by ConexED. |
| Department Code | Alphanumeric | Student Information System ID for the student. Used as a reference key for exporting data back to the institution’s SIS |
| Counselor ID | Alphanumeric | EmployeeID or SIS ID (depending on config) of the counselor |
| Note Date | Date | MM/DD/YYYY formatted date when the note was taken |
| Note Title | Char | Title of the note |
| Last Updated By | Char | EmployeeID or SIS ID of the last person to edit this note |
| Note Text | Char | Full text of the note |
Steps for FTP Flat File Import and Export
To complete an SIS integration using the FTP, ConexED creates an SFTP account for the institution. The process involves the following steps for the IT Professional and ConexED.
- The IT Professional creates a public private key and provides the public key to ConexED.
- The IT Professional provides ConexED the IP of the server that will be used to connect to the SFTP, so ConexED can whitelist it. ConexED will notify the institution’s IT Professional when this step is completed.
- The IT Professional creates a test CSV file using the OneRoster data format with ten or so users and uploads the file to ftp.craniumcafe.com/import and then notifies ConexED.
- The ConexED tech team will test the file, and if there are no errors, set up the folder for the ability for the school to import nightly any Deltas.
The institution can create its own API to automate the way they send the import data from their Student Information System (SIS) and how they pull the export data from the SFTP into their SIS. ConexED’s REST API imports and exports the data from the SFTP folders nightly so that staff have up-to-date information and details about students for initiating outreach, creating appointments, and sending early alerts.
Custom API
ConexED is building a self-serve model for an Ethos API to be able to integrate directly with Ellucian Colleague and Ellucian Banner. ConexED has developed custom APIs for PeopleSoft, Salesforce, and others. During implementation, the institution’s technical team has the opportunity to meet with the ConexED integration team to discuss custom APIs for their SIS.
Self-Service User Import
A self-service Add/Import tool is available for adding a single user at a time or importing up to 200 users at a time without the need of an FTP or API. As shown in Figure 2, you need access to the ConexED Admin User Panel, and for importing multiple users at a time, you’ll need a CSV or Excel file with the user data.
To begin, create your data file following the formatting of the example spreadsheet. Click the Download button shown in Figure 1 to download the example spreadsheet that has nine columns, each with a specific heading. Not all columns need data, but all nine columns with the specific headers given in the example are required. When your file is ready, click the Select File button to import it. The maximum file size with 200 rows of users is 10 MB.
Figure 1
Add/Import Users Self-Service
Imports and Exports Quiz
You need to be registered and logged in to take this quiz. Log in or Register