Importer

The Importer module contains the tools designed to simplify the process of moving data from the previous/current software to Focus. The Importer can also act as a substitute for the Import Tools module. The Importer involves various importing tools, importing methods, specific required fields, and mapping templates.

Before importing data into Focus, you must set up each field that pertains to all data be imported.

Using the Admin Panel

1. From the Setup menu, select Importer.

2. At the bottom of the screen, click the Admin button.

3. The Admin Panel contains four options: Configure Import Keys, Show Import History, Import Scheduled Jobs, and Manage Test Parsers. Click the desired option.

4. Settings: Configure Import Keys: This option displays the fields each import tool uses during the import process. These are the fields the application uses to determine if the data from the imported file will be added, deleted, or updated.

a. Select the Table from the pull-down.

b. The Import Key(s) for the selected table are listed; make your selections from the provided pull-down(s). To add an additional import key, click the addition icon (green plus sign) next to the last field in the list.

i. An additional pull-down displays at the bottom of the list.The pull-down contains the available columns for the selected table. In the pull-down, select the desired field.

c. To delete an import key, click the delete button (red minus sign) next to the field.

Deleting existing import keys is not recommended.

d. When finished, click Save. A confirmation message will display at the top of the screen.

e. Click Exit to return to the Admin Panel.

Changing key fields will affect any job that runs after the changes are saved. Any jobs that have already been scheduled will not be affected. Proceed with caution and always test changes in a non-production environment, as changing key fields may not produce the expected results.

5. Analytics: Show Import History: This option displays a history of all past processes triggered by the Importer, including the path of the imported file within the /export directory, the import Method, the File name, the file Format, the Mapping Template used, the Date / Time the import successfully ran, etc.

a. Click Exit to return to the Admin Panel.

6. Schedule: Import Scheduled Jobs: This option displays scheduled jobs that were scheduled through the Importer, including the template Name, the import Tool, the Method used, the Time and Days the job was scheduled, the File, the Format, the Mapping Template, and an edit link to modify the scheduled job settings.

a. Click the edit link to modify the settings for the scheduled job.

For more information on editing existing jobs, see the article titled, Scheduled Jobs.

b. Click Exit to return to the Admin Panel.

7. Templates: Manage Importer Templates: This option allows you to download or delete templates.

a. Select a Template from the provided pull-down.

b. Click the Download button to download the template to your computer.

c. Click the Delete button to delete the template.

d. Click the Exit button when finished to return to the Admin Panel.

8. Test History: Manage Test Parsers: This option allows you to standardize the file format of any test score file and make the process of importing test scores more efficient.

a. To review and/or edit an existing Parser, select it from the pull-down. To Create A New Test Parser select that option from the pull-down.

b. Enter a Name for the new test parser in the provided text box.

c. Enter Parser Logic in the text box provided. There is already the beginning of a query that could be entered for Parser Logic.

Always use the variable {Parser_Table} in the INSERT statement when creating a new test parser. Always use the variable {Source_File} in the FROm clause of the SELECT statement that references the file to be parsed. If that file doesn't include headers use the variables {col_1}, {col_2}, ..., {col_x} as fields in the SELECT statement, or as substitutes for the original headers if the file includes them.

d. When finished, to create the new test parser, click the Save button. To return to the main Importer screen, click the Exit button either before or after saving test parser data.

Importing Files & Import Tools
Import File Layout
General Guidelines

The Focus SIS includes a set of tools to assist users in the process of importing data. This document, which is intended to familiarize you with the basic requirements, serves as an instrument to successfully achieve that goal.

Data Extraction & Format

In general, the import process expects to receive data files that are formatted in a way that helps with internal data conversion. These data extracts are referred to as your export files. A set of helpful tips is provided in each layout to assist you in this process.

Focus can process export files that have the following characteristics:

a. Files with data exported as comma-separated values with each field value enclosed with double-quotes ("text string") and with a .csv file extension, or

b. Files with data exported in tab-delimited format and with a .txt file extension

All of your export files should be UTF-8 encoded and need to include the carriage return (CR) or line feed (LF) as line breaks at the end of each record.

