Academic Import Utility

The Academic Import Utility (AIU) is an optional, add-on component for EMS. It's designed to manually import from your Student Information System (SIS). For each scheduled class that is being imported, a single reservation with multiple bookings is created. One booking is created for each date on which the class meets. All class reservations created by the import process are assigned to one Group (customer, client, and so on) that you have configured in your EMS database. The imported class data can be tab-delimited or fixed width. Pior to using the AIU, you should have certain items configured in your EMS database.

To import data, configure a session for each block of data, for example, quarter or semester. Then, AIU saves the settings for use in the initial import and any subsequent reimport. When a session is reimported, only events in the import file that changed since the last import are recreated in the EMS database. This prevents the unnecessary reimport of unchanged events and the associated invalidation of large ranges of reservation numbers. After configuring a session, you import the session.

Reservations that were added to the EMS database using the AIU can also be removed from the database using AIU. Deleting a session removes all reservations created using the session.

Before you configure an AIU session, review the Academic Import Days of the Week setting in your system parameters. This setting dictates whether the AIU interprets days of the week based on their position within the data (with options for Sunday first or Monday first) or based on specific text entries (Su = Sunday, M = Monday, and so on).

Learn about EMS System Parameters for All Applications.

ClosedFixed width format 

All fields are required unless noted otherwise.

Field

Field Sequence

Column

Width

Field Type

Format

Example

Department

1

1

10

Alpha numeric

Left Justified, Trailing Spaces

ACCT

Course

2

11

10

Alpha numeric

Left Justified, Trailing Spaces

101

Section

3

21

10

Alpha numeric

Left Justified, Trailing Spaces

1

CourseTitle

4

31

225

Alpha numeric

Left Justified, Trailing Spaces

Intro to Accounting

Instructor

5

81

50

Alpha numeric

Left Justified, Trailing Spaces

Bob Smith

Days

6

131

7

Alpha numeric

Left Justified, Trailing Spaces

Parameter driven. Positional or specific text.

e.g. “M W F” for Positional, Monday First

Building

7

138

10

Alpha numeric

Left Justified, Trailing Spaces

AC

Room

8

148

20

Alpha numeric

Left Justified, Trailing Spaces

101

BegTime

9

168

4

Numeric

Right Justified, Zero Filled (numerics only)

0800

EndTime

10

172

4

Numeric

Right Justified, Zero Filled (numerics only)

1450

StartDate (Optional)

11

176

10

Date

Formatted (mm/dd/yyyy)

08/30/2018

EndDate (Optional)

12

186

10

Date

Formatted (mm/dd/yyyy)

12/17/2018

Attendance (Optional)

13

196

5

Numeric

Right Justified, Zero Filled (numerics only)

00035

ClosedTab delimited format

The EMS Campus Web Service can import your course data into EMS Campus via tab-delimited files outputted from your SIS. The following tables outline the specifications for this option. A separate file is required for each of these tables. By default, the EMS Campus Web Service looks to C:\Inetpub\wwwroot\EMSCampusWebService\CustomConfig\ on your web server for your tab-delimited files.

Buildings.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

WASH

must be unique

BuildingCode

text

20

WASH

 

Description

text

50

Washington

 

RoomTypes.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

C

must be unique

Description

text

30

C

e.g. Classroom

Rooms.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

WA300

must be unique

RoomCode

text

20

WA300

 

Description

text

50

WASHINGTON ROOM 300

 

BuildingID

text

255

WASH

 

RoomTypeID

text

255

C

 

Capacity

number

 

30

 

Instructors.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

123456

must be unique

Description

text

255

Terry Jones

 

Subjects.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

WH

must be unique

SubjectCode

text

10

WH

 

Description

text

50

World History

 

CampusCode

text

30

MAIN

blank if N/A

DepartmentCode

text

50

 

optional, blank if N/A

DivisionCode

text

50

 

optional, blank if N/A

LevelCode

text

50

 

optional, blank if N/A

CustomCode1

text

50

 

optional, blank if N/A

CustomCode2

text

50

 

optional, blank if N/A

CourseTypes.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

L

must be unique

Description

text

255

L

e.g. Lecture

Terms.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

07FALL

must be unique

TermCode

text

10

07FALL

 

Description

text

100

2007 FALL TERM

 

StartDate

date

 

9/12/2007

must be in m/d/yyyy format

EndDate

date

 

12/15/2007

must be in m/d/yyyy format

Courses.txt

Column

Data Type

Max. Characters

Example

Notes

ID

text

255

100001

must be unique

CRN

text

255

100001

must be unique

CourseTitle

text

100

World History

 

TermID

text

255

07FALL

 

EstimatedEnrollment

number

 

30

 

ActualEnrollment

number

 

0

 

InstructorID

text

255

123456

 

SubjectID

text

255

WH

 

CrossListParentID

text

255

0

relates to ID, zero or blank if N/A

Course

text

10

101

 

CampusCode

text

30

