CDSS – CDMIS User Manual; Appendix C: Creating Electronic Files

Background

Agencies maintaining a child care and development contract may utilize the CDD-801A Electronic File Transfer function of the CDSS - CDMIS to fulfill the CDD-801A Monthly Child Care Population Report (CDD-801A) requirement. The uploaded file contains all family, child, and provider information for a given report month and year (report period).

Submitting an Electronic File

Step-by-step instructions for submitting the CDD-801A via the Electronic File Transfer method is available in Chapter 3 of this manual.

Sub-Agencies

Agencies can submit the CDD-801A 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 CDD-801A reporting purposes. Once sub-agencies are created, Agency Super Users can designate for each sub-agency Agency Users who will submit the CDD-801A.

Agencies are responsible for ensuring that all associated sub-agencies submit complete, correct, and timely reports.

This section provides technical descriptions of the various components of a CDD-801A electronic file (file).

File Name

Agency-Assigned: Agencies are permitted to name files uploaded to the CDSS - CDMIS to fulfill the CDD-801A reporting requirements, at their own discretion; however, for convenience and record keeping purposes, it is recommended that file names include the report period and the agency’s vendor number/submission code

CDSS-Assigned: Once uploaded to the CDSS - CDMIS, files are assigned a name by the CDSS. This name includes the report; report period; agency vendor number/submission code; and date and time of file upload. The CDSS-assigned file name is indicated on the CDD-801A Electronic File Transfer Status Reports screen and is accessible via the CDD-801A Management Reports function.

File Format

All files uploaded to the CDSS - CDMIS must be specifically formatted to meet system standards. Specifically, files must maintain the following characteristics:

• Files must be a tab-delimited ASCII text file
• Files must not be compressed (i.e., zipped)

Incorrectly formatted files will not be processed by the CDSS - CDMIS.

Records (Rows)

The term “records” is used to refer to the rows of a file:

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

Fields (Columns)

The term “fields” is used to refer to the columns of a file:

• All 43 fields indicated in Section B: File Format Specifications must appear in files, 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

Within each field, data elements must be specifically formatted. Each field contains different data format specifications.

General descriptions of the format of the different data elements are presented below:

• 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.

Detailed descriptions of the format of the different data elements of a file are presented in the following section, Section B: File Format Specifications.

This section provides detailed information of format specifications for each data field within a CDD-801A electronic file.

Within an electronic file, all data elements must be specifically formatted and meet specific criteria. The linked table outlines each data element of a file. Additionally, the table provides descriptions and comments of each data element, including the data type, size, and entry requirement.

Electronic File Format Specifications Table

This section provides detailed instructions for structuring files.

One-Child Family

Family Data

Below is fictional information about a family with only one child receiving subsidized child care services through an agency’s contract with the CCDD.

In July 2024, Agency X (vendor number/submission code “Z987000") provided subsidized childcare services through a contract to the Alice A. Adams family. Alice is the parent/Head of Household (HoH).

Agency X assigned the Family Identification/Case Number (FICN) "A9910" to Alice.

Alice’s home zip code is 95814-1292; she lives in Sacramento County (Federal Information Processing Standards [FIPS] code "06067").

Alice did not receive Temporary Assistance for Needy Families (TANF) (No = "N"), and her income was not greater than 85 percent of the State Median Income (SMI) level (No = "N").

Her reason for receiving childcare was "Employment" ("D")

Alice has one child, Ann B., who was born 04/06/2020.

There is no active Individualized Education Program (IEP) in the family file for Ann.

Ann's primary language is English.

Ann is not an English Learner.

Ann began receiving services on 07/14/2023.

Ann is not of Hispanic or Latino Origin and Alice has indicated that Ann’s Race is American Indian or Alaskan Native.

Ann Receives Direct Services Part-Day through Agency X.

Ann is certified for one type of childcare: "License center-based care" (type of childcare code "04").

Ann's care was provided by Kiddie Care Center in Sacramento County.

The zip code of the licensed center at which her care was provided through the agency’s contract with the CDSS is 95814-1234.

Kiddie Care Center participates in the Quality Rating and Improvement System (QRIS) program and tier rank is 4.

Kiddie Care Center is accredited through a state operated accreditation program.

Kiddie Care Center's Federal Employer Identification Number (FEIN) is 23-4567890.

Ann's care was funded exclusively by the CSPP; even though there is only one program code (i.e., CSPP), the two remaining Program Code fields must be included in the row.

Ann attended all enrolled days with zero absences; (Attendance Status 1 “03”); even though there is only one program code, the two remaining Attendance Status fields must be included in the row.

Family Record

The sample record below displays how the family, child, and provider information detailed above, must be structured.

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 record

The example record below is broken into four distinct rows, which represent the following: 1) agency and report period; 2) family; 3) child; and 4) provider.

