Student Import File Specifications: Addendum for Excel Spreadsheet Files

Student Import

File Specifications

Addendum

for Excel

Spreadsheet Files

Spring 2023

IMPORTING INTO THE CTEIS APPLICATION

The CTEIS application can import student demographic, enrollment, Work Based

Learning, and grade data. The import process will upload a file from a

spreadsheet application such as Microsoft Excel or Google Docs using CTEIS

Web Application. These instructions explain how to upload files in the Excel file

format.

Uploaded files will be loaded into a temporary table where preliminary error

checks will be performed. Records that pass

all

criteria can be imported into the

application database. The criteria are further explained below.

Student Data Checks

When uploading student data to CTEIS, keep the following in mind:

The file will be grouped by students with all enrollments listed below, if

present. Based on the data specification noted below, a student record can

be added or updated, an enrollment record added or updated, or both.

Typed dates will be accepted in the formats used throughout CTEIS:

mmddyy and mmddyyyy formats cannot contain slashes.

UIC must be provided and be accurate. It must match the Michigan Student

Data System (MSDS) for the fields of First Name, Last Name, Date of Birth,

and Gender.

The Sending District and Sending Building will be matched against the district

and building file of the Education Entity Master. Further, the Sending Building

must be within the Sending District.

During the upload process, each record will be checked for this required information.

If a record fails some criteria, you will be shown, to the best of the application’s ability,

what is wrong with the data. All records that meet the minimum criteria may be

NOTE: In your Excel file, your column headings are required to match the column

heading examples provided in this guide.

imported into the system. All student records will be matched according to UIC, first

name, last name, birth date, and gender. If the record successfully matches an existing

UIC but fails to meet the other criteria, it will be flagged for examination. If no UIC is

found to match, a new student record will be created in the CTEIS system, assuming all

other criteria are met. When a new record with a UIC, first name, last name, birth date,

and gender identically matches a record already in the database, that record will be

overwritten with the new information.

Excel Spreadsheet Format

The Excel spreadsheet is a versatile file type you may use to import your student,

enrollment, class, and grade data into CTEIS. The following rules apply to Excel

spreadsheets:

Columns may appear in any order, but column headings must match the

accepted Excel specifications exactly, including capitalization. Remove unwanted

spaces—CTEIS may interpret these blank characters as part of the column header

text.

Specify that all cells contain text information, not numeric or “general” data, to

avoid conversion errors when you upload your spreadsheet into CTEIS.

The Excel format provides a certain degree of flexibility in that only the required

columns need to be present in the spreadsheet to successfully upload it. If your file

contains more fields than are required for a particular import, CTEIS will simply ignore

information it cannot use, including blank columns.

Remember, your Excel import file

should contain only one worksheet.

Sample Student Record:

STUDENT AND ENROLLMENT (WITH GRADES) HEADING FORMATS

Excel Format – Student and Enrollment (with Grades) Headings

Column Information

Acceptable

Values /

Instructions

Include When Reporting:

Column

Heading

Description

Students

Enrollments

Grades/WBL

LNAME

Student Last

Name

Must match the

UIC Master

  

FNAME

Student First

Name

Must match the

UIC Master



MIDDLE

NAME

Student

Middle Initial

Not required but

highly suggested

UIC

Student UIC

* Must pass

checksum

* Must be unique

* First name, last

name, birth date,

and gender

verified with the

UIC Master



SEX

Gender Code M/F values



DOB

Date of Birth

Acceptable date

formats: mmddyy,

and mmddyyyy



SENDDIST

Sending

District

Must be a valid

sending district

based on the

Educational

Entity Master

(EEM)



Column Information

Acceptable

Values /

Instructions

Include When Reporting:

Column

Heading

Description

Students

Enrollments

Grades/WBL

SENDBUILD

Sending

School or

Facility

* Must be a valid

sending building

within the above

sending district;

verified with the

EEM

* For codes

beginning with

zero, ensure the

leading zero

appears in your

file



PHONE1

