Preparing and Submitted Files October 2005

1
Preparing and Submitted FilesOctober 2005
2

• Agenda
• Order of File Submissions
• File Format Options
• Developing the Files
• Missing, Not Applicable, Not Collected, and Zero
  Counts
• Common Errors
• QA

3

• File Format Options

4

• File Formats
• General Rules for all files
• Fixed Format
• Comma-delimited Format
• Tab-delimited Format
• XML Format

5

• File Formats
• Four file formats are supported
• Fixed Format (.txt)
• Comma-delimited (.csv)
• Tab-delimited (.tab)
• XML Format (.xml)

6

• File Formats
• A zipped file with a .ZIP extension is allowed
• When submitting a .ZIP file, it must unzip into
  one of the extensions as described in the
  previous slide in the Header Record file name
• The file name in the Header Record should not
  have a .ZIP extension

7

• File Formats
• General Rules for all files
• Numeric fields must not contain commas or
  alphabetic characters
• Numeric fields must not be completely blank
• Decimal fields must contain a decimal point

8

• File Formats
• General Rules for all files
• Numeric fields do not need preceding zeros
• In a numeric field, the software will
  automatically fill with leading zeros, thus a six
  position numeric field containing 123 will be
  converted to 000123

9

• File Formats
• General Rules for all files
• Numeric data entered in fields defined as string
  must include leading zeroes
• FIPS State Codes 01 through 09
• Identifiers
• The file extension and file format structure must
  agree, (i.e., if the file is a fixed format file,
  the file extension cannot be .tab, .csv, or .xml)
  

10

• File Formats
• General Rules for all files
• A Carriage Return/Line Feed (CRLF) must
  immediately follow the last field in every record
  for non-xml files

11

• Fixed Format
• Description
• Each record has a pre-defined length
• Each field in a record is assigned a beginning
  position and has a pre-defined length
• All fields must be included in a record, whether
  used or not

12

• Fixed Format
• Rules
• For a fixed format file, the external file name
  in the header record must have a file extension
  of .txt
• Filler fields and optional fields for which no
  value is reported should be blank in fixed
  position records
• Unused positions of a field should be blank

13

• Fixed Format
• Rules
• Numeric fields must not contain commas
• Data in decimal fields must contain the decimal
  point (e.g., 1.123)
• Data in numeric fields should be left-justified
  with any unused portion followed by spaces
• Use as many blanks as necessary to fill
  non-numeric fields
• All fields, including filler fields, must be
  accounted for (i.e., if no data value is
  specified, the field must be all spaces)

14

• Fixed Format Example

15

• Comma-delimited Format
• Description
• Similar to the fixed format for order and format
  of each field in a record
• Each field is separated by a comma instead of
  beginning and ending in a specified position
• All fields, including filler fields must be
  accounted for
• Unvalued fields and fillers need only be
  designated with a comma

16

• Comma-delimited Format
• Rules
• Except for the last field in a record, each field
  whether valued or not, must be designated by a
  comma, including filler fields
• Example ( indicates CRLF)
• First Field Three Bears School
• Second Field Filler
• Third Field 123 Main Street
• Last Field Filler
• THREE BEARS SCHOOL,,123 MAIN STREET,

17

• Comma-delimited Format
• Rules
• When the last field in a record is a filler, the
  record will appear to end with a comma, but it
  actually ends with a blank filler field
• For valued fields, the comma should immediately
  follow the data value, except for the last field
  in the record

18

• Comma-delimited Format
• Rules
• Double quotes within a field will cause a format
  error to be issued
• In the example shown, () indicates CRLF
• First Field Three Bears School
• Second Field Filler
• Third Field 123 Main Street
• Last Field Filler
• THREE BEARS SCHOOL,,123 MAIN STREET,

19

• Comma-delimited Format
• Rules
• For a comma-delimited file, the external file
  name in the header record must have a file
  extension of .csv

20

