CDD-801A File Format Instructions

Purpose

This document provides instructions for technical staff, working in cooperation with program staff, to prepare the electronic files used by their agency (or sub-agency) to submit monthly CDD-801A data to the Child Development Management Information System (CDMIS) secure Web site via electronic file transfer. Agencies must submit their monthly reports electronically by using the CDMIS secure Web site.

Agencies can submit their reports at the CDMIS secure Web site in two ways:

• Using the data entry screens at the CDMIS secure Web site.
• Transferring electronic files.

This document describes the process for transferring electronic files.

Instructions for creating files

Electronic File Format Specification
How to read the samples and examples
Technical description of an electronic file
Structuring the contents of the file
Structuring the rows of the file

Overview: Creating an electronic file from an existing computerized system, spreadsheet, or database application
Frequently Asked Questions

Sub-agencies

Agencies can submit their reports in smaller, more manageable groups called sub-agencies. Large agencies or agencies with many sites, programs, or contract types may find it convenient to create sub-agencies for reporting purposes, and then to designate users at each sub-agency who will submit monthly CDD-801A data.

An agency is responsible for ensuring that all its designated sub-agencies submit complete, correct, and timely reports.

All of the information in this document is applicable to agencies and sub-agencies.

Electronic file content requirements

• Report month and year.
• Vendor number/submission code (of the agency or sub-agency)
• Name of the Head-of-Household who received Early Learning and Care Division (ELCD) subsidized services
• FICN (Family Identification/Case Number) of the Head-of-Household
• Zip code of the Head-of-Household
• FIPS (Federal Information Processing Standards) code of the Head-of-Household
• If the Head-of-Household receives TANF/CalWORKs
• If the Family income is greater than 70 percent of the state median income
• Reason for receiving services
• Date services to family began
• Family Size
• Monthly Family Income
• Name of child who received ELCD-subsidized services
• Child's date of birth
• Child's Ethnicity (Hispanic or Latino origin)
• Child's Race
• Child's Gender
• Child's Care Time Base (child receives less than 4 hours of care each day for more than half of the month)
• If the child has an IEP in the family file
• Child's Primary Language
• If child is an English Learner
• Date subsidized services began for the child
• Federal Employer Tax Identification Numbers (FEIN) or Social Security Number of the provider of the care
• Provider FIPS Code
• Provider Zip Code
• If the Provider participates in the Quality Rating and Improvement System (QRIS)
• If the Provider is accredited through a state, national or other child care
• Types of ELCD-subsidized services provided
• Program Code information for each physical setting (not for each family)
• Date child started receiving services with provider or date services with provider change settings or programs funding services

Warning: Transferred files overwrite existing information

CDMIS allows multiple electronic file transfers for the same report month; however, every successful electronic file transfer completely overwrites all existing family, child and type of child care information for the specified agency (or sub-agency) for that report month.

Reminder: Check the status report

CDMIS processes electronic files each day at 2:45 A.M. After that time, status reports are available for files submitted the previous day.

The status report:

• Describes whether the transfer file was rejected (fail) or successfully loaded (pass).
• Lists any error messages or warnings that caused the file to fail.

A status report is created every time a user attempts to transfer a file to CDMIS. All status reports are saved in the CDMIS database.

CDD-801A Electronic File Specifications

1 C = character (upper- and lower- case letters, numbers, dashes, etc.); N = numbers only.
2   The default submission code for agencies that do not report by sub-agency is "000". If an agency has created sub-agencies for reporting purposes, then each sub-agency has its own submission code. The list of submission codes for each agency is available within the CDMIS Web site on the Sub-agency/No Services page.

How to read the sample and examples

This section includes examples on how the data in the file must be organized.

Sample:

The Sample Row below displays the information about a family who will be included in the August 2013 CDD-801A report:

