E2LA File Spec Sheet

 

The E2L system allows a county to accept a sample file via the internet, and manually collect the data via a browser-based application. The E2LA (E2L “automated”) portion of the E2L system allows counties to upload data into the E2L, bypassing the need to enter the data manually. The steps of this process are:

 

  1. Download list of records (“skeleton file”) that need to uploaded into E2L using a webpage on the E2LA website.
  2. Create a data file with the required information.
  3. Upload the data file into E2L via a webpage on the E2LA website.

 

ACCESSING THE E2LA SYSTEM

 

E2LA is located at

 

https://www.adm.dss.ca.gov/e2l/cntyb_downloader.asp

 

Users must have supervisor rights to access the E2LA site. Contact the E2L Help Desk at E2LITE@dss.ca.gov if you have questions.

 

E2LA has three web pages: Upload Dataset, Batch Data Log, and Download Dataset.


Download the E2L User Instructions

DATA REPORTING FAQs (including Projection of Hours tables)


Download the Data Reporting FAQs

 

SYSTEM PAGES

 

Upload Dataset

This page allows the user to upload completed “skeleton” files into the E2LA system. Uploaded files are evaluated on a record by record basis, so partial uploads will be accepted, as well as uploads of records that were previously rejected and have been corrected. For example, if a sample file contains 200 records, users can upload partial files that only contain 100 of the 200 records.

 

In addition to displaying the results of the upload on this page, each upload creates an entry on the Batch Data Log page.

 

Batch Data Log

This page displays a log of all data files uploaded by the county using E2LA, and includes:

 

  1. Date and time the file was uploaded.
  2. Username and ID of the person who uploaded the file.
  3. File size (in bytes)
  4. File name
  5. Number of records in the file.
  6. Number of records accepted.
  7. Number of records rejected.
  8. If there were any errors, a bad records link to a file containing error information for the rejected record(s).

 

The error file for rejected records contains the following information for each rejected record in CSV (comma separated value) format.

 

  1. Row number of the rejected record (starting from 1 as the first record).
  2. Name of the field where the error occurred.
  3. Error message.
  4. Value in the field that caused the error.

 

Download Example Error File

 

 

Download Dataset

This page allows the user to select a sample set (based on month and year) from a dropdown list, and download a list of records for that time period. If a county HAS NEVER uploaded data for that time period, the downloaded file is the “skeleton” file and only the first eight fields are populated. If a county HAS uploaded data for a certain time period, the downloaded file will contain not only the “skeleton” data but also data that was uploaded by the county via the Upload Dataset page.

 

FILE FORMATS

 

Uploaded Dataset File

 

  1. The uploaded file must be in CSV (comma separated value) format. Each data element of each row must be separated by a comma, and there should not be trailing or leading spaces on the data elements. Basically, the file format (and values) should match the skeleton file.
  2. The format of the uploaded data file should be the same as the skeleton file, with the data elements filled in.
  3. The data file may be up to 4882.8 Kbytes (a little under 5mb) in size. If the file is larger, it must be split into separate files and each file uploaded separately.  
  4. The first row of the file must be a header row containing all the field names. Field names are case insensitive (i.e. FS_ALLOT, FS_Allot and fs_allot are all treated as the same field name). Having less than the number of required fields will generate a “missing fields” error, while having additional fields will generate an “unknown fields” error. Fields can be in any order, as long as the data on the following lines keeps that same order. If there is an error with the header file, the remainder of the file will not be evaluated.
  5. Data in the file is evaluated on a record by record basis. If there is an error with an individual record, that record will be rejected, but processing of the file will continue.
  6. Each row in the data file must match to a record in the skeleton file. If the SAMPLE_YEAR, SAMPLE_MONTH, COUNTY_NUM, CASE_NUM, FBU_NUM,  AID_CODE, REVIEW_NUM, or EW_DISTRICTO differ from the values in the skeleton file, that record will be rejected.
  7. Each record must pass data validation (see below). If a record fails to pass data validation, the record will be rejected.
  8. Each record that passes data validation is then uploaded into the E2L database. If that record had been previously uploaded, the old values for that record are overwritten by the new values.

 

Downloaded Dataset File

 

  1. The downloaded file contains records in CSV (comma separated value) format. All field values will be surrounded by quotes (ASCII 34), irrespective of whether the data value is a string or not.
  2. The first row contains a list of field names, with each field name surrounded by quotes (ASCII 34).
  3. Each row following the first row will contain the values for a sampled case in E2LA. Only the following data fields will initially be populated (EW_DISTRICTO is optional):

