EPA420-F-01-007
February 2001
Gasoline Sulfur Compliance Reports:
Guidance for Preparing Spreadsheet or
Comma Delimited Text Files
for Electronic Reporting
Transportation and Regional Programs Division
Office of Transportation and Air Quality
U.S. Environmental Protection Agency
-------
The following guidance applies to periodic reporting forms and data collections related under the
Tier n Gasoline Sulfur Rule, which includes reporting related to various sulfur standards and the
generation, transfer and use of sulfur allotments and credits. 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. EPA decisionmakers retain 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.
Information submitted with regard to baseline determinations is covered by the guidance
document, "Guidance to Parties Submitting Gasoline Sulfur Baseline Applications," EPA 420-S-
00-001 (March 2000) The guidance document related to baseline applications is available
on EPA's Website at: http://www.epa.gov/otaq
Information required to be submitted for annual reporting and data collection purposes and
covered by this guidance include the following types information required under 40 CFR §
80.370:
Beginning in 2000-2003 - data related to early credits (2000-2003) and early allotments
generated by refiners (2003)
Beginning in 2004 - data related to corporate pool average standards (2004-2005), per-
gallon cap standards (beginning in 2004), geographic phase-in (GPA) gasoline standard
(2004-2006), small refiner standards (2004-2007)1, and credit and allotment activity
(beginning in 2004).
Beginning in 20052 - refinery and importer annual average standards (2005 and beyond)
This guidance does not apply to attest engagements, as required under § 80.370(f).
In order to make submission of the data as easy as possible for both reporting parties and EPA,
we request that the information be provided in electronic format. EPA will accept reporting
1 Under a hardship exemption, the small refiner standards may be in effect for 2008-2009
as well.
2 Note that in 2005 only, both the corporate pool annual average standard and the
refinery/importer annual average standard apply.
1
-------
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 Lotus 1-2-3, Microsoft Excel, and Corel Quattro Pro. If you need other
arrangements, please contact us. (See Contact and Address Information found later in this
Guidance.)
This guidance should be used in conjunction with the individual EPA form instructions
established for Gasoline Sulfur Reporting. 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.
Concept -
Whether reporting via comma delimited text file or spreadsheet file, the underlying reporting
concept is: one line (or one row in the case of a spreadsheet) is equal to one complete report.
Each report will contain the information necessary to identify the report type, the owner of the
data and the data itself. This is, in fact, flat file reporting.
Ground Rules -
These are a few items to keep in mind before getting started:
• 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.
• Some numeric fields may be positive or negative and are identified by the "±" symbol in
the form instructions. Negative numbers must be identified using the "-" sign. Positive
numbers should be unsigned, i.e. do not use "+" sign.
Do not use any punctuation in text data fields. You may use punctuation in comment
lines since they do not contain report data.
Some numeric fields such as Company ID, Facility ID and Batch ID are actually text
fields. Please include all preceding zeros when applicable.
• For those submitting comma delimited files, we can process text fields with or without
surrounding "quotation marks."
• Always use the latest version forms. The EPA website contains the latest form
instructions and templates. Additionally, the site contains an archive area with prior form
versions that may be used for resubmissions. Please visit frequently to get the latest
updates and changes.
Originals vs. Resubmissions. Once an Original report has been submitted to EPA, all
changes or updates to that report must be submitted as a Resubmission.
Each line/row in a submitted file/spreadsheet must start with either a comment symbol
(##) or a valid Report Form ID. Or the line/row must be completely blank.
A complete, single line/row, report is the combination of the overhead (identifying
information) and the report detail (the actual data).
If you have any questions, comments, or suggestions regarding reporting, please e-mail or
call.
-------
Report Forms -
Reporting forms have been separated into two types, or families — those pertaining to company
information are one and those pertaining to facility (individual refinery or import facility)
information are the other. Gasoline Sulfur Company forms are identified by the prefix 'GSC'
and deal with reporting of allotments and corporate pool averages. Gasoline Sulfur Facility
forms are identified by the prefix 'GSF' and deal with reporting of credits, individual gasoline
batches, and general facility information. Each family of forms is made up of one overhead form
and several report forms.
When complete, the forms of both families contain fields that are necessary to identify the form,
the form's owner, and the reporting year of the data. This information is called "overhead." The
overhead is common information that must be reported regardless the type of report being
submitted. In other words, all forms in a family will contain the same first half dozen or so
fields.
The detailed instructions for the report forms contain the data part of the reports only. The
overhead information has been omitted to save on space. But, no form is complete without 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.
Form Examples -
The following two examples are fictitious Sulfur Batch Report 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 Sample 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 Sample of spreadsheet Sulfur Batch Report
## Capital City Refinery 1999 Sulfur Batch Reports
Q
o
LL
•c
O
Q.
CD
o:
at
1
GSF0400
GSF0400
CD
Q.
O
Q.
CD
o:
0
o
CD
-H- <
CO
Q
O
Q.
CD
o;
02/21/2000
02/21/2000
j_
CO
p
•c
o
Q.
CD
Di
1999
1999
O
0.
O
Q
cc
E
o
O
1234
1234
Q
>,
'o
CO
12345
12345
CD
5
Z
.c
CO
00
1
2
CD
Q.
H
B
•o
2
Q_
RG
CD
CO
Q
o
B
•o
2
Q_
01/02/1999
CD
"5
B
CO
00
10000
a
3
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.
Comments -
Comments may be added to either of the report formats so long as they are on a single line and
are not combined with any data. 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, begining 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. 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.
-------
RFG & Anti-Dumping Batch Reports and Sulfur Batch Reporting -
To avoid placing additional burden on the reporting community, EPA has taken several steps
which satisfy Sulfur Batch reporting requirements through the existing RFG & Anti-Dumping
Batch reporting process. A new field has been added to the Batch Report forms which allows the
batch to be identified as GPA Gasoline (Gasoline produced in a Geographic Phase-In Area by
registered GPA refiners and importers.) This change has been implemented in existing paper
bubble reports as well as the EDI mapping. Additionally, we have chosen to release the comma
delimited and spreadsheet versions of the "RFG & Anti-Dumping Batch Report" and the "RFG
& Anti-Dumping Report for Batches Containing Previously Certified Gasoline." These reports
follow the same conventions outlined in this guidance for GSC and GSF reports, but are
identified as the RFG family of reports.
Combining Reports -
A single comma delimited file or spreadsheet file may contain all the reports 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.
Naming the file -
Give your file a meaningful name. 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. Long
file names are acceptable.
Use native file extension when naming your files. Text files will normally have a .txt, or .csv
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, Encryption and Submitting ~
Information covered by a claim of business confidentiality should be clearly marked. Submit a
cover letter, a floppy diskette containing the report file(s) and a paper printout of the file you are
submitting. Beginning May 2001, all report files submitted to this office must be encrypted.
Instructions for encrypting and submitting report files beginning May 2001 will be posted on
EPA's Website at: http://www.epa.gov/otaq or will be available through John Weihrauch (see
contact information below). EPA will treat submissions covered by a claim of business
confidentiality in accordance with established safeguards and with the requirements of 40 CFR
Part 2.
Spreadsheets and comma delimited text files should be submitted as electronic files on 1.4
megabyte floppy disks. A hard copy cover letter signed by an appropriate corporate officer
-------
certifying that the contents of the submission meet the requirements described in § 80.370(d)(2)
of the regulations, must accompany the floppy disk and printout of the data. The letter should
reference the included file name(s) containing the reports, the company name and EPA company
ID, all facility names and EPA facility IDs covered by the report(s), and include the other
information required in § 80.370, except for § 80.370(f) dealing with attest engagements and as
discussed above. Additionally, the letter should include the name, address, phone number,
facsimile number, and E-mail address of a corporate contact person.
EPA Contact and Address Information -
EPA Contact:
John Weihrauch
(202)343-9477
Fax:(202)343-2802
E-mail: weihrauch.john@epa.gov
To receive paper copies of all related forms and instructions, please contact:
Angie Young
(202)343-9038
Fax:(202)343-2802
E-mail: young.angie@epa.gov
If sent by U.S. mail, send to:
U.S. EPA
Attn: Sulfur Program (6406 J)
1200 Pennsylvania Ave. NW
Washington, DC 20460
If sent by an overnight or courier service, send to:
U.S. EPA
Attn: Sulfur Program - John Weihrauch
1310 L Street, NW
Washington, DC 20005
Phone: (202) 343-9477
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 on the
sulfur content of gasoline. Thus, the requirements of section 6 of the Executive Order do not
apply to this guidance document.
------- |