• In August 2013, Agency X (vendor number/submission code Z987000" provided ELCD-subsidized services to the Alice A. Adams family.
• Agency X assigned the FICN "A9910" to Alice.
• Alice's Social Security Number is 123-45-6789.
• Her home zip code is 95814-1292, and she lives in Sacramento County (FIPS code "06067").
• Alice did not receive TANF (No = "N"), and her income was not greater than 75% of the State Median income (No = "N").
• Her reason for receiving child care was "Employment" ("D").
• Alice has one child (Ann B.) who was born 04/06/2010.
• There is no active IEP (Individualized Education Program) in the family file for Ann.
• Ann's primary language is English
• Ann is not an English Learner
• Ann began receiving services on August 14, 2013.
• Ann is not of Hispanic or Latino Origin and the Alice (the parent) has indicated that Ann’s Race is American Indian or Alaskan Native.
• Ann receives 3.5 hours of care, three days a week. Because her care is less than four hours during the days she receives care, she is reported as receiving part-time care.
• Ann received one type of child care: "License center-based care" (type of care code "04").
• Ann's care was provided by Kiddie Care Center in Sacramento County. The zip code where the licensed center is located is 95814-1234.
• Kiddie Care Center participates in the Quality Improvement and Rating Scale program.
• Kiddie Care Center is accredited through a state operated accreditation program.
• Kiddie Care Center's Federal Employer Identification Number (FEIN) 23-4567890.
• Ann's care was funded by the California State Preschool Program ("CSPP").
  
  Note: Even though there is only one program code ("CSPP"), the two remaining empty fields must be included in the rows.

Two symbols are used in the examples below to represent required formatting:
> is the symbol used for the tab separation that must appear between each field
@ is the symbol for CR/LF which indicates the end of each row

Sample Row:

08/2013>Z987000>
A9910>123456789>Adams>Alice>A>958141292>N>N>3>1500>D>06067>08/14/2013>
Adams>Ann>B>N>Y>N>N>N>N>F>04/06/2010>00>N>08/14/2013>Y>
234567890>06067>958141234>1>2>04>CSPP>>>08/14/2013@

Note: all data format examples below have each row broken into sections representing the agency and report period, family, child, and provider. The actual file would have all four sections together on the same row.

Example

Suzie Smith has three children (Milly, Barry, and Donny). She began receiving services for all three of her children from agency Z987 on September 14, 2012. The CD-9600A (Child Care Data Collection Privacy Notice and Consent Form) signed by Betty indicates she does not want her Social Security Number submitted for data collection purposes, so that field must be blank. All three children receive services from the same provider. The provider participates in the local Quality Rating and Improvement System and has received accreditation from the National Association for the Education of Young Children (NAEYC). Barry and Donny are twins and started receiving services on September 14, 2012, in the agency's preschool program. Milly began receiving services on July 5, 2013 (when she was about four months old). Milly received full time care in July 2013 and has an active IEP in the family file. The other two children, Barry and Donny, only received 2 to 3 hours of care each day in July. All three children speak English as their primary language and are not English Learners.

Note: Three rows must be included to represent the services provided to each child. All children are from the same family, so the family information (fields 1-15) must exactly the same in all 3 rows.

Data format:

07/2013>Z987000>
SS1832>>Smith>Suzie>>958141234>Y>N>4>3000>D>06067>09/14/2012>
Smith>Milly>>N>Y>N>Y>N>N>F>03/10/2013>Y>00>N>07/05/2013>N>
987987987>06067>958284321>1>1>04>CCTR>>>07/05/2013@

07/2013>Z987000>
SS1832>>Smith>Suzie>>958141234>Y>N>4>3000>D>06067>09/14/2012>
Smith >Barry>>N>Y>N>Y>N>N>M>06/14/2009>N>00>N>09/14/2012>Y>
987987987>06067>958284321>1>1>04>CSPP>>>09/14/2012@

07/2013>Z987000>
SS1832>>Smith>Suzie>>958141234>Y>N>4>3000>D>06067>09/14/2012>
Smith >Donny>>N>Y>N>Y>N>N>M>06/14/2009>N>00>N>09/14/2012>Y>
987987987>06067>958284321>1>1>04>CSPP>>>09/14/2012@

Top of page

Technical description of an electronic file

File name:

• The user may give any name to the file. (For your convenience, CDE recommends that the file name include the report month and year as well as your vendor number/submission code.)

File format:

• The file must be a tab-delimited ASCII text file.
• A file must not be compressed ("zipped").

Row (record) format:

• Each row (record) in the file must end with the standard PC end-of-line characters CR/LF (Carriage Return/Line Feed).
• Blank rows are not allowed in the file.
• A file must contain at least one row and no more than 100,000 rows.

Field format:

• All 41 fields that appear in the file format specification must appear in the file, even fields with null or blank values.
• All fields are delimited (separated) by tabs, except the last one, which is followed by a CR/LF.
• The use of two tabs next to each other indicates a blank field.
• Fields must not be padded with spaces.
• Fields must not contain control characters or format symbols.
• If a field is marked as required, null values (no data) are not allowed.
• Field lengths may be less than the specified length if the Data size column includes "Max and Min sizes", but can never exceed the maximum. If the data size column includes a specific field size, the field must be exactly that length.

Data formats:

• Character fields can contain both upper- and lower-case letters, numbers and dashes, unless otherwise noted.
• Number fields must contain only numbers.
• Date fields must contain dates in the format mm/dd/yyyy.

Structuring the contents of a file

The format for an electronic file consists of rows of information about all of the ELCD subsidized services provided by one agency (or sub-agency) for one month.

Multiple rows must be submitted for that family when:

• A family has more than one child receiving ELCD subsidized services.
• A child receives services from more than one provider.
• A child receives services in more than one setting.

The rows in a file must be grouped in a very specific way:

• All the children in one family must be grouped together.
• All the providers/types of services for one child must be grouped together.

If there are multiple rows for one family, then all the information for the family (fields 1-15) must be exactly the same in all those rows.

If there are multiple rows for one child (for a child that had more than one provider during the month), then all the information for the child (fields 16-31) must be exactly the same in all those rows.

If there is only one program code in the row, it must appear in field 38; fields 39 and 40 must be included, even if they are null or blank.

Structuring the rows of a file

Each row in an electronic file contains information about a family, a child in that family, and a type of subsidized services received by that child.

Overview: Creating an electronic file from an existing computerized system

If you can format your existing data as tab-delimited text files, then the files can be transferred to CDMIS.

The flexibility of your existing system determines what you can transfer into CDMIS. PC-based databases like Microsoft Access, FileMaker, and FoxPro can easily create tab-delimited text files. Mini-computer systems and mainframes may need special programming to create PC-readable electronic files.

The only real limitation to creating electronic files is in reconciling the files and fields between your existing system and CDMIS. The electronic file structure for CDMIS is fixed. This means your existing data must conform to the fields required by the CDMIS database.

Reconciling the two systems' file formats and fields is the most difficult part of the process.

Examples:

• If the child's date of birth is stored in your existing system in the format m/d/yy (for example, 8/4/05), then that field must be re-formatted as mm/dd/yyyy (08/04/2005) in the electronic file for CDMIS.
• If the child's last name in your existing system is 75 characters long, that name won't fit into CDMIS' 50-character child last name field.
• If your existing system does not store all of the required information, then you may be unable to create an electronic file.

Using a spreadsheet application to create an electronic file

Spreadsheet applications, such as Microsoft Excel, allow the person working with the file to apply special formatting to each column to meet the formatting requirements and then be saved as a tab-delimited text file.

You can create your own spreadsheet template to input (or format) your data and create tab-delimited files that you can transfer into the CDMIS database.

Your spreadsheet must be built to contain all the fields (columns) described in the file format specifications. The electronic file MUST be constructed with the EXACT format shown, all columns in the proper order, even if they are not populated with data. To export your data, saving your file as a "tab-delimited text file" should, in most cases, be appropriate.

Important:

• Do not include a header record in the file.
• Do not include extra delimiters in the file (for example, commas or quotation marks).

Using a database application to create an electronic file

Database applications are slightly less widespread than spreadsheet applications because of their relatively higher level of complexity and (sometimes) their higher prices. It is hard to provide general instructions for exporting from one system to another.

Generally speaking, the same recommendations apply for creating an electronic file from a database as for a spreadsheet: you must base your table design on our file format specifications and respect the order of the fields when exporting.

Top of page

Frequently Asked Questions

Can I e-mail my agency's file to CDE?

No. CDE does not accept files via e-mail. E-mail is not secure. NEVER use e-mail to send files that contain confidential information (such as Social Security Numbers). Your agency must transfer the file by using the CDMIS secure Web site.

Can I send a diskette or CD with my agency's file to CDE?

No. CDE does not accept diskettes or CDs. Your agency must transfer the file by using the CDMIS secure Web site.

Can I download a file from CDMIS?

Yes. One of the CDD-801A Management Reports within CDMIS allows you to export one month of data into an Excel spreadsheet and save that file onto your computer.

How do I know if someone else in my agency has already transferred a file for a report month?

Each time a user attempts to submit a file using the CDD-801A Electronic File Transfer option, the system checks to see if data already exists in that month. If it does, a page is displayed asking for confirmation to accept the file and overwrite the existing data in the selected month or cancel the transfer. If there is no data in the month, the file is accepted and the user can check the status the next day.

When I format the file, do I put quotation marks around the character fields?

No. Do not use quotation marks before and after character fields. The only allowed delimiters in the file are tabs (to separate fields) and CR/LF (to indicate the end of a row).

If I use the file transfer once, do I then have to use the file transfer every month after that?

No. In subsequent months, you can manually enter (type) your data into the CDMIS data entry screens at the secure Web site. For example, you can do a one-time file transfer to load all of your data into the CDMIS database. For the next report month, you can use the "Copy forward" feature on the data entry screens to copy your data to a subsequent month. After the data is copied to the new report month, you can edit it by using the CDMIS data entry screens.

Can I use the file transfer option whenever I want?

The CDMIS secure Web site is available at all times for you to submit your data. Obviously, you cannot submit data for a report month in the future (reporting rules require that you submit information based on receipt of child care).

Can I transfer a partial file now and transfer the rest later?

No. Every time you (successfully) transfer a file, existing data in CDMIS for the specified report month is erased. The transfer file replaces all your existing data.

The only way for an agency to submit monthly data in multiple files is to establish sub-agencies, but those sub-agencies must contain different groups of families.

How do I change the information I already transferred? I need to add, edit and delete data.

You have two options:

• Use the data entry screens at the CDMIS secure Web site (select the function CDD-801A Web Input/Edit from the Main Menu) to correct the information, (This might be easier if you have just a few changes.) or
• Make corrections to your file and transfer the file again. (For the report month, all of the information in the system will be erased and the new file will be loaded into the CDMIS database.)

Help! I transferred a file with wrong information. How do I delete all of the data in the file?

You have two options:

• Use the data entry screens at the CDMIS secure Web site (select the function CDD-801A Web Input/Edit from the Main Menu) to delete all of the information, or
• Create a new file that contains the correct information and transfer the file. When the file passes, all of the information in the system will be erased and the new information will be loaded into the CDMIS database.)

