Use this page to define upload and download interfaces. Interfaces are used to upload data from an external file or download data to an external file.
File Formats
The format of the upload or download file determines how you will define your interface records. Source files use one of the following formats:
Delimited format You must define a separate interface record for each field in the file, including fields being skipped. For example, if you are uploading data from fields 1, 2, 3, and 10, you also must define records for fields 4, 5, 6, 7, 8, and 9, which should have their Action fields set to Skip Field.
Fixed-length format You must define a separate interface record for each field in the file being uploaded from or downloaded to, but you can set up individual records for skipping consecutive positions in multiple source fields. For example, if you are importing data from field 1 (positions 1-5), field 2 (positions 6-10), and field 10 (positions 46-50), then you would define four detail records: three for the data fields and one for the fields being skipped. As a result, record 3 would have a field length of 35 (for positions 11-45), and its Action field would be set to Skip Field.
On the search page, search for the upload/download definition, then click its Description link.
Click Copy and select Copy to New Interface.
Enter new ID and description on the Copy to New Upload/Download Definition pop-up window.
Click OK.
Copy interface headers
You can copy one or more header records for an interface. You can copy from the same interface or from another interface. For example, if you are creating an upload for test scores, you may want to copy the previous header when adding a header for another score, to save data entry; then, you can revise the detail columns for the header as needed.
Typically, you will copy headers for upload interfaces, not download interfaces.
On the search page, search for the upload/download definition, then click its Description link.
Click Copy and select Copy Interface Headers.
In the Interface ID to Copy from field, select the interface that contains the headers you want to copy. You can copy headers from your current interface into itself.
In the Header IDs to Copy field, select the header(s) you want to copy.
In the Header IDs to Create field, specify the unique header ID to assign to the copied header(s).
If you are copying from a different interface and you want to retain the header IDs assigned in the selected interface, leave this field blank.
If you are copying from the same interface, you must define unique header IDs.
If you entered a list of headers, you need to enter a comma-delimited list of header IDs to create. Make sure that you define the new IDs in the same order that you selected the headers to copy.
Click OK.
To add the same header multiple times, repeat steps 3-7.
Fields
Interface Panel
Field
Description
Interface ID
Code identifying the interface record. Character/5
Description
Description of the interface. Character/255
Upload/Download
Determines whether this is an upload or download interface.
Select:
Download Upload
Interface Headers Panel
Field
Description
Description
Description of the interface. Character/255
Header Order
Identifies the processing order of the current header. You can skip numbers to leave space for creating additional headers. Small Integer
Filename
Filename of the source information including file extension.
Last Run Date
Maintained by the system. Contains the last time this header was run.
Delimit Character
Character used to delimit fields within the source file.
Following are characters commonly-used as delimiters:
Quoted, comma-delimited file: Q
Comma-delimited file:, (comma) Do not use a comma-delimiter if any of the fields you are downloading could include a comma in the value.
Quoted, comma-delimited file where quotes are not used if a field is blank: S
Tab-delimited file: T
| (Pipe) delimited file: |
To upload from a Fixed Length (also known as a fixed width) file, leave this field blank.
Use Change Flag
Checked if the download should add a marker at the beginning of each record if the student’s Registration (reg) record has been added, changed, or deleted since the last download, based on the Start Date entered in the Run Download option (Administration > Utilities > Download & Upload > Run Download.) This allows for smaller file downloads at large school districts.
The markers that can be included in the file:
A - New add C - Change since last download D - Delete
Only select the checkbox if the interface downloads information from Registration. In this case, a record is logged to the LTDB_INTERFACE_STU table every time a student is added to the Registration table and every time a student's Registration record is changed.
Note
Changes to contact, address, and phone information are not tracked in the LTDB_INTERFACE_STU record. You cannot use this functionality to selectively download students whose address information has changed.
Column Headers
Checked if you want to use the Description as a column header in the file to identify your data.
If the Column Headers box is checked, you will have the option of using the column's default name, as defined in the database table, or overriding the default by entering a different column name.
Table Affected
Name of the table for uploading or downloading data. You must define a separate header for every table referenced as well as for every record updated. For example, if you are uploading data to two district-defined fields, you must define two headers.
Additional SQL
Use this field to add SQL statements to be used when the header is run. Additional SQL is entered only for downloads.
You need to define SQL statements in the following situations
Your file includes fields from more than one table. You must indicate how the tables are joined to the table selected in the Table Affected field.
You need to pull more than one value for a student from a record that stores values in multiple records. For example, each district-defined field is stored as an individual record. Therefore, if you need to include data from two district-defined fields, you must define aliases for the fields and define joins and criteria to specify how data should be selected.
You want to define criteria to limit the records processed. If you want to specify criteria, but you do not need to define a join, you must begin the additional SQL statement with WHERE. For example, to select Registration records for building 2, you would enter WHERE building=2.
Do not use WHERE when including criteria with joins. Also, do not use the following character sequence in your SQL statement: – (dash dash). This is a comment sequence for SQL and is not supported in the Additional SQL field.
Click the Edit icon to display fields for entering additional details for an interface record.
For descriptions of these fields, refer to Edit Fields below.
Field ID
Unique identifier for the interface detail record. Character/5
Table Name
Table for uploading data to or downloading data from.
To skip a field or load a literal value, select DUMMY. To access fields for entering a literal value, click Edit on the current row.
Table Alias
The alias for the table. This field is typically only entered for downloading data from district-defined pages and you need to pull values from multiple records from a single table into one download record. Character/18
For example, if you wanted to download data from field 1 (make) and field 2 (model) from district-defined Screen 300 (Parking Information), you would need to define an alias for each field. You could enter make as the alias field 1 and model as the alias for field 2.
Field within the selected table for uploading data to or downloading data from.
If you are using external matching to return a value for a column, select the column to which you want to upload data. For example, if the student ID is not in the file and you are going to return the student ID by matching the first name, last name, and birthdate, select the Student ID column for all three detail rows. For detailed information about using externals, refer to External Matching.
If you select a field that is program tracked, the Run Upload will close and open program records as needed if a student's value changes for the field. For more information, refer to Uploading Data to Program Tracked Fields. Do not include fields that are program tracked and linked to a header field in the program. The Run Upload option will skip records if one of the fields in the header is linked to another field. For more information about how program tracking fields can be linked, refer to Program Setup Page.
You cannot select a column if you selected DUMMY for the table.
Field Length
Maximum number of positions for data being uploaded or downloaded.
You can truncate data by entering a length that is smaller than the length of the field in your database (downloads) or source file (uploads). For example, if you are downloading from a field that stores 15 characters, but a vendor has specified that the field can only be 12 characters, enter 12. Any characters that exceed this length are ignored.
If you are specifying a literal value, you can enter a length of 0 (zero) to make it easier to identify that the field has a literal value. However, if needed, you can enter the length of the literal value. To access fields for entering a literal value, click Edit on the current row.
Column Override
To override the database's default column name, enter a new name for the column header. If you leave the field blank, the database's default name will be used as the column header. Character/255
For example, if the default in the database is student_id, you could override this by entering Student ID.
This field only applies to downloads.
(Delete)
To delete a row, select the Delete checkbox. The row will be deleted when you save the page.
Edit Fields
These fields allow you to add details to interface records. To display the fields, click Edit to the left of the Import File Layout panel's File Field ID field.
Using these fields you can:
Set up literal values.
Specify a format for the field's values.
Provide table validation for the field.
Define external matching values.
Set up rules for handling blank or specified values in the source file.
Convert codes in the source file to specific values.
For additional information, including examples, refer to the field descriptions.
Field Details Section
Field
Description
Literal
To include the same value for all records, enter the value to upload or download. For example, if the source file does not contain the title of the test, you can include the title as a literal value. Character/30.
Literal values can either add values that are not found in the source file or replace values that would otherwise be uploaded or downloaded.
The following special variables can be used:
$USERID - to include the login of the user who ran the upload or download. $TODAY - to include the date when the upload or download was run. The date format is MM/DD/YYY, for example, 10/16/2015.
Format String
To specify special formatting for data, you can use keywords and formatting characters. For example, you can format dates, right justify values, concatenate multiple fields, and specify how numeric fields should be formatted. Character/20
For information on the formats you can apply, refer to Format Functions.
Download File Dbs Field Start Position
If you are including part of a database value in a download, enter the first character position to be pulled from the field.
For example, to include phone numbers without their area codes, use 4 as the start position.
Download File Dbs Field End Position
if you are including part of a database value in a download, enter the last character position to be pulled from the field.
For example, to include phone numbers with or without their area codes, use 10 as the end position.
Convert File Fields Section
This section enables you to convert a source file's code to a specific value in eSchoolPlus.
Field
Description
Code
Value that you want to convert when the file is uploaded or downloaded.
For example, if 250 identifies the building in your database and you will be converting this to Derry High School in the download, enter 250 as the Code and Derry High School as the Value.
Value
Value that you want to use in place of the code. Your entry must be a valid value in your database.
For example, if California identifies the state in your source file and you will be converting this to CA in the upload, enter California as the Code and CA as the Value.
(Delete)
To delete a row, select the Delete checkbox. The row will be deleted when you save the page.
Upload File Validation and External Matching Section
Externals are used to return a value in your database when the source file for an upload does not include the field value needed for importing records. They also can be used to validate values against data stored in Registration.
For more information on using externals, refer to External Matching.
Field
Description
Validation Table
To validate values from the source file based on an eSchoolPlus validation table, select the table. If a value from the source file is not found in the table, a blank is entered in the upload, and a system-generated error message appears in the log file.
Code Column
If you selected a validation table, select the eSchoolPlus field for validating source values.
Valididation List
To limit imported data to a specific set of values, enter a comma-delimited list of the valid values, for example, English,Math,Reading,Science. Character/255
Error Message
If you selected a validation table and code or entered a list of valid values, enter the text to output to the log file if an imported value does not satisfy the validation test, for example, Invalid Value. Character/255
External Table
If you are matching external data to return the data to be imported, select the table where both values are stored.
External Column In
If you are matching external data to return the data to import, select the column (field) that corresponds with the value in the source file or Literal field. This column will be used in combination with the columns for the other detail records that have the same External Table and External Column Out values to search for and return a unique value for the column indicated in the External Column Out field. If a unique value is not returned, no record will be updated or inserted, and the log file will display an error.
For example, if the source file does not include the student ID, but does contain last name, first name, grade, and birthdate, you could use these values to return a unique student ID.
You also can enter the same column in both the External Column In and External Column Out fields if you want the import to verify that the value exists in the database prior to importing the value. For example, if the student ID is in the source file, but you want to make sure that the student ID matches an ID from the database, you would enter Student ID in both columns.
External Column Out
If you are matching external data to return the data to be imported, select the column (field) for which you want to import the unique value returned based on matching data in the database for multiple columns that have values in the source file or Literal field. If a unique value is not returned, no record will be updated or inserted, and the log file will list an error.
You can also enter the same column in both the External Column In and External Column Out fields if you want the import to verify that the value exists in the database prior to importing the value. For example, if the student ID is in the source file, but you want to make sure that the student ID matches an ID from the database, you would enter Student ID in both columns.
JavaScript errors detected
Please note, these errors can depend on your browser setup.
If this problem persists, please contact our support.