Unless a different format is specified, all year values should preferably be sent as YYYY, while date values should be sent as YYYYMMDD. Year and date values should have the same formats across all year and date fields, respectively, within one same export file.

Other accepted formats for date include: YYMMDD, DDMMYYYY, MMDDYYYY, MMDDYY, and their variants with either dash "-" or slash "/" (e.g. 2015-03-27 or 03/27/2015)

Timestamps as part of the filename are accepted, however, they should not be included for files that will eventually be set to be automatically imported into Focus on a regular basis. For those files the name should not change so that the process  is able to locate them in the server. This means automated files will always be overwritten and replaced with a new export file.

Code Set Up

In some cases you will need to know which codes have to be sent for a specific set of fields depending on how they are setup in Focus. Whenever applicable and for optimal import results make sure the following have already been created when a file is ready to be imported:

  • User Profiles
  • Grade Levels (setup by school)
  • Student and Schedule Enrollment Codes (setup by year)
  • Periods and Bell Schedules (setup by school and year)
  • Marking Periods (setup by school and year)
  • Calendars (setup by school and year)
  • Graduation Subjects (setup by school)
  • Graduation Requirements (setup by school)
  • Grade Scales (setup by school)
  • Grade Posting Averaging (setup by school and year)
  • Tests, Test Parts, and Test Score Types
  • Attendance Codes (setup by school and year)
Additional Data

The layouts provided in this document list all the fields needed to meet regular system functionality as well as state reporting needs. If your district or school(s) track data that has not been included in these layouts and the fields have already been created in Focus, you can append those to the end of the appropriate file, or create a new export file altogether.

Importing a File

1. From the Setup menu, select Importer.

2. The main Importer screen displays the Tool pull-down, the File browser, the Use a server file link, and the notes section on the right side of the screen. Along with a START and ADMIN button along the bottom of the screen.

3. While hovering over specific areas on the screen, the notes section will change to reflect specified guidelines for each section. In the example shown, the mouse is hovering over the File section; therefore, the notes section displays: Select a file from your computer. Remember to choose an accepted formated: CSV or Tab-delimited.

4. Start by selecting a Tool from the provided pull-down.

For a description of each listed Tool, see the section titled, Tools Explained.

5. Select a File from your computer by clicking the Choose File button or Use a server file by clicking the corresponding link.

It is important that the file selected be in one of the two acceptable formats: CSV or Tab-delimited.

6. If the file selected contains a header, select the File Header check box.

7. Select the Format of the file selected from the provided pull-down. Options include CSV (comma-separated values) or Tab-delmited.

8. In this case, a Method must also be defined for the selected file. Select the correct Method from the provided pull-down. Options include Delete and Insert, Insert Only, Update Only, and Update or Insert.

a. Delete and Insert: Deletes existing records, then inserts new ones.

b. Insert Only: Insert all file records that do not exist in the database.

c. Update Only:Updates existing records using file data.

d. Update or Insert: First updates existing records, then inserts new ones.

The Imported column of a table will contain the following values to represent the import method that was used:

D: Delete and Insert

I: Insert Only

U: Update Only

P: Update or Insert

Please Add to Importer Help Link - lizetteg@focusschoolsoftware.com - Focus School Software Mail

9. Select the Primary Key from the pull-down; options include SIS or Custom. The Primary Key selection aids in inserting a legacy value in the Focus identity column.

The Tool selected from the pull-down will determine the additional fields that populate the screen. The Primary Key will display for the Students and Users tables only.

10. When all selections have been made, click the START button.

A descriptive error message will display at the top of the screen in red if any of the selected options were invalid.

11. The next screen is used to map the fields in the file to the corresponding columns in the destination table. Fields are automatically mapped when the column header matches the field title. To map the rest of the file fields, select the Destination Table Column from the pull-down next to each field. If you do not want to import a field, select none from the pull-down.

12. To default a value into a table column for a field that does not exist in the file, click the addition icon (green plus sign) next to the last pull-down in the list.