What happens to the file I transferred?

The file is processed in three steps:

• The file is immediately stored on the CDMIS secure Web site.
• The file process begins at 2:45 am the following morning.
  * If processing is successful, the information in the file is transferred to the CDMIS database.
  * If processing is not successful, none of the information in the file is transferred to the CDMIS database.
• A processing status report is created and stored (permanently) at the CDMIS secure Web site.

How do I know if my file transfer was successful?

Our system processes files submitted the previous day at 2:45 am each morning. A processing status report is available the morning after you transfer the file. Go to the main menu and select the function "CDD-801A Management Reports" and then select "CDD-801A Electronic File Status Report" along with the report month and year you would like to view. All files submitted for a specific report period will be displayed in the order they were received. To view the status of a file, click the link for that file to open the status report. The processing status report describes if a file failed to load or successfully processed. If a file fails, the report displays errors or missing information in the file that must be corrected.

If one sub-agency uses the file transfer function, does the other one have to?

No. Every sub-agency is completely independent of any other sub-agency.

Can I switch from file transfer to Web input (or vice versa) anytime?

Yes.

When I look at my transfer file, the data wraps into lots of rows. Did I do something wrong?

When printing or displaying an import file, long records may wrap around the screen or page for multiple "lines." These are still considered single rows (records) if the end-of-line characters CR/LF appears after the last field.