Note: An actual file to be uploaded to the CDSS – CDMIS would have all four sections together on the same row

07/2024>Z987000>
A9910>Adams>Alice>A>958141292>N>N>3>1500>D>06067>07/14/2023>
Adams>Ann>B>N>Y>N>N>N>N>F>04/06/2020>00>N>07/14/2023>Y>
234567890>06067>958141234>4>2>04>CCTR>>>>>03>>>>>07/14/2023@

Multi-Child Family

Family Data

Below is fictional information about a family with three children receiving subsidized child care services through an agency’s contracts with the CCDD.

Suzie Smith has three children: Milly, Barry, and Donny. Agency Z987 began claiming the days of enrollment of Susie’s children for fiscal reimbursement purposes under their contract on July 14, 2024.

The current report month is July 2024.

All three children receive subsidized childcare services through the agency’s contract from the same provider.

The provider participates in the local QRIS and tier rank is 4 and received accreditation from the National Association for the Education of Young Children.

Barry and Donny are twins and started receiving subsidized childcare services on July 14, 2024, in CCTR.

Milly began receiving services on July 15, 2024, when she was one day old.

All Children received Direct Service Full-Day care in July 2024 and Milly has an active IEP in the family file.

All three children speak English as their primary language and are not English Learners.

Family Record

The sample records below display how the family, children, and provider information detailed above, must be structured.

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 record

The example records below are broken into four distinct rows, which represent the following: 1) agency and report period; 2) family; 3) child; and 4) provider. Three records are included to represent the services provided to each child within the family. Because all children are from the same family, the family’s information (fields 1–15) are exactly the same in all three rows.

Note: An actual file to be uploaded to the CDSS - CDMIS would have all four sections together on the same row.

07/2024>Z987000>
SS1832>Smith>Suzie>>958141234>Y>N>4>3000>D>06067>07/14/2024>
Smith>Milly>>N>Y>N>Y>N>N>F>07/14/2024>Y>00>N>07/15/2024>N>
987987987>06067>958284321>4>1>04> CCTR>>>>>03>>>>>07/15/2024@

07/2024>Z987000>
SS1832>Smith>Suzie>>958141234>Y>N>4>3000>D>06067>07/14/2024>
Smith >Barry>>N>Y>N>Y>N>N>M>07/14/2020>N>00>N>07/14/2024>Y>
987987987>06067>958284321>4>1>04> CCTR>>>>>03>>>>>07/14/2024@

07/2024>Z987000>
SS1832>Smith>Suzie>>958141234>Y>N>4>3000>D>06067>07/14/2024>
Smith>Donny>>N>Y>N>Y>N>N>M>07/14/2020>N>00>N>07/14/2024>Y>987987987>
06067>958284321>4>1>04> CCTR>>>>>03>>>>>07/14/2024@

This section provides detailed instructions for reading files.

Each row in a file contains three levels of information:

• Family information
• Child information
• Provider information

Rows must be grouped in a very specific way:

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

Multiple rows must be submitted for a family when one or more of the following occur:

• A family has more than one child receiving subsidized child care services through an agency’s contract
• A child is certified to receive subsidized child care services through an agency’s contract from more than one provider
• A child is certified to receive subsidized child care services through an agency’s contract in more than one settin

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