13. In the added row, replace the Default Value text with the value that will default into the table column. Then select the Destination Table Column from the pull-down.

14. Add any additional defaulted fields as needed. There is no limit to the number of defaulted fields that can be added. To delete a defaulted field, click the delete button (red minus sign) to the right of the pull-down.

Only defaulted fields can be deleted in the mapping process.

15. To save the map as a template for future use, click the Save button at the top of the screen.

a. In the pop-up window, enter a Template Name and click Save Template.

Entering a Template Name that already exists will overwrite the existing template.

16. To use an existing template to map the fields, click the Load button at the top of the screen.

a. In the pop-up window, select the template from the provided pull-down and click Load Template.

17. When finished mapping fields, click the Next button.

A descriptive error message will display at the top of the screen in red if there are any mapping errors, including if any required fields were not mapped.

18. Upon clicking Next, the Settings Summary screen will display. Review the settings to confirm that all data is correct. Click the Previous button at the bottom of the screen if any settings need to be changed.

19. To import only specific rows of the file, enter the number span in the Import Specific Rows text box. For example, enter 10-20 in the text box to insert only rows 10-20.

20. To create a Scheduled Job for the import, start by entering a unique Name for the scheduled job in the text box. Next, select the Days the job will run, enter a run Time (format is HH:MM using the 24-hour clock), and enter the Email(s) of people who should receive results of the scheduled job. If entering multiple email addresses, separate the addresses with a comma. Click Schedule when finished. If any changes need to be made to the scheduled job in the future, access the created job via Setup > Scheduled Jobs.

For more information on scheduled jobs, see the article titled, Scheduled Jobs.

21. When finished reviewing the settings, click Validate.

22. The Validation Summary screen is displayed. The system has created temporary tables to hold the data while conversion and other data checks occur. This screen displays information on data integrity based on what is sent in the source file. The Importer automatically checks for numeric values where a numeric value is expected and returns an error message when the conditions are not met. The same applies to date fields. Review the validation summary and note the number of invalid rows. Review any errors displayed at the bottom of the screen. Error logs are generated for each type of error encountered during the validation process. Click the link to download the error log.

23. Error logs are plain text files that can be used to troubleshoot invalid data in the import file. It is a copy of the original files data that only includes the rows with invalid data for the type of error display displayed in the summary. A row_id is appended as the first column of the file for easier identification. Error logs are stored in /uploaded-assets/export/importer_error_files.

Open the file in Excel, delete the summary rows, and parse it out using the Text to Columns feature using comma as the delimiter for easier reading.

24. To continue with importing the data, click Continue. This begins the process of committing the changes to your data, except for those rows flagged as invalid. To cancel the import, click Cancel.

25. On the Final Report screen, statistics on the affected rows are displayed, along with conversion and execution times. The import job is logged in the Admin Panel. Click Return to go back to the main Importer screen.

If an error occurs during the import step, this screen will not display and no changes will take affect. The Importer will display an error message at the top of the previous Validation Summary screen.

Tools Explained

Address Catalog

Attendance Calendar

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Year, School Id, Title

Required Fields: Minutes for Import, School Date for Import

If the column Minutes is not available in your file, a value of 999 must be defaulted.

Course Catalog

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Year, School ID, Short Name

Required Fields: Title

The Course Catalog import tool imports data into the course catalog which is used to validate any course and section data that is input into Focus. To utilize the course catalog, the system preference for course catalog must be turned on.

Course History

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Year, School ID, Student ID, Marking Period ID, Course Number

Certain fields, although not required, are very important flags for course history records. The COURSE_HISTORY column is a Y/N field used to flag records that should be included in the students transcript.

Instructor names are stored in a text field, and so the full name, not the staff_ID, of the teacher who taught the course will need to be included as a field in your file if you wish to have this information displayed.

To import exam grades, put an "E" in front of the marking period short name.

