Guidance for Preparing Spreadsheet or
Comma Delimited Text File Electronic
Reports and Submitting Them via CDX
United States
Environmental Protection
Agency
-------
Guidance for Preparing Spreadsheet or
Comma Delimited Text File Electronic
Reports and Submitting Them via CDX
Compliance and Innovative Strategies Division
Office of Transportation and Air Quality
U.S. Environmental Protection Agency
SER&
United States
Environmental Protection
Agency
EPA420-B-07-016
October 2007
-------
The following guidance applies to select periodic reporting forms and data collections for a
number of fuels-related programs including Reformulated Gasoline (RFG) & Anti-dumping, Tier
2 Gasoline Sulfur, Mobile Source Air Toxics (MSAT), Diesel Sulfur, Renewable Fuel Standard,
and the Fuel and Fuel Additive Registration System (FFARS). Regulations for these programs,
including reporting requirements, are found at 40 CFR Parts 79 and 80. All are implemented by
EPA's Office of Transportation and Air Quality. Useful links related to the fuels regulations,
reporting forms, and contacts are located at the end of this guidance document.
The statutory provisions and EPA regulations described in this document contain legally binding
requirements. This document does not substitute for those provisions or regulations, nor is it a
regulation itself. Thus, it does not impose legally binding requirements on EPA or the regulated
community, and may not apply to a particular situation based upon the circumstances. The
electronic reporting method described in this document is recommended by the Office of
Transportation and Air Quality (OTAQ). We recognize that the procedures outlined in this
document represent one of many possible ways to report electronically. For the reporting
method described in this document to work, the submitter should follow the procedures
presented below. EPA retains the discretion to adopt approaches on a case-by-case basis that
differ from this guidance where appropriate. Any decisions regarding a particular facility will be
made based on the statute and regulations. Therefore, interested parties are free to raise
questions and objections about the substance of this guidance and the appropriateness of the
application of this guidance to a particular situation. EPA will consider whether or not the
recommendations or interpretations in the guidance are appropriate in that situation. This
guidance is a living document and may be revised periodically without public notice. EPA
welcomes public comments on this document at any time and will consider those comments in
any future revision of this guidance document.
This guidance should be used in conjunction with the EPA defined forms and instructions
established for the individual fuel programs. The form instructions provide detailed information
on field order, codes, data type, length, and format. In addition to the instructions, spreadsheet
template files are available. The templates allow the data to be entered directly into a given
report, providing a column header row of all necessary fields. EPA is prepared to accept
reporting information as either a comma delimited text file or a spreadsheet file from any of the
major spreadsheet software applications available. We consider the major spreadsheet software
applications to include Microsoft Excel, Lotus 1-2-3, and Corel Quattro Pro. If you use different
software, please contact us.
Concept -
Whether reporting via comma delimited text file or spreadsheet file, a report consists of one line
for a text file or one row in the case of a spreadsheet. Each report contains the information
necessary to identify the report type, the owner of the data and the data itself. This concept is
known as "flat file" reporting and it is a simple, inexpensive method that does not require special
software.
-------
Some Basics -
These are a few items to keep in mind before getting started:
1. Use upper case when filling text data fields. You may use both upper and lower case in
comment lines since they do not contain report data.
2. Some numeric fields may be positive or negative and are identified by the "±" symbol in
the form instructions. Negative numbers should be identified using the "-" sign. Positive
numbers should be unsigned, i.e. do not use "+" sign.
3. Punctuation should not be used in text data fields. Punctuation can be used in comment
lines since they do not contain report data.
4. Some numeric fields such as Company ID, Facility ID and Batch ID are actually text
fields. Include all preceding zeros when applicable.
5. For those submitting comma delimited files, EPA is able to process text fields with or
without surrounding "quotation marks."
6. Please use the latest version forms. The EPA website contains the latest form
instructions and templates for the reporting program. Prior form versions may be used
for resubmissions; contact EPA for versions that are not posted (contact info). Please
visit frequently to get the latest updates and changes.
7. Please note the difference between Originals and Resubmissions. Once an Original
report has been submitted to EPA, all changes or updates to that report should be
submitted as a Resubmission.
8. Each line/row in a submitted file/spreadsheet should start with either a comment symbol
(##) or a valid Report Form ID - or the line/row should be completely blank.
9. A complete, single line/row, report is the combination of the overhead (identifying
information) and the report detail (the actual data).
10. If you have any questions, comments, or suggestions regarding reporting, please call or
send an e-mail (contact info).
Report Forms -
Each reporting program will have one or more families of forms associated with it. Each
individual form contains a format for providing "overhead" information, including very basic
identifying information, such as the company name, company and/or facility identification
number, the reporting quarter or year of the data, etc. All forms in the same form family contain
the same first half dozen or so fields of overhead.
Each form also has a detail part, which contains the substantive data. The detail part makes up
the majority of fields. For example, the detail part would contain the compositional data for a
company's batches of gasoline or diesel fuel. A complete form consists of the overhead and
detail parts combined together.
The EPA website maintains a listing of current forms, instructions, and spreadsheet templates for
online reference as well as download, (www.epa.gov/otaq/regs/fuels/forms.htm)
-------
Form Examples -
The following two examples are hypothetical Gasoline Sulfur Batch Report (form GSF0400)
submissions. Figures 1.1 and 1.2 represent the same report information in different formats. The
first version is comma delimited and the second is a spreadsheet representation of the same data.
Fig. 1.1 Example of comma delimited Sulfur Batch Report
## Capital City Refinery 1999 Sulfur Batch Reports
GSF0400,O,02/21/2000,1999,,1234,12345,000001,RG,01/02/1999,10000,140
GSF0400,O,02/21/2000,1999,,1234,12345,000002, ...
Fig. 1.2 Example of spreadsheet Sulfur Batch Report
## Capital City Refinery 1999 Sulfur Batch Reports
Q
—
E
o
LJ_
O
Q.
CD
o:
8
GSF0400
GSF0400
CD
Q.
I—
O
Q.
CD
o:
o
0
CD
CO
Q
O
Q.
CD
o:
02/21/2000
02/21/2000
^_
CO
CD
t
O
Q.
CD
o:
1999
1999
sj
0
<
Q_
0
Q
c
CO
E
o
0
1234
1234
Q
CO
LJ_
12345
12345
CD
z
CO
CO
1
2
CD
Q.
H
•s
•a
E
Q_
RG
CD
CO
Q
o
•Q
•a
IX
01/02/1999
CD
^
"5
>
CO
CO
10000
a
w
140
The examples above show that a Sulfur Batch Report is made up of twelve fields. The first
seven fields of this report are the overhead fields (Report Form ID, Report Type, Report Date,
Report Year, GPA Gas, Company ID, and Facility ID). Fields 8 - 12 are the data fields (Batch
Number, Product Type, Production Date, Batch Volume and Sulfur).
The Report Form ID, the first field in the sample reports shown above, is the driving force
behind this reporting system. This ID defines the remaining information in the report. For Form
GSF0400, the field order, data formats and instructions will never change; the sixth field will
always be the Company ID and the twelfth field will always be the Sulfur value, etc. If a form is
updated or changed, it will be given a new Report Form ID number. The old form will be
archived and its use will be limited to submitting resubmissions only.
-------
Comment Lines -
Comments may be added to any of the report formats so long as they are on a single line and are
not combined with any data. You should use two pound signs (##) to indicate the start of a
comment line. Since comment lines do not contain data, you may use any capitalization and
punctuation you need, provided you start the line with the ## symbol.
The examples above (Figures 1.1 and 1.2) contain the comment line "## Capital City Refinery
1999 Sulfur Batch Reports." Note that in the above spreadsheet example (Fig. 1.2), the first two
rows are comment lines. The second row, beginning with "## Report Form ID," functions as a
header for the data in the columns beneath. Since the column headers are not actual data, the
row is marked in this manner. You may use as many or as few comment lines as you need.
Column Headers -
Column Headers are unique to the spreadsheet file only and are not necessary for reporting
purposes. Note that there is no header row in the comma delimited format. Column Headers
provide a guide for entering data in the proper position. The entire row may be deleted if you
like.
Combining Reports -
With the exception of FFARS reports, a single comma delimited file or spreadsheet file may
contain all the reports (for all fuels programs) of your submission. In fact, EPA encourages the
use of a single file as it will be easier for us to track and process. With respect to spreadsheets,
there is no need to have more than one worksheet (or page). This is possible because a single
line is a complete and independent report; and there is no need to worry about the order, or
mixing of lines within a file. Within a file submission, reports should be in a logical order with
thoughtfully placed comment lines dividing different report types. At this time, due to the fact
that our computer systems are physically located in separate places, please do not combine
FFARS reports with any other program reports. We hope to update this guidance in the future to
permit combining of FFARS reports with other fuels reports.
Naming Files -
You should give your file a meaningful name up to 75 characters in length. We suggest the file
name should include the Company ID or Facility ID, and the year the averaging reports cover.
For example, reports from Company 1234 for the 2001 reporting year may be "1234_01.txt" for
a comma delimited text file. Or, for facility 67890 with resubmissions for year 2000 reports,
"67890_OOR.xls" may be a good choice.
Please use default file extensions when naming your files. Text files will normally have a .txt, or
.csv default extension while spreadsheets should have their application default extension: .xls for
Microsoft Excel, . 123 or .wk4, .wk3, etc. for Lotus 1-2-3, and .qpw for Corel Quattro Pro.
-------
Confidential Business Information -
Identification of information covered by a claim of business confidentiality will be provided for
in each report form using a "CBI" field. EPA will treat information covered by such a claim in
accordance with the regulations at 40 CFR Part 2 and applicable Agency policies. If information
is not marked as CBI, it may be released to the public.
Certification and Authentication —
The individual fuel programs generally require that data submitted be certified by an owner or
responsible corporate officer or his/her designee on-file with this office. Electronic reporting of
data will be accomplished using EPA's Central Data Exchange (CDX). CDX is similar an e-
mail system, except that it requires the individual persons responsible for reporting to register
and obtain a digital certificate. Specific instructions on the procedure and use of CDX are
provided under separate guidance (http://www.epa.gov/otaq/regs/fuels/cdxinfo.htm).
Encrypting Data -
In order to protect confidential data, EPA has incorporated an encryption algorithm into the CDX
reporting portal. Encryption is performed transparently to the user, while the data is still local to
the submitter's computer and does not require obtaining third party encryption software.
Submitting Reports -
All submissions of report data will be handled via the CDX web portal.
If you have questions or need to send other correspondence, please see the contact information
below.
Federalism Implications -
Executive Order 13132, entitled "Federalism" (64 FR 43255, August 10, 1999), requires EPA to
develop an accountable process to ensure "meaningful and timely input by State and local
officials in the development of regulatory policies that have federalism implications." "Policies
that have federalism implications" is defined in the Executive Order to include regulations and
regulatory policies that have "substantial direct effects on the States, on the relationship between
the national government and the states, or on the distribution of power and responsibilities
among the various levels of government."
This guidance document does not have federalism implications. It will not have substantial
direct effects on the States, on the relationship between the national government and the States,
or on the distribution of power and responsibilities among the various levels of government, as
specified in Executive Order 13132. It covers the procedures to follow in submitting data for
fuels-related programs. Thus, the requirements of section 6 of the Executive Order do not apply
to this guidance document.
-------
EPA Contact and Address Information -
EPA Technical Contact:
John Weihrauch (U.S. EPA)
(202)343-9477
Fax:(202)343-2802
E-mail: weihrauch.john@epa.gov
Alternate Technical Contact:
Scott Christian (U.S. EPA)
(202)343-9498
Fax:(202)343-2802
E-mail: christian.scott@epa.gov
To receive paper copies of forms and instructions, please contact:
Angie Young
(202)343-9038
Fax:(202)343-2802
E-mail: young.angie@epa.gov
Submissions via U.S. mail; please send to:
(NOTE: Our experience is that USPS mail is destroyed when portable media such as CDs or diskettes are enclosed; x-ray
equipment will actually burn mail and melt the media. In these cases we encourage the use of overnight or courier service.)
Fuels Programs Reporting (6406J)
U.S. Environmental Protection Agency
Ariel Rios Building
1200 Pennsylvania Ave. NW
Washington, DC 20460
Submissions via overnight or courier service; please send to:
U.S. EPA
Attn: Fuel Program Reporting - Angie Young
Rm. 647C
Phone: (202) 343-9038
1310 L Street, NW
Washington, DC 20005
Useful Links -
•OTAQ Fuels Reporting Forms Homepage at:
www. epa.gov/otaq/regs/fuel s/forms .htm
------- |