Basic Quick Steps on How to Import a File:

1. Go to Assessments>Setup>Assessment Templates. 
2. If the Assessment Definition does not exist, click "Add an Assessment Definition."
3. If you see it on the list already, click the blue icon to the right of the definition (such as SAT, PSAT etc) which indicates "Upload New Results File":
   
4. On the RESULT FILEStab, upload the result file to Genesis by clicking the "Upload New Results File" button. 
   1. You will be prompted in the pop up to upload a new file or replace current file.
5. After the file is uploaded, map the relevant columns using the "Type of Column" column and the "Map to Score" column.   
   1. For example, Genesis needs to know which columns in the Result file can identify a student - so you must always map the STUDENT ID column. 
   2. And then you need to map which columns IN your excel to what they are within Genesis (Reading Scores, Math Scores etc.)
6. Once you have identified enough columns to identify a student; click the 'Save' button at the bottom of this Result Files tab.
7. When you think have all of the score columns mapped and configured correctly and have clicked SAVE, click the "Import Scores from Selected File" button on the Result Files tab. The import engine will then try to import all rows into Student Score Records. 
8. After clicking import, you will be brought to a screen that will show you any errors (as in, students who can not be located based on the mapping and the result file.)
9. At this point, you key in the correct student id and click the Validate Student ids button OR you can check off "Ignore students that cannot be located" and then click "Validate Student IDs and Save" to just continue on with the import.
10. After you click "Validate Student IDs and Save" - you will see a pop up that indicates how many scores were successfully updated.

Scroll down for more in depth information on the above steps.

TABLE OF CONTENTS

• 1. Add a New Assessment
• 2. Upload the Result File to Genesis
• 3. Identifying Students in the File
• 4. Identify Score or Other Data Columns
• 5. Importing the records
• Additional Assessment Import Information

1. Add a New Assessment

This is added/updated via Assessments>Setup>Assessments.

If the Assessment Definition does not exist, click "Add an Assessment Definition."

If you see it on the list already, just click the blue icon to the right of the definition (such as SAT, PSAT etc) which indicates "Upload New Results File":

If you need to add a new definition:

1. Click the 'Add an Assessment Definition Button' to open a pop up box:
   
2. Fill out this pop up box with all the required fields: 
   1. Name
   2. Description 
   3. Seq - Sequence number for the assessment. Controls where on the list of assessments this will sort into
   4. Test - This is the name of the test that is in the file you are about to import
   5. Copy Columns From - Allows you to copy the column setup from a previously existing Assessment
3. Click the 'Add Assessment' button to finish up and add the assessment.

If the Assessment Definition is already in the list:

1. Click the blue icon to the right of the Assessment definition on the Assessments>Setup>Templates tab:
   

1. After clicking the blue icon, you will be directed to the Results file tab - Assessments>Setup>Assessment Templates>Result Files tab:
   

3. At this point, you key in the correct student id and click the Validate Student ids button OR you can check off "Ignore students that cannot be located" and then click "Validate Student IDs and Save" to just continue on with the import.
4. After you click "Validate Student IDs and Save" - you will see a pop up that indicates how many scores were successfully updated.

2. Upload the Result File to Genesis

Next, you need to actually locate the file and upload it.

On the RESULT FILES tab, upload the result file to Genesis by clicking the "Upload New Results File" button.

You will be prompted in the pop up to upload a new file or replace current file.

 Click on the 'Choose File' button; and select the file from your local machine that you want to load into the system.

1. Click on the 'Upload File' button to send it to the system. 
2. Genesis will analyze the file and try to determine what kind of file it is. 
3. The import file record will then be created in Genesis; and depending on the type of file you choose (Excel, Comma Separated Values, Tab Delimited, or Positional), refer to that section of the documentation to see what to do next.

3. Identifying Students in the File

You are immediately greeted with the warning "There are not enough columns mapped to identify a student.".

Genesis needs to know which columns in the Result file can identify a student.

After the file is uploaded, map the relevant columns using the "Type of Column" column and the "Map to Score" column.  

1. For example, Genesis needs to know which columns in the Result file can identify a student - so you must always map the STUDENT ID column. 
2. And then you need to map which columns IN your excel to what they are within Genesis (Reading Scores, Math Scores etc.)
3. Example of how you might start mapping it:
   
4. Once you have identified enough columns to identify a student; click the 'Save' button at the bottom of this Result Files tab.
   