Phone 1 (###) ### - ####

PHONE2

Phone 2 (###) ### - ####

ADD1

Address 1

ADD2

Address 2

CITY

City or Town

STATE

State

2-letter

abbreviation for

the state

ZIP

Zip Code ##### ( - ####)

E-mail

Student’s

primary

e-mail address

Single Parent

Y/N values

(Default: N)

OWF

Out Of

WorkForce

Y/N values

(Default: N)

CSC

Course

Section Code

*To import data

for this class, you

must enter it into

CTEIS first



Column Information

Acceptable

Values /

Instructions

Include When Reporting:

Column

Heading

Description

Students

Enrollments

Grades/WBL

BEGDATE

Student

Begin Date

Date student

began

enrollment

ENDDATE

Student End

Date

Date student

ended

enrollment

WBL

Work Based

Learning

Experiences

Indicate work

based learning

experiences.

Acceptable

values:

A — Career

Awareness

E — Career

Exploration

P — Career

Preparation

T — Career Training

Y — Youth

Apprenticeships

SUB

Subsection

The subsection

of a course a

student is

enrolled in—

defaults to

Subsection A if

left blank



CRSGRD

Course

Grade

Letter grade

received by the

student in the

current course

section



STUDENT AND ENROLLMENT VALIDATION

After you load the file containing students and enrollments to import, it will be

validated and displayed on the screen. If a student has enrollments, they will be

bundled together below the student information.

For students, the first column is the Results column. It will display the result of

the validation. It will show Add Student (Green), Update Student (Blue) for valid

records or Student Errors Found(Red) if the record failed validation. If errors are

found, the next row will list them.

Below the Student, enrollments will be listed if provided. Similarly, they will

be validated based on the specifications and the result column will

indicate: Add Enrollment(green), Update Enrollment (blue), or Enrollment

Error Found (Red). If errors are found, the next row will list them.

The errors and their potential resolution are listed below in the Issue Tables

section.

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttuden

SSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudent

VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeerifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrify

LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasssssssssssssssssssssssssssssssssssssssssssssssssssssttttttttttttttttttttttttttttttttttttttttttttttttttttt

mnamnamnamnamnamnamnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaamemememememememememememememememememememememememememememememememememememememememememememememee,e,e,e,e,e,e,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

FiFiFiFiFiFiFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsrrrrrrrsssssssttttttttttttttttttttttttttttttttttttttttttttttttttttt

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttuden

SSSSSSSSSSSSSSSSendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendingngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngng

MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMusususususususususususususususususususususususususususususususususususususususususususususususususususususttttttttttttttttttttttttttttttttttttttttttttttttttttt

bebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebe

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaalidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlid

sssssssssssssssssssssssssssssssssssssssssssssssssssssendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendingngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngng

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttuden

AgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmusususususususususususususususususususususususususususususususususususususususususususususususususususususttttttttttttttttttttttttttttttttttttttttttttttttttttt

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccceptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablableeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

datdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdateeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

fof

ofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofofoformarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmarmatststststststststststststststststststststststststststststststststststststststststststststststststststststs:::::::::::::::::::::::::::::::::::::::::::::::::::::

(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)(m)m/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(dm/ (dm/ (dm/(d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d)d/////////////////////////////////////////////////////yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttuden

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudentudent

VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeerrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyrifyify

LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLa

sLasaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasssssssssssssssssssssssssssssssssssssssssssssssssssttttttttttttttttttttttttttttttttttttttttttttttttttttt

FiFiFiFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsirsrrrrrrssssssttttttttttttttttttttttttttttttttttttttttttttttttttttt

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttuden

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendiendingngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngngng

bebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebebe

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaalidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlidlid

SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSStttttttttttttttttttttttttttttttttttttttttttttttttttttuden

AgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgAgeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmusususususususususususususususususususususususususususususususususususususususususususususususustttttttttttttttttttttttttttttttttttttttttttttttt

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccceptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptepteptablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablablableeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

datdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdatdateeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

ISSUE TABLES

Error Level

Error Message

Resolution

Enrollment Import

Student Core Information Error Messages

Student

Invalid UIC

First name, last name,

birth date, and gender

verified with the UIC

Master. Must be 10 digits

Student

Last Name, First Name, invalid longer than

20 characters.

Make sure the name is no

longer than 20 characters.

Must match UIC Master.

Student

The student doesn't match Verify Last name, First

name, DOB, and Gender

matches MSDS. Verify

UIC is entered correctly.

Student

Sending District is not valid

Must be a valid sending

district based on

Educational Entity

Master. (EEM).

Student

Age must be less than 30. Invalid age. Date of

Birth Invalid.

Acceptable date formats:

mmddyy, and mmddyyyy.

Verify age.

Student

Sex invalid, longer than one character.

not "M" or "F".

Please use M/F values.

Verify sex is no longer

than one character.

Student

Sending District invalid,

longer than five characters

Must be a valid sending

district from the Educational

Entity Master. (EEM). Verify

district code is correct.

Student

Sending Building invalid, longer than five

characters.

Must be a valid sending

building within the sending

district.

Verify City or Town. Formatting

can be no longer than 150 characters.

Error Level

Error Message

Resolution

Enrollment Import

Student Core Information Error Messages (CONT).

Student

Phone 1 or Phone 2 invalid

• longer than 30

characters.

Verify phone number is accurate and in

the correct format. (###)###-####.

Student

Address 1 Invalid, longer

than 100 characters.

Verify address. Formatting no

longer than 100 characters long.

Student

Address 1 Invalid,

• longer than 100

characters.

Verify address. Formatting no longer

than 50 characters long.

Student

City invalid,

longer than 150 characters.

Verify the correct City or Town. No longer

than 150 characters.

Student

Zip code invalid,

• longer than 10

characters.

Verify Zip code is accurate and

entered in the correct formatting:

##### (-####)

Student

Single parent invalid,

• longer than one

character. Not "Y" or

"N"

Y/N Values (Default: N)

Student

Email address

longer than 100 characters.

Student's primary e-mail address.

Verify correct formatting.

Student

Out of workforce.

• longer than one

character. Not "Y" or

"N"

Y/N values (Default: N)

Error Level

Error Message

Resolution

Enrollment Import

Enrollment Information Error Messages

Enrollment

Invalid UIC, the student doesn't

match UIC

UIC was not found or did not match

Last Name. Please check UIC and

last name is correct.

Enrollment

Invalid course section code

Course Section Code not found. To

import data for this class, you must

enter it into CTEIS first. Verify course is

entered properly.

Enrollment

Invalid building number

Must be a valid sending building and

district. For codes beginning with zero,

ensure the leading zero appears in a file

Enrollment

Begin date invalid

• unable to determine that

it can be resolved to a

date

Verify begin date is entered correctly.

Begin dates must match records in the

CTEIS system.

Enrollment

End date invalid

• unable to determine that it

can be resolved to date.

Verify end date is entered correctly. End

dates must match records in the CTEIS

system.

Enrollment

Dates invalid

• begin date less than course

begin date.

• begin date greater than

course end date.

• end date less than course

begin date.

• end date greater than

course end date.

• begin date equals

enrollment end date

• end date less than

enrollment begin date

• begin date greater than

enrollment end date.

Verify dates are entered correctly. Check

enrollment dates and make sure data

matches and is within course dates.

Enrollment

Invalid subsection

• has a subsection value and

longer than one character.

• if no subsection defaulted

to A

Verify student enrollment information.

The Manage Courses screen within

CTEIS can be used to verify class

subsections.

Enrollment

Invalid course grade

• has a grade value and is

longer than one

character.

• doesn't fit our letter grade

values

Verify student grade information.

Ensure that the appropriate single-

letter grade for the student is entered.

Error Level

Error Message

Resolution

Enrollment

No valid student enrollments to

upload

This often occurs when improper start and

end dates are chosen. Use the manage

Enrollment screen to verify enrollment

data.

Enrollment

Invalid work based learning

• has a work based

learning value and

longer than 15

characters

• doesn't match our

values for work based

learning

Indicate work based learning experiences.

Acceptable values:

A-Career Awareness

E-Career Exploration

P-Career Preparation

T-Career Training

Y-Youth Apprenticeships

Each experience should be entered as an

individual character. Please refer to the

Enrollment & Completion Collection guide

for more information on Work based

learning values.