MAIN

optional, blank if N/A

Section

text

10

01

 

Credit Hours

number

 

3.0

 

DepartmentCode

text

50

 

optional, blank if N/A

DivisonCode

text

50

 

optional, blank if N/A

LevelCode

text

50

 

optional, blank if N/A

CustomCode1

text

50

 

optional, blank if N/A

CustomCode2

text

50

 

optional, blank if N/A

CourseDates.txt

Column

Data Type

Max. Characters

Example

Notes

CourseID

text

255

100001

 

ID

text

255

100001

must be unique

CourseTypeID

text

255

L

 

StartDate

date

 

9/11/2007

must be in m/d/yyyy format

EndDate

date

 

12/15/2007

must be in m/d/yyyy format

BuildingID

text

255

WASH

 

RoomID

text

255

WA300

 

StartTime

time

 

0900

HHMM format, no colon

EndTime

time

 

1300

HHMM format, no colon

Sunday

0 or 1

 

0

 

Monday

0 or 1

 

1

 

Tuesday

0 or 1

 

0

(recursive snippet)

Wednesday

0 or 1

 

1

 

Thursday

0 or 1

 

0

 

Friday

0 or 1

 

1

 

Saturday

0 or 1

 

0

 

ClosedConfigured Items for the AIU 

Before using the AIU, you should have the following items configured in your EMS database:

Item

Description

Group

When reservations are created through the import process, one Group is the reservation holder for all reservations. You might want to define a group used for the reservations created through the import process, for example, Registrar's Office.

New Bookings Status

When running the AIU, you must specify a status for the new bookings.

This status must be “Book Space” type. You can use an existing status like Confirmed, but it's best to create a new status to denote the imported data.

Conflict Status

Create a conflict status to give new bookings if the room they require is already booked. This status should have a "Info Only" or "Wait" status type. Those types of bookings can be created even if there is already a confirmed booking in the room. Academic - Conflict is a commonly used name for this status.

Cross List Status

Create a status to apply to bookings when the system encounters conflicts within an import file (for example, the same class with different names). This status should have a status type of “Info Only” or “Wait.”

Override Status

You can configure the settings in an AIU session to allow AIU bookings to override existing bookings. The status is changed for the existing bookings and the room is reserved for the imported bookings. You must create an override status to apply to the bookings that have been bumped. This status should have a status type of “Info Only” or “Wait.”

You also might want to define a unique Event Type and Setup Type to be used solely for class reservations.

To import data, configure a session for each block of data, for example, quarter or semester. Then, AIU saves the settings for use in the initial import and any subsequent reimport. When a session is reimported, only events in the import file that changed since the last import are recreated in the EMS database. This prevents the unnecessary reimport of unchanged events and the associated invalidation of large ranges of reservation numbers. After configuring a session, you import the session.

Reservations that were added to the EMS database using the AIU can also be removed from the database using the AIU. Deleting a session removes all reservations created using the session.

ClosedConfigure an AIU session 

This section describes the steps for configuring a new session for the AIU. If you need to load an existing session, then after opening the Sessions window, skip to Import the data into the session.

To configure an AIU session:

  1. On the EMS menu bar, click Reservations > Academic Import > Academic Import Utility

    The Sessions window opens and lists all the sessions configured in your EMS database.

  2. Click New

  3. Enter the information for the new AIU session.

Field

Description

Session Code

Unique identifier for the session, for example, SPRING18.

Description

Expanded definition of the session code, for example, Spring Semester 2018 (50 character maximum including spaces).

Start Date

End Date

Start and end date for the session (for example, 1/15/18 and 5/31/18).

Group

Reservation holder for all reservations in the session. For example, Registrar's Office. Select a value from the Group field, or click the Search icon to open the Groups window and create a group.

Event Type

Select a value from the field. If none of the event types configured in your EMS database meets your needs, then configure an event type.

Setup Type

Select a value from the field. If none of the event types that are configured in your EMS database meets your needs, then you can configure a setup type.

Status

Status for new bookings.

While you can use an existing status such as Confirmed, it's best to create a new status (for example, Academic - Confirmed) to denote the imported data.

Conflict Status

Available after you specify a Status. Select a value from the field. If none of the statuses that are configured in your EMS database meets your needs, then you can configure a status (for example, Academic - Conflict).

Use Cross List Status

Select if the system is to apply a special status to classes that are cross-listed in the import file. The class appears twice in the file, with each occurrence having a different name.

Cross List Conflict Status

Available if you select Use Cross List Status. The status to be applied to cross-listed classes. Select a value from the field. If none of the statuses in your EMS database meets your needs, then you can configure a status (for example, Academic - Cross List).

Override Existing Bookings

Select if the system is to give import file events precedence over existing events in the EMS database.

Override Conflict Status

Available if you select Override Existing Bookings. The status that is to be applied to existing events if they are overridden by imported events. Select a value from the field. If none of the statuses that are configured in your EMS database meets your needs, then you can configure a status (for example, Academic - Conflict).