Directions for Large School Districts: The Course History import is a very processor intensive import, as the course history table can easily contain millions of records and running the tool in Update/Insert requires the entire course history data to be compared and processed locally. Because of this, the tool can quickly run out of memory; Firstly, its recommended that data be deleted and then inserted on larger districts. Secondly, it is recommended that for each year of course history a separate file be created. When loading each file consider that there must not be any of the course history records that are in the import file already in the database or the import tool will create duplicate records. Run the import in Insert Only mode instead of Update/Insert.

Course Periods

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School ID, Course ID, Short Name, Marking Period ID

Required Fields: School Year, Teacher ID, Subject ID

The Course Periods import tool is the second part of the master schedule import, and should be run after the Courses import. Section data is what assigns a teacher to a period and room, a student to a class, and contains other elements that are an integral part of scheduling. Most of the section data filters down to the students schedule when the schedule is built in Focus.

Marking periods should be set up prior to this import. Marking periods must be included in the Course Periods import for the Student Schedules import tool to work.

Courses

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Year, School ID, Course Title, Short Name

Required Fields: Credits for Import, Subject ID, MP

The Courses import tool is used to import course data, and together with the Course Periods import tool, makes up the master schedule.

Course Subjects

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Year, School ID, Short Name

Required Fields: Title

Daily Attendance

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: Student ID, School Date

Required Fields: School Year, Daily Code

It is recommended that daily attendance files are split by year. Files should be sorted by School Number, School Year, and School Date. Prior to importing attendance it is important to make sure Attendance Codes are set up for all school years being imported, and that the short_name values for your codes set up in Focus match the values in the "Attendance Code" field in your import files.

Periods will need to be copied back to prior years before importing historic years' period attendance.

Period Attendance

Import Methods Available:Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields:Student ID, School Date

Required Fields:School Year, Daily Code

It is recommended that period attendance files are split by year. Files should be sorted by School Number, School Year, and School Date. Prior to importing attendance it is important to make sure Attendance Codes are set up for all school years being imported, and that the short_name values for your codes set up in Focus match the values in the "Attendance Code" field in your import files.

Periods will need to be copied back to prior years before importing historic years' period attendance.

Rooms

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School ID, Short Name

Required Fields: Resource Category

Resources are where Focus typically stores room data. After importing, make sure to set up at least one Resource Category in Setup > Resource Scheduler > Setup the Resources (e.g. Rooms).

Schedule

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School ID, Student ID, Course ID, Course Period ID, Course Weight

Required Fields: School Year, Subject ID

Student schedule imports are processor intensive. This tool assigns students to sections in the master schedule.

When running a Schedule import in update mode, you must include a start date as the tool will otherwise search for schedule records with the current date as the start date.

Schedule Requests

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Year, School ID, Student ID, Course ID

Schools

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Number

Required Fields: Title

