ODE Certification Data File
ODE posts an updated file named issued_credentials.txt every Monday at approximately 1 AM on their FTP site, ftp://ftp.ode.state.oh.us/misc/. The file holds the active (or 'issued') credential information for all educators in the state. The table below shows the format of the data file and how the data is loaded:
ODE Field Number | ODE File Field Name | Length | Data Type | Description | Location |
|---|---|---|---|---|---|
1 | Educ_Cred_Key | 15 | Number | CORE 2 ‘key’ to this credential | Certification Window – Number Field (emp_certificate.number) |
2 | State Staff ID | 9 | Character | Example: OH3019384 | Used to look up the employee number based on a matching Credential ID on the User Defined Window, Page 10000. The employee number is set to the employee number for the certificate (emp_certificate.empl_no) |
3 | First Name | 75 | Character | Not Used | |
4 | Middle Name | 75 | Character | Not Used | |
5 | Last Name | 75 | Character | Not Used | |
6 | Credential Term Name | 60 | Character | Example: 2 Year, 5 Year | Certification Window – Type Field (Credential Term Name, Professional Classification Name and Credential Category Name are concatenated together and compared to the expanded certification description, cert_type.desc_x. If a match is found cert_type.code is entered into emp_certificate.c_type, otherwise a new certificate type code is created and entered into the certificate type reference table, cert_type.code, and employee’s certificate, emp_certificate.c_type) |
7 | Professional Classification Name | 60 | Character | Example: Professional,Provisional | Certification Window – Type Field (Credential Term Name, Professional Classification Name and Credential Category Name are concatenated together and compared to the expanded certification description, cert_type.desc_x. If a match is found cert_type.code is entered into emp_certificate.c_type, otherwise a new certificate type code is created and entered into the certificate type reference table, cert_type.code, and employee’s certificate, emp_certificate.c_type) |
8 | Credential Category Name | 60 | Character | Example: License, Permit | Certification Window – Type Field Credential Term Name, Professional Classification Name and Credential Category Name are concatenated together and compared to the expanded certification description, cert_type.desc_x. If a match is found cert_type.code is entered into emp_certificate.c_type, otherwise a new certificate type code is created and entered into the certificate type reference table, cert_type.code, and employee’s certificate, emp_certificate.c_type. |
9 | Credential Type Name | 60 | Character | Example: Elementary 1-8, High School | Certification Window – Area Field Credential Type Name is compared to the expanded certification area description, cert_area.desc_x. If a match is found cert_area.code is entered into emp_certificate.c_area. If a match cannot be found in the reference table, a new certificate area code is created and entered into the certificate area reference table, cert_area.code, and employee’s certificate, emp_certificate.c_area. |
10 | Action Name | 60 | Character | Example: New In State, New Out of State, Renewal | Not Used |
11 | Valid In Organization IRN | 6 | Character | Example: 011983, for license types that are only valid in one school district or educational service center | Compared to District IRN on EMIS Profile, or District IRN entered to determine if a warning should be reported because the employee is not associated with the district. |
12 | Status | Character | Indicates the status of the credential. All should be in “issued” or active status. | Not Used | |
13 | Issue Date | 8 | Character | YYYYMMDD format | Certification Window – Issued Field (emp_certificate.iss_date) This date is also used as the issue date for any teaching fields, endorsements and limitations (ohcert_teach.iss_date, ohcert_endors.iss_date, ohcert_limit.iss_date). |
14 | Expiration Date | 8 | Character | YYYYMMDD format, is null for permanent licenses | Certification Window – Expiration Field (emp_certificate.exp_date) This date is also used as the expiration date for any teaching fields, endorsements and limitations (ohcert_teach.exp_date, ohcert_endors.exp_date, ohcert_limit.exp_date). |
15 | Effective Year Name | 60 | Character | Example: 2007 | Certification Window – Registration Field (emp_certificate.reg_date - Date is set to 7/1/Effective Year) |
16 | Teaching Field Legacy ID 1 | 6 | Character | Alternates between ID and Name 18 times | Certification Window – Teaching Fields Option – Teaching Field (ohcert_teach.field_id) |
17 | Teaching Field Name 1 | 60 | Character | If description is null, uses name | Certification Window – Teaching Fields Option – Teaching Field Field as description (ohcert_teach.field_desc) |
18 – 50 | Teaching Field Legacy ID 2 through 18 | 6 | Character | Certification Window – Teaching Fields Option – Teaching Field (ohcert_teach.field_id) | |
19 - 51 | Teaching Field Name 2 through 18 | 60 | Character | Certification Window – Teaching Fields Option – Teaching Field Field as description (ohcert_teach.field_desc) | |
52 | Endorsement Legacy ID 1 | 6 | Character | Alternates between ID and Name 6 times | Certification Window – Endorsements Option – Endorsement Field (ohcert_endors.endorse_id) |
53 | Endorsement Legacy Name 1 | 60 | Character | If description is null, uses name | Certification Window – Endorsements Option – Endorsement Field as description (ohcert_endors.endorse_desc) |
54 – 63 | Endorsement Legacy ID 2 through 6 | 6 | Character | Certification Window – Endorsements Option – Endorsement Field (ohcert_endors.endorse_id) | |
55 - 63 | Endorsement Name 2 through 6 | 60 | Character | Certification Window – Endorsements Option – Endorsement Field as description (ohcert_endors.endorse_desc) | |
64 | Limitation Legacy ID 1 | 6 | Character | Alternates between ID and Name 6 times | Certification Window – Limitations Options – Limitation Field (ohcert_limit.limit_id) |
65 | Limitation Name 1 | 60 | Character | If description is null, uses name | Certification Window – Limitations Options – Limitation Field as description (ohcert_limit.limit_desc) |
66 - 74 | Limitation Legacy ID 2 through 6 | 6 | Character | Certification Window – Limitations Options – Limitation Field (ohcert_limit.limit_id) | |
67 - 75 | Limitation Name 2 through 6 | 60 | Character | Certification Window – Limitations Options – Limitation Field as description (ohcert_limit.limit_desc) | |
76 | District IRN1 | 6 | Character | Alternates between district IRN and reporting building IRN 15 times | Compared to District IRN on EMIS Profile, or District IRN entered to determine if a warning should be reported because the employee is not associated with the district. |
77 | Reporting Building IRN 1 | 6 | Character | Not Used | |
78 | Position Code 1 | Character | The code associated with the position assignment of the employee. | Not Used | |
79 | Position Description | Character | The description of Position Code 1. | Not Used | |
80 - 106 | District IRN 2 through 15 | 6 | Character | Compared to District IRN on EMIS Profile, or District IRN entered to determine if a warning should be reported because the employee is not associated with the district. | |
81 – 107 | Reporting Building IRN 2 through 15 | 6 | Character | Not Used | |
82 – 110 | Position Code 2 through 15 | Character | Not Used | ||
83 – 113 | Position Description 2 through 15 | Character | Not Used |
Note
As of June 2017, details about the data file format can be found at:
http://education.ohio.gov/Topics/Data/Frequently-Requested-Data/Staff-Data