Individual Student Enrollment System
CSV Interface Specification for CWCS Student and Completion Data
Individual Student Enrollment System
CSV Interface Specification for
Coursework Completion System –
Student File
Version 3.2
Date 10/24/14
Wisconsin Department of Public Instruction
Table of Contents
1 Introduction 2
1.1 Purpose 2
1.2 How This Document Is Organized 2
1.3 Definitions 2
2 CWCS Student and Completion Data Exchange Description 3
2.1 Overview of Comma Separated Value (CSV) 3
2.2 File Flow 3
2.3 CWCS Student and Completion Data Files 3
2.3.1 Student Load File 3
2.3.1.1 Student Load Filename Format 3
2.3.2 Student Load Results Report 3
2.3.2.1 Student Load Result Report Filename Format 4
2.3.3 Student Load Failure File 4
2.3.3.1 Student Load Failure Filename Format 4
2.3.4. Student Status File 4
2.3.4.1 Student Status Filename Format 4
2.3.5 Student Baseline Download File 4
2.3.5.1 Student Baseline Download Filename Format 4
2.3.6 Student Backup File 5
2.3.6.1 Student Backup Filename Format 5
A. Data Element Definitions 6
A.1 Date Formats 8
A.1.1 Date Format 8
A.1.2 Date Time Group Format 8
A.2 Sequence Number 8
A.3 Criteria for Matching CWCS to WSLS 8
A.4 Uniqueness Criteria 8
B. File Definitions 9
B.1 File Header Definition 9
B.2 Student Load File and Baseline Download File Definition 10
B.3 Student Load Failure File Definition 13
B.4 Student Load Result Report 14
B.5 Student Status File Definition 15
C. Additional CWCS-Student and Completion Data Validations 18
D. Code Tables 19
D.1 Collection Type 19
D.2 Completion Status 19
D.3 Grade Earned 19
D.4 Grade Level Placement 19
D.5 Record Type 20
D.6 Term 20
E. Special Character Entity References for XML 21
1 Introduction
The primary goal of the Coursework Completion System (CWCS) is to allow the public school and/or district staff to report coursework information via on-line entry and/or batch processing necessary to associate teachers to courses, courses to students, and students to completion data.
A process has been established which enables schools and/or districts to upload coursework completion data into the CWCS in two parts. First course and teacher data is submitted followed by student and completion data. This document deals with student and completion data. Limited validations will be run at the time of student file upload. Once the school submits an error free file, all of the student and completion information for the school will be stored in the CWCS database. More complex validations will be performed within the CWCS web application during the “Validate” Process.
1.1 Purpose
The purpose of this Interface Specification is to provide the information necessary to transfer student and completion data from a Student Information System to the CWCS.
1.2 How This Document Is Organized
The remainder of this document is divided into two sections plus appendices, designed to answer a wide range of questions involving the operational considerations, and the technical requirements for implementing the Student Information System interface with the CWCS.
· Section 2 provides an introduction to the electronic form of data exchange.
· Appendix A contains the definitions of the data elements.
· Appendix B contains the definitions of the CSV formatted files.
· Appendix C contains the definitions for additional data validations
· Appendix D contains the code tables to validate the student files.
1.3 Definitions
This table provides a brief introduction to a variety of terms and acronyms that are used in this Interface Specification.
Term / Description /Data Element / A singular piece of data in a data exchange.
DPI / Wisconsin Department of Public Instruction
ISES / Individual Student Enrollment System
SIS / Student Information System located at the district or school.
File / A standard format ISES data exchange.
WSLS / Wisconsin Student Locator System
WSN / Wisconsin Student Number
CWCS / Coursework Completion System
CSV / Comma Separated Value, a flexible way to create information formats and share both the format and the data on the World Wide Web, intranets, and elsewhere.
Table 11 Definition of Terms
2 CWCS Student and Completion Data Exchange Description
2.1 Overview of Comma Separated Value (CSV)
Comma Separated Value (CSV) has been selected as one of the underlying formats for the data exchange with the Student Information System packages. A CSV file contains the values in a table as a series of ASCII text lines organized such that each column value is separated by a comma from the next column's value and each row (or each new line) starts a new record. It is strongly encouraged that all fields are enclosed in double quotes. For example,
"DOE","JANE","10-01-2015"
A valid file contains three to several rows of data. Each row of data contains several data fields, which are separated by commas. The first field of every row indicates the header type for the row. The CWCS accepts distinct header types depending on the file type, for example the Student Load File has 4 header types, 01 – collection record, 02 - school record, 03 – student record, 04 – end of file. These header types tell the CWCS the type and format of data to expect in the row.
2.2 File Flow
For each Load File received from a Student Information System by the CWCS a file result is returned. The file result identifies the status of the Load, Success or Failure, and any errors found when processing the file.
2.3 CWCS Student and Completion Data Files
The subsections that follow provide a brief description of the CWCS Student and Completion Data files. The specific structure of each file is defined in Appendix B.
2.3.1 Student Load File
The Student Load File is used to transmit student and completion data from the schools. The student record is associated to the course and teacher data via a Section ID submitted in the Course and Teacher Load file. This Section ID is included in each student record in the Student Load file.
2.3.1.1 Student Load Filename Format
Name: <SendTag>_<DistrictCode>_<SchoolCode>_<MMDDYYYY>_<Type>_<SeqNumber>.csv
Size: <1> <4> <4> <8> <3> <5>
Example: S_0130_0000_02012015_WSL_00002.csv
This is the Student Load File for District 0130. It is a district level file and may contain multiple schools in the district, thus the school code is represented as “0000” (If the file was for one school or a non-district 2R charter school, then the school code would be used here). This file was generated on February 01, 2015, by the Student Information System. The WSL type stands for CWCS Student Load file. Refer to Appendix B.2 for additional information about this file.
NOTE: Sizes are exact and fixed. Most numbers should have leading zeroes if they do not fill the full size for the named field (e.g. the month of May should be represented as 05 and NOT 5). If the file contains more then one school, then the school code value must equal “0000”. School code, “0000” is not valid for a non-district 2R charter school as a school code must be used.
2.3.2 Student Load Results Report
A Load Results Report is generated for each file received and processed by the CWCS. If the file status is not SUCCESS, then a Load Results Report is returned with an identification of the error. SUCCESS indicates that the file uploaded successfully, it does not reflect the result of the validation process required before data is locked in the CWCS web application.
2.3.2.1 Student Load Result Report Filename Format
Name: <ReportTag>_<DistrictCode>_<SchoolCode>_<MMDDYYYY>_<Type>_<SeqNumber>.html
Size: <1> <4> <4> <8> <3> <5>
Example: R_0130_0000_02012015_WSL_00002.html
This is the Student Load Results Report that corresponds to the Load file S_0130_0000_02012015_WSL_00002.csv. Refer to Appendix B.4 for additional information about this report.
2.3.3 Student Load Failure File
A Load Failure File will only be generated if the Load File fails to process successfully. This file can be used by districts and schools to determine which records violated file upload business rules and the reason for the error.
2.3.3.1 Student Load Failure Filename Format
Name: < SendTag>_<DistrictCode>_<SchoolCode>_<MMDDYYYY>_<Type>_<SeqNumber>.csv
Size: <1> <4> <4> <8> <3> <5>
Example: S_0130_0001_02012015_WSF_00002.csv
This is Student Load Failure File for District 0130, for School 0001 in the district. It was generated from processing the load file S_0130_0001_02012015_WSL_00002.csv. The type WSF stands for CWCS Student Load Failure. This is a file generated by the CWCS and may be used by the Student Information System to upload the results from the CWCS Load file. Refer to Appendix B.3 for additional information about this file.
2.3.4. Student Status File
The Student Status File can be requested from the File Download Request screen and is a snapshot of the current student and completion data within the CWCS database. This file contains all the data in the submitted via Student Load Files as well as calculated fields and validation status information.
2.3.4.1 Student Status Filename Format
Name: <SendTag>_<DistrictCode>_<SchoolCode>_<MMDDYYYY>_<Type>_<SeqNumber>.csv
Size: <1> <4> <4> <8> <3> <5>
Example: S_0130_0001_02012015_WSS_00002.csv
This is a Student Status File with all WS record types for District 0130, for School 0001 in the district. It was generated by request in the CWCS web application on February 01, 2015. The type WSS stands for CWCS Student Status file. Refer to Appendix B.5 for additional information about this file.
2.3.5 Student Baseline Download File
The Student Baseline Download File can be requested from the File Download Request screen. This file has the same format as the Load file.
2.3.5.1 Student Baseline Download Filename Format
Name: <SendTag>_<DistrictCode>_<SchoolCode>_<MMDDYYYY>_<Type>_<SeqNumber>.csv
Size: <1> <4> <4> <8> <3> <5>
Example: S_0130_0001_02012015_WSB_00002.csv
This is a Student Baseline Download File with all WS record types for District 0130, for School 0001 in the district. It was generated by request in the CWCS web application on February 01, 2015. The type WSB stands for CWCS Student Baseline Download file. Refer to Appendix B.2 for additional information about this file.
2.3.6 Student Backup File
The Backup File is automatically generated following each successful Load file. The file contains all records replaced by the load process.
2.3.6.1 Student Backup Filename Format
Name: <SendTag>_<DistrictCode>_<SchoolCode>_<MMDDYYYY>_<Type>_<SeqNumber>.csv
Size: <1> <4> <4> <8> <3> <5>
Example: B_0130_0001_02012015_WSL_00002.csv
This is the backup file associated with the load file S_0130_0001_02012015_WSL_00002.csv. Refer to Appendix B.2 for additional information about this file.
A. Data Element Definitions
Files are comprised of data segments, which are groups of data elements. This section defines the Coursework Completion System data elements. They are presented in alphabetic order, below. The data types of the data elements are all ASCII text, and are further defined in the table as: A (alphanumeric), N (numeric), D (date), or DT (date time). For more information about data elements included in the CWCS, see http://www.dpi.wi.gov/lbstat/cwcsapp.html and http://www.dpi.wi.gov/lbstat/eseamap.html#data.
Data Element Name / Description / Data Type / Size (bytes) / Value/Format /CollegeCreditsEarned / Number of credits that can be applied towards college requirements / N / 5,3 / NN.NNN
CollectionType / Collection Type / A / 3 / See Appendix D.1
CompletionStatus / Completion Status Type / A / 2 / See Appendix D.2
DateTimeGroup / Date and Time this file was created / D / 24 / See Appendix A.1
EnrollDate / Student enrollment date / D / 10 / See Appendix A.1
GradeEarned / Grade assigned indicating level of accomplishment / A / 4 / See Appendix D.3
GradeLevelPlacement / Grade Level Placement / A / 2 / See Appendix D.4
HighSchoolCreditsEarned / Number of credits that can be applied toward the requirements for a diploma, certificate, or other formal award / N / 5,3 / NN.NNN
RecordType / Record Type / A / 2 / See Appendix D.5
CourseOptionsDistrict / Indicator for Course Options students taking up to two courses in a district other than the submitting district / A / 4 / ‘NA’ or valid District Code
SchoolCode / School within the district submitting the ISES students’ data that provides/supervises the students’ primary PK-12 educational services / A / 4
SectionID / Unique identifier defined by the district to differentiate courses into units associated with a specific teacher or group of teachers and a specific group of students / A / 25
SeqNum / Sequence Number for this file / N / 5 / NNNNN is a 5 digit sequence number
Status / Status Message in Load Failure File / A / 7 / ‘SUCCESS’, ‘ERROR’, or ‘WARNING’
SubmittingDistrict / District submitting students’ data This is the district that provides / supervises the students’ primary PK-12 educational services / A / 4
Term / An Indicator for when the course is offered / A / 3 / See Appendix D.6
ValidationMessage / Validation Message in Status File / A / 7 / ‘INFO’, ‘ERROR’, ‘WARNING’ or blank
WSNStudentID / Wisconsin Unique Student Identifier / N / 10
WSLSSchool / WSLS School that hold the students WSN ID and where the student is enrolled. / A / 4
Year / School Year / A / 4 / “YYYY” format
A.1 Date Formats
A.1.1 Date Format
MM/DD/YYYY
Or
MM-DD-YYYY
A.1.2 Date Time Group Format
MM/DD/YYYY HH:MM:SS XXX
Or
MM-DD-YYYY HH:MM:SS XXX
Where XXX is the time zone (e.g., CST).
A.2 Sequence Number
· Begins each new school year at 00001
· Reset to 00001 at the end of each school year.
· Incremented by 1 each time a new file is created.
Example: The file is generated for a school as the first file that year. The sequence number would be 00001. The very next file generated would be 00002.
· Each re-submittal of a file will be given a new sequence number.
Example: If the previously mentioned file (sequence 00001) had errors and a new file was created after corrections were made, the new file would have a NEW sequence number (in this case 00002, since 00001 was used for the first file).
· Sequence Number in the file must match the sequence number on the file name.
A.3 Criteria for Matching CWCS to WSLS
Listed below are the criteria that will be used to match a CWCS record to an existing record in WSLS.
FIELDS / WSLS Match Criteria / ISES Match CriteriaSchool District / X / X
School / X / X
WSN / X / X
School Enroll Date / X / X
Year of Record / X
Record Type / CD
A.4 Uniqueness Criteria