Reservation Source

Select a source for the reservation. If none of the sources in your EMS database meets your needs, you can configure a source.

  1. Click OK

    The Sessions window opens with the newly configured session displayed.

  2. Continue to import the data into the session below.

ClosedImport the Data into the Session

To import data into the session:

  1. Select the session that you are using for importing classes, and then click Load

    The first of three AIU windows opens.

  2. To browse to and select a file to import, click Search.

  3. In Import Format, select the file format (Fixed Width or Tab Delimited).

  4. Click Next

    Two results are possible:

    • If any of the records in the import file have any issues (missing data, data that is not in the correct format, data that falls outside the start and/or end dates, and so on) a message opens that details the issue and if applicable, the number that cannot be imported, and asking if you want to continue. You can click No in the message, and then correct the errors in the file before you load the file again, or you can click Yes in the message, and then click Next to continue.

    • The import file is complete and there are no issues with the file, so no message opens. Click Next to continue. The second of three AIU windows opens. This window lists the academic Building and the Room that are currently reserved for all the classes in the import file. For a new import file, the Building/Room combinations have a status of Assigned or Do not Import. Unassigned classes must be assigned to a room in EMS. Classes with a status of “Do Not Import” are not to be assigned to a room in EMS. 

    • Optionally, you can also do one or both of the following:

      • Click View Errors for more information.

      • Click Export Errors to export the errors in the import file and an XML format.

  5. Optionally, select a different View and/or Building option to filter the information that is displayed in the window.

    The default display is all views (Assigned, Unassigned, and Do not Import) for all buildings.

  6. Select an unassigned class, or Ctrl-click to select the multiple unassigned classes, and then click Assign

    The Room Selector dialog appears.

  7. In Building, select the building or view by which to filter the available rooms list.

  8. On the Available rooms list, select the room to which the classes are to be assigned, and then click OK

    Two results are possible:

    • If you selected a single class to assign to the room, then the class status changes to Assigned in the AIU window. The EMS Building code and EMS room name display for the class in the AIU window.

    • If you selected multiple classes to assign to the same room, then a message opens asking you if you are sure that you want to assign all the selected items to the same room. Click Yes in the message. The class status changes to Assigned for all the selected classes in the AIU window. The EMS Building code and EMS room name display for the classes in the AIU window.

  9. Click Next

    The third AIU window opens. In the right pane, the window lists the dates for which bookings will be created for the selected classes.

  10. Optionally, do one or both of the following:

    • To add more booking dates, select the date in the left pane, and then click Add to open the Date Pattern dialog and specify the date patterns for the bookings that should be created.

    • To delete booking dates, select the date in the right pane, and then click Delete.

  11. After you add or delete booking dates, click Finish.

    A success message appears.

  12. Click OK.

ClosedTroubleshoot Academic Import Utility (AIU)

Support receives a variety of calls about AIU but most of them are concerning either formatting or configuration.

Checklist

You should follow the following tasks while troubleshooting AIU:

Formatting

  • Check Format – Open import file in Excel to check format. This will tell you if there are any added columns that may cause errors.
  • Eliminate clutter – Eliminate all additional clutter by altering the import text file to only use the first line. Once the format of the first line is correct the customer can create the import file format based on that. To eliminate additional clutter:
    1. Open the import file and delete all the rows except the first row.

    2. Save the file as a test import file.

    3. Test the import of that single row into the session.

      If there is still an error the easiest way to find errors is by using our import file checker. The import file checker is available on our FTP site in the Tools folder.

      Only the customer can do the import file creation. Support can only point out the issues in formatting. We recommend starting with a small import file and build on it.

  • Use the Tab Delimited format – If using the Fixed Width format, recreate the file in the Tab Delimited format and retry the above tests.
  • Check Tabs – If the import file is in the Tab Delimited format, check whether all the tabs are used correctly and are in the same line.
  • Check Fields – Verify that the fields in the import file match the criteria in the setup guide appendix, outlining what character types are allowed for each field. Then, check the length of the fields. The import checker will do this for you and list the errors if any, on the right side.
  • Check Dates – Verify that the dates in the import file match those set in the session.
  • Check AIU Parameters – Navigate to System Administration > Settings > Parameter. Verify that the AIU parameters have the right format.

EMS Import File Checker

To use the import file checker:

  1. Click Open File.

  2. Verify that the AIU data type is correct.

  3. Verify that the file type is correct.

    Use the AIU field type that sets the field format requirements.

  4. Click Check File.

    Errors display in the Errors column. One error displays per row.

If none of the troubleshooting steps above work, contact a consultant.

Creating reservations can take an extended time and must be performed when no users are logged on to EMS. (To log off active users, see Viewing and Emailing Logged-In Users.) The time required to create reservations varies depending on the amount of data involved and the processing power of the computer making the reservations. After the import process completes, the system automatically displays a report listing booking conflicts, if any.