4. Identify Score or Other Data Columns

Make sure you identify at least 1 score or other data column to import into Genesis. 

There are 6 standard score columns:

And 194 optional other scores / data we can import to:

If your file only has the built in scores, you can use the built in options of Lang Arts, Reading, Science, Writing etc to map with.

All remaining scores (cluster scores and other random pieces of data you wish to capture) should be flagged as Other Score or Data.

See screenshot below:

Result Files Tab:

5. Importing the records

When you think have all of the score columns mapped and configured correctly and have clicked SAVE, click the "Import Scores from Selected File" button on the Result Files tab. 

The import engine will then try to import all rows into Student Score Records.

After clicking import, you will be brought to a screen that will show you any errors (as in, students who can not be located based on the mapping and the result file.)

If a student cannot be located, you will be able to key in the correct student id and click the Validate Student ids button.

OR you can check off "Ignore students that cannot be located" and then click "Validate Student IDs and Save" to just continue on with the import.

To troubleshoot WHY certain kids are being skipped, take a look at the ERROR KEY to determine why they could not be imported:

There should be a symbol next to each student, and when you hover over it, it will tell you why they cannot be imported.  

If you want to ignore those students and import the other students, click "Ignore Students that Cannot Be Located" and click "Validate Student IDs and Save."

You'll need to review the students being skipped and possibly review the assessment file you are working with.

ERROR KEY:

• Student has no score in this file. Check mapping.
• Student has multiple scores in this file with same key (Assessment Test/Exam/Year/Month).
• Scores with duplicate keys will NOT be imported.
• Student already has a score record for this Assessment in the system
• Students may only have ONE score per Test Name, Exam Name, Assessment Year, Assessment Month. Score will be skipped.
• Student has an invalid value in one of their columns.

How a student is identified

A specific search is only executed if there is data to search for. For example; if the local id column is blank in the file; the engine will not try to search for it. The process stops when a student is found.

1. Search by the state id in the import file's school year
2. Search by the local id in the import file's school year
3. Search by the state id in the current system school year
4. Search by the local id in the import file's school year
5. Search by an exact match on first and last name (not case sensitive) in import file's school year and if only 1 result is found.
6. Search by an exact match on first and last name (not case sensitive) in the current system school year and if only 1 result is found.

If all students are identified; the engine enters into Pass 2 automatically.

Pass 2: Import Records

Pass 2 is where import engine actually imports the data into the system.

Additional Assessment Import Information

Student Score Record Columns

Create Score Columns from the Result File:

Once the file that contains the scores is loaded into Genesis and it's columns described; it can now be mapped it to the Student Score Record. 

On the Result File Layout tab; you should see something like this:

To automatically create the score column descriptions (more on this in a moment); click on the "Click here to automatically…" button.

Name

It is advisable to change the name to be as short and descriptive as possible. This name is used in various places throughout the system. In the above screen shot; position 7 should probably be renamed to LAL Raw.

Position

This identifies which number in the Student Score Record the value will be stored into. During the import process; it will be automatically determined if the value is an alpha or numeric score. Note how the first 6 scores are standardized and cannot be changed.

Type Of Score

This field describes what kind of score is stored in this field. The default value is SCALE for the standardized scores; and RAW for all other scores. The types of scores that are available are:

Proficiency Scale

If the data in this column can be mapped to a proficiency; you can select the scale from the drop down list. More on this later.

Student Tab

This column will by default appear on the student data tab. This option may be going away as we redesign the student tab screen.

Transcript

This data will appear on the transcript when using the ASCORE_ macros.

Type

Allow anything, only alpha, only numeric or integers (Numeric = with decimals, Integer = whole numbers)

Min/Max #

Ensures that values only in this range are accepted

RegEx

For the really advanced user; a Regular Expression pattern can be specified to validate the score.

File Types Supported

Microsoft Excel (xls and xlsx)

Important facts about Microsoft Excel files:

• .xls and .xlsx file formats are supported.
• The data must exist on the first sheet in the file. (See Figure 1).
• The first row can optionally contain headers (See Figure 2)
• Headers can appear on further down than the first row

Comma Separated Values (CSV)

• A file where the values are separated by commas
• A field can optionally be surrounded in quotes. "
• Quotes can be escaped using two quotes. ""
• The first row can optionally contain headers (See Figure 2)
• Headers can appear on further down than the first row

Some examples of valid CSV files:

STUDENT_ID,STATE_STUDENT_ID,LAST_NAME,FIRST_NAME,FULL_NAME,TEST_SCORE

123456,0012345,Zinn,Christopher,"Zinn, Christopher",99

"STUDENT_ID","STATE_STUDENT_ID","LAST_NAME","FIRST_NAME","FULL_NAME","TEST_SCORE"

123456,0012345,Zinn,Christopher,"Zinn, Christopher",99

"STUDENT_ID",LAST_NAME,FIRST_NAME,TEST_SCORE

"00123456",Zinn,Christopher,99

TAB Delimited

• Each field is separated by a TAB character (ASCII 9)
• The first row can optionally contain headers (See Figure 2)
• Headers can appear on further down than the first row

An example of a TAB delimited files:

STUDENT_IDSTATE_STUDENT_IDLAST_NAMEFIRST_NAMESCORE

12341234234210923543951231Zinn  Christopher90

Positional Files

A positional file is where a range of columns determines where the data is located within the row of a file. For example; it might be the case that columns 1 through 2 are the grade level, and columns 3 through 15 are the last name. The files never have header information in the first row. Such a file might look like this when viewed in a text editor (such as notepad):

Sample Positional File

08Zinn

08Sponheimer

08Smith

08Dilts

The vendor of the file must supply a document that describes the columns within the file. One such vendor that is known to publish positional files is the College Board Advanced Placement file. They provide a layout document that looks like something like:

The values circled in green are the from and thru column numbers. Later in this document will describe how to use this information to import a file.

Files (Without Headers)

If the file you imported was a CSV, TAB or Excel file without headers; then the header/desc field will be "editable".'

• It will be important that you have a document from the vendor that created the file that describes each column; because without headers you will have no idea what each column is.

Files (With Headers)

If the selected file is a CSV, TAB or Excel file with headers; then this screen should appear after importing the file into Genesis:

Skip first x rows of file

The option 'Skip first x rows of file' is for files where the first few rows of it are extra information that should be ignored.

• Fill this field out with the number of rows you would like to skip and when you click the 'Update and re-analyze' button that number of rows will be skipped over

For example; consider the following NJ-ASK File that was created by the NJ-Smart Ed Analyzer Portal:

• You would set this option to 3; so that the import engine will completely ignore the first 3 row

Date Taken

This column will do the following:

• Store the date/time in the dateTaken field on the Student Assessment Score record.
• The test year and month will be taken from this field instead of the default that was specified when this import file was created.

The Option column must be filled in with the format that the date is in the file.
 Here are some examples:

Exam Name

This column's value will be stored into the examName field on the Student Assessment Score record.

PSAT Options

The PSAT positional file uses some obscure rules for storing its data.  These are some options you can use in the appropriate fields to translate that data during the import.

PSAT_GRADE_LEVEL

• 1=<8 (not yet 8)
• 2=8
• 3=9
• 4=10
• 5=11
• 6=12
• 0=Other
• 9=No Response 
  • 1,0 and 9 - Genesis will use the grade level on the student from that school year / if data is not available it will be blank
  • 2-6 will be translated to 8-12.

PSAT_GPA

• A=A+
• B=A
• C=A-
• D=B+
• E=B
• F=B-
• G=C+
• H=C
• I=C-
• J=D+
• K=D
• L=E or F

PSAT_RAW

• (-07 through +38) 
  • If 2nd position is A – I or { the number is positive.
  • If 2nd position is J – R or } the number is negative.
• {=0, A=1, B=2, C=3, D=4, E=5, F=6, G=7, H=8, I=9}=0, J=1, K=2, L=3, M=4, N=5, O=6, P=7, Q=8, R=9 
  • EXAMPLE 5D = +54
  • 5M = -54
  • 5{ = +50
  • 5} = -50

Unknown or Incorrectly Identified Files

Rarely, Genesis will not be able to figure out what kind of file was uploaded.  It is usually because the file is in a really obscure format.

• If this happens; you can manually adjust what kind of file this is and click the 'Update and re-analyze' button.

Grade Level

This data will be loaded into the StudentAssessmentScore record.  If a column of type "Grade Level" is not specified, then this field will remain blank in the student record.

Additional Documentation Attachments

You can optionally load an unlimited amount of additional documentation to this import file.

The files can be in any format such as PDF, Word or Excel.

It is advised that you attach anything you have regarding this assessment to this screen.