The Schools import tool is especially useful for districts with a large number of schools. To preserve the school numbers from your legacy system (or use your state DOE's Assigned numbers), make sure to set Primary Key to CUSTOM_327.

Student Addresses

Students can be tied to one or more addresses. Each address record contains a residential address. If the students mailing address is different from the residential address, the mailing address should be on the same record as the residential address. Each address record can be flagged for Mailing (a Y/N field). This determines whether an address record should be included when printing Avery labels. The Avery labels will automatically use the mailing address if it is listed.

Import Methods Available: Delete and Insert

Primary Key Fields: Address

Required Fields: Student ID

Student Contacts

Students can have one or more contacts. Each contact record is tied to an address record. If the student contact is at the same address as the student, the address record in the contact file should be the same and flagged as Resides With Student. If the contact is at a different address than the student, this flag should not be used and the address should be different than that of the students. The relationship to the student is marked with the Student Relation field.

Focus has a number of predefined relationships: Father, Mother, Stepfather, Stepmother, Parent, Stepparent, Grandfather, Grandmother, Aunt, Uncle, Guardian, Emergency Contact, and Other.

If an option is used that is different than the Focus predetermined relationships, the field will automatically be populated with the custom relationship.

Import Methods Available: Delete and Insert

Primary Key Fields: Address

Required Fields: Student ID, First Name, Last Name, Priority

Student Enrollment

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: School Year, School ID, Student ID, Start Date

Required Fields: Include in Class Rank

Student enrollment data is what assigns a student to a school or schools for a specific enrollment period. Students will not be visible in Focus until at least one enrollment record is created for each student. The rolling/retention option can be imported into Focus as the field Next School. Student enrollment records are required to have a school ID, enrollment code, and enrollment date.

A Note on Student Enrollments Imports:

The Rolling / Retention Options are mapped as Next School. Values for this field can be:

The School ID or "Next grade at current school"

The code 0 for Retain

The code -1 for Do not enroll after this school year

For concurrent enrollment, use the field Second School and map to a Y value.

Student Log Entries

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: Student ID

Students

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: Local Student ID

Required Fields: First Name, Last Name

To insert students using the same student ID in the import file as the student ID in Focus, change the Primary Key Option field to SIS Primary Key, and set the key to CUSTOM_53 in "Configure Import Keys".

When importing students in any mode that inserts records, the student's first name and last name are required fields.

When adding additional information to existing student records, make sure to always run this tool in "Update Only" mode.

Test History

Import Methods Available: Delete and Insert

Primary Key Fields: Administration ID

Required Fields: Student ID, Administration Date, Test ID, Test Part ID

Test History uses the Short Name field and Test Part Subject Code field as the keys for mapping. This means you must have a value set up in these fields to import the data.

The test short names, test part short names, and subject codes are set up under Assessment > Standardized Tests (Setup). For more information see he article titled, Standardized Tests.

Sorting

For scores to group correctly your import file must be sorted by the following in the listed order:

1. Student ID

2. Test Short Name

3. Administration Date

Test History Imports Can Be Run in Two Ways

The first way involves importing multiple types of tests from the same file. Test History files with multiple test types should be separated by school year and sorted by student ID then administration date. Importing test files with the wrong sort order will result in duplicate administrations for each test part. When importing multiple tests, each test is distinguished by the test short name that should match what is set up in Focus. The test parts can be identified by either the test part short name or test part subject code. Whichever part identifier is selected should match what is set up in Focus.

The second is for importing a specific test. No sorting is required with this method.

Test History Parser

Pre-processing importer tool, which allows users to standardize the file format of any test score file and make the process of importing test scores more efficient.

User Enrollment

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: Staff ID, Schools

User Log Entries

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: Staff ID

Users

Import Methods Available: Delete and Insert, Insert Only, Update Only, Update or Insert

Primary Key Fields: Staff Number Identifier

Required Fields: First Name, Last Name

The Users import tool is used to import all faculty and staff. Profiles should be set up prior to this import, and your data should include a corresponding field. For the custom primary key, you will want to use either your legacy system staff_id, username (if this value is unique - no two users have the same value), or Social Security Number.

Whichever field you choose to use as the custom primary key during this import should be present in all other imports where staff_id is mapped (most importantly sections).

Troubleshooting & Case Scenarios
Troubleshooting

General Guidelines About File Formatting

  • Process text files that have comma-separated values where each value is enclosed in double quotes, or text files with data in tab-delimited format
  • The file must be UTF-8 encoded and needs to include the carriage return (CR) as line break at the end of each record
  • Always include headers in the import file
  • Year values should be sent in the YYYY format
  • Date values that can be converted to a real date are accepted, e.g. YYMMDD, DDMMYYYY, MMDDYYYY, MMDDYY and their variants with either dash (-) or slash (/)

Conversion

Conversion will happen when a value needs to be transformed into a Focus ID. These fields depend on system setup and the Importer will expect the correct value for conversion. Depending on the field the application will look for the following in the source file:

  • A SHORT NAME code: Most setup relies on SHORT NAME definitions (periods, marking periods, graduation subjects, attendance codes)
  • A TITLE: For when SHORT NAME is not an available field (profiles, calendars, grading scales)
  • An OPTION code: This applies to custom fields with the pull-down, select one, or select multiple types

Temporary Tables

The Importer generates a series of temporary tables during the validation process. Knowing these tables can help you find bad data once validated.

In general four temporary tables get created:

  • [TemporaryTable]tempimporter_file_data (stores raw data from file)
  • [TemporaryTable]tempimporter_dup (stores converted values)
  • [TemporaryTable]tempimporter_error (stores rows with invalid data and cause of error)
  • [TemporaryTable]tempimporter (stores total of converted records to be imported)

The following SQL query gives you an aggregate list of rows with errors (what you would get if you merge all error log files into one single error file)

SELECT e.*, t.* FROM studentreportcardgradestemptable_error e

JOIN studentreportcardgradestemptable t ON (t.row_id = e.row_id);

Some tools need an additional table because they have more than one destination file: Address, Contacts, Test History, Attendance. They only hold processed data.

Temporary tables are dropped and recreated every time you run a specific import tool.You should only use these tables to find potential issues with your original data.Never update or delete the contents of these tables.

Case Scenarios

You have a list of Student IDs that need to be flagged as ESE

1. Run file through the Importer

2. When mapping the fields, default the ESE field to Y

3. Validate and import data

You have a file where a year column has an invalid format

1. Dump file into temporary table with the Import File into Blank Table tool

2. In runquery, reformat the year values with a case statement or with an UPDATE query

3. Export your results as a CSV

4. Run your new file through the Importer

Your test scores file is not an accepted format

1. Convert your file to CSV with the Create CSV from Fixed Width File tool

2. Dump your new CSV file data into a temporary table with the Import File into Blank Table tool

3. Reformat your data using queries

4. Run in runquery or store query as a Manage Integrations district report

5. Export/Generate file and store in the uploaded-assets/export folder

6. Process new file with the Test History import tool

You need to generate data from multiple source files and schedule the import as a nightly job

1. Schedule a data dump of the contents of each file into the database

a. Go to the Importer, select your file, and use the Import File into Blank Table tool. Continue to the Summary screen.

b. In the Summary screen, schedule a run for the file

c. Repeat process when working with multiple source files

2. Schedule generation of new data file

a. Go to runquery. Write a select statement using your temporary tables.

b. Go to District Reports. Create district report using your custom query.

c. Go to Manage Integrations. Install report as an integration, set location path to /uploaded-assets/export, choose the file format settings in the edit options, and name your file appropriately. Assign a scheduled run for your integration.

3. Schedule import

a. Go to the Importer. Select your nightly generated file and import settings, map your fields, and continue to the Summary screen.

b. In the Summary screen, assign a scheduled run for your import

Preparation Tools
Create CSV File From Fixed Width File

The Create CSV File from Fixed Width File tool will convert an uploaded fixed-width file into a customized CSV file.

1. From the Setup menu, select Importer.

2. From the Tool pull-down, select Create CSV File From Fixed Width File.

3. Select the fixed-width from your computer or select the file from the server. To select a file from your computer, click Choose File. To select a file from the server, click Use a server file and select the file from the File pull-down.

4. Once complete, click the Start button.

5. In the File Breaking area of the screen, you can customize the column delimitation by typing in the start and end positions of the columns. In the File Columns area of the screen, you can enter the header titles. Each column should aways have its matching header title.

6. To save this as a template for future use, click Save at the top of the screen. In the pop-up window, enter the Template Name and click Save Template.

7. To use an existing template, click Load at the top of the screen. In the pop-up window, select the template in the pull-down and click Load Template.

8. When finished entering the customizations, click Next.

9. The Importer will generate and store the reformatted file in the server at /uploaded-assets/export/importer_fixed_width_files. The new file will have the suffix _TO_CSV.csv. In the import process, the file can be selected by using the Use a server file link.

Import File Into Blank Table

1. From the Setup menu, select Importer.

2. From the Tool pull-down, select Import File into Blank Table.

3. To select a file from your computer, click Choose File. To select a file from the server, click Use a server file and select the file from the File pull-down.

4. Select the Format; options include CSV or Tab-delimited.

5. The Method defaults to Insert Only.

6. Enter a custom title for the table in the Blank Table Name text box.

7. Click Start when all information is complete.

All temporary tables will be given the suffix _temp. Entering a name that already exists will overwrite the existing temporary table.

8. Next, the Settings Summary screen is displayed. Review the settings and confirm they are correct. Click the Previous button at the bottom of the screen if any settings need to be changed.

9. To import only specific rows of the file, enter the span in the Import Specific Rows field, e.g. 10-20.

10. To create a Scheduled Job for the import, start by entering a unique Name for the scheduled job in the text box. Next, select the Days the job will run, enter a run Time (format is HH:MM using the 24-hour clock), and enter the Email(s) of people who should receive results of the scheduled job. If entering multiple email addresses, separate the addresses with a comma. Click Schedule when finished. If any changes need to be made to the scheduled job in the future, access the created job via Setup > Scheduled Jobs.

For more information on scheduled jobs, see the article titled, Scheduled Jobs.

11. When finished reviewing the settings, click Validate.

12. Once complete, the Final Report screen will display the number of records that has been inserted into the table.

13. Queries and other transactions can be run on the temporary table. All temporary tables will remain in the Focus database unless manually dropped.

Match Students using Import Tools

1. From the Setup menu, click Importer.

2. Select Match Students from the Tool pull-down.

3. To select a file from your computer, click Choose File. To select a file from the server, click Use a server file and select the file from the File pull-down.

4. If the file selected contains a header, select the File Header check box.

5. Select the Format; options include CSV or Tab-delimited.

6. Enter a Birth Date Format in the provided text box; for example, dd/mm/yyyy.

7. When all selections have been made, click the Start button.

8. Map the First Name (Required), Last Name (Required), Birthdate (Required), and School (Optional) fields. For more information on this section, see step 11 of Importing a File.

9. Click Next to proceed or click Previous to go back to the main Importer screen and start over.

10. Upon clicking Next, the Settings Summary screen will display. Review the settings to confirm that all data is correct. Click the Previous button at the bottom of the screen if any settings need to be changed.

11. To set up a Scheduled Job, enter the corresponding information and click Schedule. For more information, see step 20 of Importing a File.

12. When finished reviewing the settings, click Validate.

13. To proceed with the import, click Process. You also have the option to Cancel.

14. Once processed, review the confirmation and click Return to return to the main Importer screen.

15. Once processed, from the Setup menu, select either Match Students - Admin Panel or Match Students - School Panel. Click the corresponding link for further instructions.

Importing Test Scores
How to Import Test Scores using the FLEID

For test score files that include the students' FLEIDs you may choose to skip the Match Student ID process and use your original extract to import the scores.

Setting the FLEID

1. From the Setup menu, select Import Tools.

2. Click Configure Import Keys.

3. Change the Legacy Key Field for the Students table to the FLEID (CUSTOM_200000224) field, which is usually set to Local Student ID [CUSTOM_53). Once the new Students table is updated, the information saves automatically.

Importing Scores

1. From the Setup menu, click Importer.

2. Select Test History from the Tool pull-down.

3. To select a file from your computer, click Choose File. To select a file from the server, click Use a server file and select the file from the File pull-down. Look for your parsed file under the importer_test_parser_files directory.

Hover over Upload a local file with the mouse for file hints. Additional information about the hint displayed is listed in the section on the right side of the screen.

[FOCUS-24411] Importer Enhancement: screen hints - JIRA

4. If the file selected contains a header, select the File Header check box.

5. Select the Format; options include CSV or Tab-delimited.

6. A Method must be defined for the selected file. Select the correct Method from the provided pull-down. Options include Delete and Insert, Insert Only, Update Only, and Update or Insert. For more information, see step 8 of Importing a File.

7. When all selections have been made, click the Start button.

8. The next screen is used to map the fields in the file to the corresponding columns in the destination table. Fields are automatically mapped when the column header matches the field title. To map the rest of the file fields, select the Destination Table Column from the pull-down next to each field. If you do not want to import a field, select none from the pull-down. Be sure to map the student id File Field to FLEID.  For more information on this process, see step 11 of Importing a File.

The original file does not include a Date Administered; therefore, you may choose to add a default date instead of using the date included in the parsed file.

9. Upon clicking Start, the Settings Summary screen will display. Review the settings to confirm that all data is correct. Click the Previous button at the bottom of the screen if any settings need to be changed.

10. To set up a Scheduled Job, enter the corresponding information and click Schedule. For more information, see step 20 of Importing a File.

11. When finished reviewing the settings, click Validate.

12. Address any errors that may arise, then click Import.

13. Once the import is complete, click Return to return to the main Importer screen.

Reverting the FLEID

This is a very important step, especially for districts that run other manual imports on a regular basis.

1. From the Setup menu, select Import Tools.

2. Click Configure Import Keys.

3. Change the Legacy Key Field for the Students table back to Local Student ID [CUSTOM_53) from FLEID (CUSTOM_200000224). Once the Students table is updated, the information saves automatically.

Resources

If a parser is not available in your Importer, you may create your own APT parser by selecting Manage Test Parsers via Setup > Importer > Admin and using the code included in this file.

Test Scores File Structures

There are currently three different options for importing test scores into Focus and the one that should be used will depend on how your source file is structured. Therefore, it is always important to do an initial review of the file layouts when they're generated by a third-party system before proceeding with the import. You may also rearrange the data from a source file and generate a new extract.

Files that Include Multiple Score Fields for Each Test Part

In general, this is how third-party systems will generate their scores extracts. You'll have multiple sets of score columns where each set represents a specific test part. In this type of structure you won't have a column in the file with the Part Code ID. It should also have one record per student and test administration date.

Layouts are provided by the third-party system.

File header example:

In this case, you will use the Test History Import Tool, and when prompted to select a test, you will choose the specific test for which you will be importing data from the Tests pull-down. For more details, see Import Tools: Test History.

Files that Include Test and Part Code Columns and One Set of Score Fields

This type of structure is the one we use at Focus during implementation. There's just one set of score columns but also one column for Test ID and one column for Test Part to identify the part those scores belong to for the test. You will have multiple rows for the same student and test administration.

This structure is also used in the Florida EOC tests, for example, but without a Test ID column it needs to be run with method #1 described above.

File header example:

In this case, you will use the Test History Import Tool (Setup > Import Tools), and when prompted to select a test, you will select ALL at the top of the list from the Tests pull-down. For more details, see Import Tools: Test History.

If your source file doesn't include a Test ID column, you can process your file to include it. You may also import multiple tests from one single source file, limitations on file size considered.

Files that Include Test, Part, and Score Type Code Columns

This is a new type of file structure used by the Importer that allows you to include a column for Test ID, Part ID, Score Type ID, and Score Value. There aren't any known providers that work with this structure but you may rearrange your file to this format as an alternative. You will have multiple rows for the same student and test administration.

File header example:

In this case, you will use the Importer. Select Test History from the Tool pull-down.

1. From the Setup menu, click Importer.

2. Select Test History from the Tool pull-down.

3. To select a file from your computer, click Choose File. To select a file from the server, click Use a server file and select the file from the File pull-down. Look for your parsed file under the importer_test_parser_files directory.

4. If the file selected contains a header, select the File Header check box.

5. Select the Format; options include CSV or Tab-delimited.

6. A Method must be defined for the selected file. Select the correct Method from the provided pull-down. Options include Delete and Insert, Insert Only, Update Only, and Update or Insert. For more information, see step 8 of Importing a File.

7. When all selections have been made, click the Start button.

8. The next screen is used to map the fields in the file to the corresponding columns in the destination table. Fields are automatically mapped when the column header matches the field title. To map the rest of the file fields, select the Destination Table Column from the pull-down next to each field. If you do not want to import a field, select none from the pull-down. For more information on this process, see step 11 of Importing a File.

9. Upon clicking Start, the Settings Summary screen will display. Review the settings to confirm that all data is correct. Click the Previous button at the bottom of the screen if any settings need to be changed.

10. To set up a Scheduled Job, enter the corresponding information and click Schedule. For more information, see step 20 of Importing a File.

11. When finished reviewing the settings, click Validate.

12. Address any errors that may arise, then click Import.

13. Once the import is complete, click Return to return to the main Importer screen.