How can I test my file?

CDMIS has a secure Test Web site for this purpose. It operates just like the main CDMIS Web site but the files submitted do not count as a submission for your agency. Files are processed every half-hour between 7:00 am and 7:00 pm daily. Users can log on to this test Web site to test their files and resolve errors. Once all the errors are resolved, the file can be submitted to the main CDMIS Web site.

Do I have to give my electronic file a special name before I transfer it to CDMIS?

No. The CDMIS secure Web site accepts files with any name. For your own convenience, we recommend that you give your file a name that indicates its report month and year.

My agency uses a software package to create our monthly CDD-801A reports. Can I transfer a file to the CDMIS secure Web site?

Agencies that use software created by outside vendors will be able to submit electronic files if the software has been updated with the file format specifications detailed in this document. For more information, consult your software vendor.

I forgot to report some families when I transferred last month's electronic file. Can I just include those families in my file for next month?

No. A file must contain information for only one month. If you try to transfer a file with information for more than one month, the entire file will be rejected. To add just a few families to a prior month, use the "CDD-801A Web Input/Edit" function.

What is a FIPS code and how do I find it?

FIPS stands for Federal Information Processing Standards. In California, FIPS codes are assigned by county. The FIPS code is the two-digit state code followed by 3 numbers associated with the counties in those states. We have provided a Zip/FIPS Lookup utility that can be accessed from the Main Menu of CDMIS. Just enter the Zip Code and our system will provide the correct FIPS Code to enter in your file.