• Comma-delimited Format Examples
• The first example gives the field descriptions
  and values in a Header Record for the LEA
  Directory file and shows the record in a
  comma-delimited format
• Note the Carriage Return/Line Feed (CRLF) is
  represented by an
• The second example shows a partial Grades Offered
  file

21

• Comma-delimited Format Examples
• Header Record Field Content
• Field Name Format Data Value
• File Type STRING LEA DIRECTORY INFO
• Total Records in File NUMBER 123
• Filename STRING EULEADIRECTORYVer0001.csv
• File Identifier STRING LEA Directory Example
• File Reporting Period STRING 2003-2004
• LEA DIRECTORY INFO,123,EULEADIRECTORYVer0001.csv,L
  EA Directory Example,
• 2003-2004

22

• Comma-delimited Format Examples

23

• Tab-delimited Format
• Description
• Almost identical to comma-delimited format except
  for the following differences
• Each field is separated by a tab character
  instead of a comma
• Blank fields and fillers need only be designated
  with a tab character
• String fields containing a comma SHOULD NOT be
  enclosed in double quotes

24

• Tab-delimited Format
• Rules
• For a tab-delimited file, the external file name
  in the header record must have a file extension
  of .tab
• When the last field in a record is a filler, the
  record will appear to end with a tab character,
  but it actually ends with a blank filler field
• For valued fields, the tab character should
  immediately follow the data value

25

• Tab-delimited Format
• Rules
• Except for the last field in a record, each field
  whether valued or not, must be designated by a
  tab character, including filler fields
• Example (gt indicates a tab character -
  indicates CRLF)
• First Field Three Bears School
• Second Field Filler
• Third Field 123 Main Street
• Last Field Filler
• THREE BEARS SCHOOLgtgt123 MAIN STREETgt

26

• Tab-delimited Format Examples
• The first example gives the field descriptions
  and values in a Header Record for the LEA
  Directory file and shows the record in a
  tab-delimited format
• Note the tab character is represented by a
  greater than sign (gt) and the Carriage
  Return/Line Feed (CRLF) is represented by ( ).
• The second example shows a partial Grades Offered
  file

27

• Tab-delimited Format Examples
• Header Record Field Content
• Field Name Format Data Value
• File Type STRING LEA DIRECTORY INFO
• Total Records in File NUMBER 123
• Filename STRING EULEADIRECTORYVer0001.tab
• File Identifier STRING LEA Directory Example
• File Reporting Period STRING 2003-2004
• LEA DIRECTORY INFO,123,EULEADIRECTORYVer0001.tab,L
  EA Directory Example,
• 2003-2004

28

• Tab-delimited Format Examples

29

• Extensible Markup Language - XML
• XML is a standard for creating documents that
  describe and apply a structure to data
• XML document are self-describing making them
  readable by both humans and computers
• Extensible means there not a fixed set of
  elements like HTML. Authors can define their own
  tags.
• A common format for exchanging data over the Web.

30

• Example of XML
• ltFILETRANSMISSION FILELAYOUTTYPE"SEA MEMBERSHIP
  TABLE"
• FILEID"SEA MEMBERSHIP 15 OCT 2005"
• SCHOOLYEAR"2005-2006"gt
• ltAGENCY FIPSSTATECODE"99" STATEAGENCYIDNUMBER
  "01"
• STATELEAIDNUMBER"518"gt
• ltTABLETYPE TYPEABBRV"MEMBER"
  TOTALINDICATOR"N"gt
• ltCATEGORY TYPE"GRADELVMEM"
  VALUE"03"/gt
• ltCATEGORY TYPE"RACEETHNIC"
  VALUE"AS"/gt
• ltCATEGORY TYPE"GENDER" VALUE"F"/gt
• ltAMOUNTgt456lt/AMOUNTgt
• ltEXPLANATION/gt
• lt/TABLETYPEgt
• lt/AGENCYgt
• lt/FILETRANSMISSIONgt