School Data Mapping Worksheet (Step 3)

Your first task is to fill out the linked School Data Mapping Worksheet. This worksheet helps us understand what you are sending, and if you have any unique field names that we need to translate. From the Data Mapping tab, please enter a YES or NO in column C to tell use what data elements you are sending. Your implementation manager may have discussed adding a couple of custom fields to your GuideK12 instance. If that is the case, go ahead and add those rows in the Custom Field Section on the bottom of the School Data Mapping Worksheet and remember to complete column C.

Student Data Mapping Worksheet (Step 4)

Time to fill out the linked Student Data Mapping Worksheet. This worksheet helps us understand the student elements you are sending, and if you have any unique field names that we need to translate. Your implementation manager may have discussed adding a couple of custom fields to your GuideK12 instance. If that is the case, go ahead and add those rows in the Custom Field Section on the bottom of the Student Data Mapping Worksheet. Yes in column C means you are sending the field and No means it is not included.

Next we need to understand the values associated with SPECIAL_ED, for example, in your district. Think of them as the values you want to see in a pop-up or multi-select menu. If you have this type of field, one that may not be understood by all users, you need to use the look-up tabs on the bottom of the worksheet to outline each value and description. Column one of each tab contains the value you are sending us in the .csv file, and column two holds the description for that code value.

We will assume you emailed your mapping worksheets, either downloaded the encryption key and installed WinSCP or received your SFTP username and password credentials, and are ready to begin creating your .csv files. The data mapping worksheets will remind you what to pull, and the following information will help you format it properly.

The Schools CSV Files (Step 7)

Time to create your .csv file, where each field, i.e. DISTRICT_ID, GRADES_SERVED, etc is a separate column. Each row in the file represents a School in your District. If you have not spent much time working with .csv files, please refer to this Wikipedia page .

Remember to take note of the following suggestions. A) All the fields below are bold and suggested for implementation. B) For most districts, the school file is easier to create by hand, rather than exporting from an SIS. Excel tends to drop leading zeros, so make sure to check your output carefully. C) Please name your school file schools_current.csv. D) In review, here are the primary fields in the proper order for schools_current.csv:

• DISTRICT_ID – Unique school number or ID (e.g. 0040)
• NAME – Name of the school
• ADDRESS – Physical school address including building # and full street name (e.g. 123 Main St N)
• CITY – Full city name where school resides
• STATE – 2 digit state abbreviation where school resides
• ZIPCODE – 5 digit zip code where school resides
• MAIN_PHONE – Main phone number for the school (e.g. 715-755-3602)
• SCHOOL_TYPE – 4 character type of school (Elem, Midd, High, etc.)
• CAPACITY – Total student capacity for the school
• START_TIME – Start time for the School (e.g. 7:15)
• END_TIME – End time for the School (e.g. 4:15)
• MAIN_URL – Full web address for the school (e.g. http://www.myschool.com)
• ROOMS – Number of instructional classrooms in the school
• IN_SCHOOLSEARCH – Specify if you want a school to show up in SchoolSearch or not (please use “Y” for Yes and “N” for No)

The Students CSV File (Step 8)

Time to create your .csv file, where each field, i.e. STUDENTID, FIRSTNAME, etc is a separate column. Each row in the file represents a K-12 child your District is serving. If you have not spent much time working with .csv files, please refer to this Wikipedia page .

We have looked at a ton of files, so please take note of the following suggestions. A) All the fields below are bold and suggested for implementation. B) Exporting .csv files from your SIS usually provides clean data. Opening the files in Excel tends to introduce issues, like dropping leading zeros. When possible, always produce your .csv file directly from the source using a .csv writing library. C)Since a .csv file uses commas to delimit fields, use a proper text qualifier (usually quotes) if you have fields that contain commas. Oh, and if your values have quotes, please properly escape them upon export. D) Please name your student file students_current.csv. E) In review, here are the primary fields in the proper order for students_current.csv:

• FAMILYID – Family identifier of the student
• STUDENTID – Unique student identifier
• FIRSTNAME – First name of the student
• LASTNAME – Last name of the student
• GRADE – Grade level of the student
• GENDER – Gender of the student
• ETHNICITY – Student ethnic code
• GUARDIAN1_FIRSTNAME – First name of guardian 1
• GUARDIAN1_LASTNAME – Last name of guardian 1
• GUARDIAN2_FIRSTNAME – First name of guardian 2
• GUARDIAN2_LASTNAME – Last name of guardian 2
• PHONE_PRIMARY – Primary family phone number
• EMAIL_PRIMARY – Primary family email address
• SCHOOL_ID – Building code for the current school of attendance, which must match an entry in the schools_current.csv file
• SCHOOLNAME – Building name for the current school of attendance
• ADDRESS – Student address including house # and full street name (e.g. 123 Main St N)
• UNIT_INFO – Apartment number or other unit identifier (e.g. Apt 2)
• CITY – Full city name where student lives
• STATE – State where student lives (2 digit State abbreviation)
• ZIPCODE – 5 digit zip code where student lives
• MAIL_ADDRESS – Student mailing address including house # and full street name (e.g. 123 Main St N)
• MAIL_UNIT_INFO – Mailing apartment number or other unit identifier (e.g. Apt 2)
• MAIL_CITY – Mailing city name
• MAIL_STATE – Mailing 2 digit State abbreviation
• MAIL_ZIPCODE – 5 digit mailing zip code
• RESIDENT_DISTRICT – School district where the student lives
• HOME_LANGUAGE – Home language classification of the student
• FREE_AND_REDUCED – Free and reduced classification of the student
• SPECIAL_ED – Special education classification of the student
• ESL_ELL_STATUS – ESL/ELL classification of the student
• TITLE_ONE – Title I classification of the student

Shapefiles (Step 10)

Shapefiles spatially describe geometries: points, polylines, and polygons. Your project manager will speak with you about getting shapefiles for the following elements:

• ENROLLMENT (Elem, Midd, High, etc) BOUNDARY DATA
• SCHOOL POINT LOCATIONS DATA (IF AVAILABLE)
• WALK ZONE DATA (IF AVAILABLE)
• OTHER MAPPING FILES TO BE USED AS OVERLAYS