Field Name

Data Format

SAMPLE_YEAR

YYYY

SAMPLE_MONTH

MM

COUNTY_NUM

NN

CASE_NUM

XXXXXXX

FBU_NUM

X

AID_CODE

XX

REVIEW_NUM

XXXXXX

EW_DISTRICTO

XXX

 

Remember, if records for a certain sample set have never been uploaded records, the downloaded file will contain only the eight “skeleton” field values. If records for a certain sample set were previously uploaded, the downloaded file will contain skeleton field data and data from the uploaded file. That way, counties can track which records in the file are complete, and which are not.

 

Update Records – To update records, upload new data to replace the old data.

 

Note: Avoid using MS Excel to save the .csv file, as this will cause the file to become corrupt. Excel corrupts the file by applying  “General” formatting to fields. Leading zeros are removed from data values such as CASE_NUM, and when saving files discards columns of “empty” cells at the end of rows.

 

Download Example Sample File

 

 

DATA DICTIONARY

 

The Data Dictionary can be found here in PDF format:

 

            Download Data Dictionary FFY 2007

            Download Data Dictionary FFY 2008

            Download Data Dictionary FFY 2009

            Download Data Dictionary FFY 2010

            Download Data Dictionary FFY 2011

            Download Data Dictionary FFY 2012

            Download Data Dictionary FFY 2013

 

 

DATA VALIDATION

 

Only data relevant to a particular record will be evaluated. The skip rules document flowchart displays the process for determining which data elements are required for a particular record. Data elements which are not required for a record will be ignored by the E2LA uploader, and replaced with zero length strings when stored in the database.

 

Download Skip Rules FFY 2007

Download Skip Rules FFY 2008

Download Skip Rules FFY 2009

Download Skip Rules FFY 2010

Download Skip Rules FFY 2011

Download Skip Rules FFY 2012

Download Skip Rules FFY 2013

 

Required fields must be of the proper format for the field, as must be the appropriate data range and type. The uploaded file contains a mix of the Numeric, Date, Boolean and Text values. These values can optionally be quoted (ASCII 34). The following edits are applied to the different data types:

 

Data Type

Acceptable Values

Numeric

 

Must be a positive integer consisting only of the digits 0 through 9. Any value containing a non-digit character will be rejected. For example, -12 will be rejected (the negative sign “-” is not a digit), as will 12.34 (the decimal mark “.” is not a digit).

 

Numeric data is also validated against the range appropriate for the field. Refer to the Data Dictionary for information on each particular field.

Date

Must be in the format MM/DD/YYYY. For example, 01/12/2007. Months and days with only single digit must have a leading zero (i.e. 08 instead of 8).

 

Dates must also be valid dates (i.e. 09/31/2007 would be rejected).

 

The date may have additional constraints based on the value of the SAMPLE_YEAR and SAMPLE_MONTH fields. Refer to the Data Dictionary for information on each particular field.

 

The exception to this rule is the AIDED_CHILD_DOB, which allows a value of 99/99/9999 to indicate an unborn child.

Boolean

May be 1/0, TRUE/FALSE or YES/NO. The value is case-insensitive, so True, TRUE and true are all treated the same.

Text

Text fields can contain a variety of values, including commas (ASCII 44), (ASCII 34) and linefeed characters (ASCII 10, ASCII 13). To prevent E2L from misinterpreting the data, all text data should be delimited with quotes (ASCII 34). For example, because the following text contains commas:

 

            This, that, and the other

 

it will be interpreted as three separate data values. To prevent this from occurring, the field should start and end with a quote character (ASCII 34):

 

           "This, that, and the other"

 

This will cause the text field to be read as a single field. However, an additional step needs to be taken if the field contains a quote, such as:

 

            I said, "Snap!"

 

To prevent the quotes from being treated as delimiters, each quote character (") must be replaced by two quote ("") character. For example, the above example would be:

 

           "I said, ""Snap!"""

 

The following example is color coded to help clarify the above example – the double quotes in the text are first replaced (in green), and then the entire string is placed in quotes (in red):

 

           "I said, ""Snap! """

 

Empty strings can optionally be output as:

 

           ""

 

Other than checking that required text fields are not blank, no other edit is applied to them.