EWR Spreadsheet Requirements

Document Purpose

This document provides requirements for data format and entries to ensure that they are properly processed at submittal to NHTSA.

EWR Template Standards

Each manufacturer has the option of submitting numeric data for the reporting period as a single workbook file created with Microsoft Excel 2000 or later. The file is composed of multiple worksheets (Tabs) each of which contains some or all of the respective major reporting areas: Production, Deaths & Injuries, Property Damage, Consumer Complaints, Warranty Claims and Field Reports (numeric only). The Excel templates provided shall be populated by the manufacturer with the appropriate data. No modifications in any form shall be made to the Worksheet names, Column headings, Report Labels or their ordering. The electronic template files provided, must be populated with the appropriate data by the manufacturer when submitting EWR data to NHTSA. These files contain custom programs for extracting and validating the data at NHTSA.

File names

File names for the workbooks shall be constructed by concatenating the Manufacturer EWR ID, Report Type, Report Year, Report Quarter and Sequence Number. This will enable NHTSA to identify the manufacturer, report type and reporting period, even if the file is corrupted during transmission and cannot be processed. The Manufacturer EWR ID will be a four-digit number supplied by NHTSA. This ID will allow support for up to 9,998 EWR reporting manufacturers (0000 and 9999 are reserved).

If the volume of data should exceed that supported in a single Excel worksheet (currently approximately 65,000 rows), multiple workbook files can be submitted and the sequence number used to identify multiple files for the same manufacturer and reporting period.
Valid filename values are demonstrated below:

Table 1

Field	Example	Valid Values
Manufacturer ID	6543	0001-9998
Report Type	L	C – Child Restraints H – Medium to Heavy Vehicle and Buses L – Light Weight Vehicle T – Trailers M – Motorcycles R – Tire G – Tire Common Green
Report Year	03	YY format
Report Quarter	1	1-4 (Calendar Quarter)
Sequence Number	01	01-99 (01 shall be the default where only a single workbook file is sufficient for the reporting period)

Example Filename: 6543L03101.xls

Introduction/Overview Worksheet

Each workbook shall contain details about the reports included in the workbook. This information is contained in a worksheet named “ReportInfo” and is the first worksheet in the workbook. Information is provided in two columns in this worksheet. The first column, named “InfoName”, is used for the name of the data field and shall not be modified by the manufacturer. The corresponding second column named “InfoValue” will be used to store the associated Report Information details.

Data Specification

In addition to the data definitions and specifications contained within the final rule, the following criteria shall be applied for specifying the data within each worksheet:

• A four digit year format shall be used for the Model or Product year (Example: 1998)
• Date format for all dates shall be in the MM/DD/YYYY format (Example: 05/17/2002)
• Alphabetic characters must be in all caps
• Numeric data cells shall not contain any non-numeric data including commas
• Undefined or unknown numeric or character data shall be represented by the string “UNK”
• Data cells shall not be left blank

Data Updates

Updates to data previously submitted will be accomplished by re-submitting the entire updated workbook file. The appropriate ReportInfo details shall be updated accordingly.