I used a spreadsheet to create my electronic file. When I try to transfer it, the transfer fails. Help!

There are four common problems when a spreadsheet is used:

• Problem: The user puts column headings at the top of the spreadsheet and then enters data into the rows underneath. The CDMIS system is unable to read the header row, so the file is rejected.
  Solution: Remove the header row from the spreadsheet before you save the spreadsheet as a tab-delimited text file.
• Problem: When the spreadsheet application creates the tab-delimited text file, it adds delimiters to the file. For example, quotation marks may be inserted around text fields.
  Solution: Remove any additional characters from the text file.
• Problem: When the user types a leading "0" (zero) in some entries, the spreadsheet application ignores the 0. For example, the user enters "04" (Licensed center-based care) as the type of child care, but the spreadsheet drops the "0" and displays only the "4".
  Solution: Format the column in the spreadsheet as a "text" column rather than a number column. You can also use custom formatting for that column and enter the format type as 00 so the leading zero is not dropped when a single number is entered in that column.
• Problem: When the user enters a date into the spreadsheet, the spreadsheet changes the format. For example, the user enters "04/06/2012", but the spreadsheet displays "4/6/2012".
  Solutions:
  1. Confirm that the date format of the column in the spreadsheet is mm/dd/yyyy or
  2. Format the column in the spreadsheet as a "text" column.

Top of Page