If there are multiple rows for one child (i.e., a child that had more than one provider during the report period), all 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 37; fields 38 and 39 must be included, even if they are null or blank.

Examples

The below table details the number of rows a file should maintain in different hypothetical family situations. Explanations are provided for each situation.

If a family has the following:	Then the file contains the following:	Explanation
1 child who received 1 type of child care	1 row	This is the basic format for reporting a family with one child and one provider.
3 children who each received child care from the same provider	3 rows (one for each child)	All children are from the same family, so the family information is exactly the same in all three rows. All children received subsidized child care services from the same provider, so the provider information is exactly the same in all three rows.
1 child who received 2 types of child care from different providers	2 rows (one for each provider)	Only one child received subsidized child care services, so the family and child information are exactly the same in both rows.
1 child who received child care from 2 providers and 1 child who received child care from 1 provider	3 rows (two rows for the first child, 1 row for the second child)	For the first child, the family and child information is exactly the same in both rows.
1 child who received 2 types of child care from 1 provider (for example, "License-exempt in child's home by a relative" and "License-exempt outside child's home by a relative")	2 rows (one row for each type of child care)	Only one child received subsidized child care services, so the family and child information is exactly the same in both rows. The provider FEIN/SSN is the same in both rows. Even though the child received subsidized child care services from the same provider, the type of child care (setting) is different. An example is when the child's grandmother provides the child care in both the child's home on some days and in her own home on some days.
1 child who received child care from the same provider, in the same type of child care, but under 2 contract codes	1 row	The row has three fields for contract codes. In this example, the first two fields would contain the contract codes and the third field would be blank.

The flexibility of agencies’ existing computerized systems determines what files agencies can transfer to the CDSS - CDMIS. PC-based databases, such as 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.

E.1 Using an Existing Computer System

The flexibility of agencies’ existing computerized systems determines what files agencies can transfer to the CDSS - CDMIS. PC-based databases, such as 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 primary limitation to creating files is reconciling the files and fields between agencies’ existing systems and the CDSS - CDMIS. The electronic file structure for the CDSS - CDMIS is fixed; existing data must conform to the fields required by the CDSS - CDMIS.

Reconciling two systems' file formats and fields is the most difficult part of the process. Examples of fields that may require reconciliation are as follows:

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

E.2 Using a Spreadsheet Application

Spreadsheet applications, such as Microsoft Excel (Excel), allow agencies to apply special formatting to each column to meet the formatting requirements, and save data as a tab-delimited text file.

Spreadsheet Templates

Agencies can create spreadsheet templates to input or format data and create tab-delimited files that can be transferred to the CDSS - CDMIS.

Spreadsheets must be built to contain all the fields described in Section B: Electronic File Format Specifications. Files MUST be constructed with the EXACT format shown (i.e., all fields must be in the proper order, even if they are not populated with data). To export data, saving a file as a "tab-delimited text file" is appropriate in most cases.

When using spreadsheets, remain mindful of the following best practices when working with files:

DO

• Have a working knowledge of the spreadsheet application
• Utilize custom formatting for each column of data
• Exercise caution when altering file contents
• Save as a tab-delimited text file

DO NOT

• Allow the spreadsheet application to auto-format file contents (i.e. dates, numbers)
• Add headers or punctuation (i.e. commas, quotation marks, periods, parentheses) to or between data fields
• Delete spaces between data fields
• Save as an XLS or XLSX file type, if using Excel

E.3 Using a Database Application

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

The same recommendations made for a spreadsheet apply when creating a file from a database: agencies must base table designs on the file format specifications and respect the order of the fields upon export

E.4 Using a Software Vendor

For agencies’ convenience, the CDSS provides information of software vendors (vendors) who have expressed interest in working with agencies on the electronic reporting of the CDD-801A and the Subsidized Provider Report (SPR). Vendor contact information is available at Vendor Information.

The CDSS provides this list of vendors for agencies’ convenience. Agencies should thoroughly review vendors' products and references before purchase.

The CDSS does not endorse any vendors or their products.