EMTS Conversion Tool (ECT)
Purpose and Description
The EMTS Conversion Tool (ECT) has been developed to assist users in the preparation of XML files for
submission to the EMTS. The ECT takes as input Excel or text files and produces as output XML files that
conform to the EMTS schema specification (see Appendix C for a logical representation of this schema).
Within the ECT, users may select to use either the Basic Version or the Advanced Version.
• Basic Version: This version takes as input files that use EPA-defined standard column headers. EPA
has created several basic Excel templates that use these headers and which are intended to
facilitate the use of the Basic Version. These templates are available for download on the Fuels
Reporting Forms Page: http://epa.gov/otaq/regs/fuels/rfsforms.htm.
• Advanced Version: This version does not require the use of the basic EPA templates, or that the
input files use the standard headers (though the templates or other files with the standard headers
can be used). Users can create mappings between their own user-defined column headers and
labels that will be used in the XML files. Users can also input data manually and change existing
data for conversion. The Advanced Version physically shows the user the errors that may be in
their data, whereas the Basic Version provides an error message and an entry in the log.
If the user is already producing user-defined formatted files
that do not have the same column headings as those found
in the template, then the Advanced Version must be used
and the column headers must be mapped to the proper ECT
column headers so that the data is read appropriately and
valid output is produced.
Overview of the ECT Screens
Data Entry
Inexperienced users are advised to use
the EMTS website for manual data entry.
The ECT does not run Quality Assurance
checks on data.
The User Interface for both the Basic and the Advanced Versions of the ECT consists of three screens.
1. A "Start-up" screen where the user can select whether to use the Basic or Advanced Version (see
Figure 1).
United Stales
Environmental Protection
Agency
EPA-420-B-10-020
March 2010
-------
EMTS Conversion Tool
Figure 1: Start-up Screen
Format XML Front Page
- n x
Convert a delimited text file or Excel file to EMTSXMLfile (files can be zipped).
Advanced Version
Go This version allows the flexibilty (hat the Basic version doesn't.
Basic Version
Go
This is a basic version which requires:
1) If using a text file then it must be a tab, comma, or bar delimited format.
2) The columns of the files must be the EPA recommended column names.
3) For an existing file all elements must be included, set unused defaults as blanks.
i— Don't show this page anymore.
If later you would like this see this display then, on the Main Menu, select ShowFrontPage and unselect Don't Show.
Exit
This screen allows the user to select from two versions of the ECT: the Basic Version or the Advanced
Version. The user may check the "Don't show this page anymore" box if desired. This will cause the start-
up screen to be skipped on subsequent uses of the ECT, resulting in the processing starting up directly at
the screen where the input files are chosen. The version that will be used would be the last version chosen
after selecting the "Don't show this page anymore" box. This feature can be turned back on from the file
selection screen (see Figures 3 and 4) by clicking on "Show Front Page" and then selecting "Show Front
Page" when the start-up page appears. Additionally, the user can toggle back and forth between the Basic
and Advanced Versions from the file selection screen, as depicted in Figure 2.
Figure 2: Toggling Between Basic and Advanced Versions
H EMTS Conversion Tool (ECT)
File | Help File List View Show Front Page Show Log WinZip
his application will format an Excel or delimited file for conversion to XML.
n x
| "* | Go To Advanced Version
Go To Basic Version
Process Existing Files
Exit
specify delimiter
3 delimited format.
on figure out if it is a tab, comma, or bar delimited format.
slimiter.
Delimiters
Default C Tab <~ Comma
Bar f~ Other: \~
-------
EMTS Conversion Tool
2. A File Selection screen where the user can select the file to be processed. Figure 3 displays the
Basic Version and Figure 4 displays the Advanced Version.
Figure 3: File Selection Screen (Basic Version)
II EMTS Conversion Tool (ECT)
File Help File List View Show Front Page Show Log WinZip
: Welcome to Version 1.0.0.8.4. This application will format an Excel or delimited file for conversion to XML.
Step ttl - Select File(s)
Select a Folder on the left panel and File(s) on the right panel and click on the Process File(s) button to process OR Select the Build a New File button.
EMTS Conversion Tot A
!-Cj Error_Files
Pi Good_Files
!--^3 lnput_Files
LJ Val_Log
CJ FormatXML
Pi My Data Sources
Pi My Music
PJ My Pictures
PJ My Videos
H-Pj NetHood
i+; Pi personal
Pj PrintHood
PJ Recent
SarnetimeTranscripts
-Pi SendTo
i+';--Pj Start Menu
--Pi Templates
:-Pl Localservice
r~\ Networks ervice
S-CJ DRV
CJ ForrnatFile1002
il Q PNPDRVRS
<
-
Name
Size | Modified
| Attribute
g BuyExample.xls 27,136 2/22/20101:06:04PM A
Ml GenerateExample.xls 35,940 1/13/2010 2:32:46PM A
m: RetireExarnple.xls 27,136 1/13/2010 2:34:29PM A
Process
Existing
File(s)
Or
Build a New
File
Or
Exit
Files
File(s) Folder: | C: \D ocurnents and S ettings\kkuhn\M y D ocuments\E M T S Conversion Tool (E CT ]\l nput_Files
id File (s) Folder: | C:\Docurnents and Settings\kkuhn\Mii DocumentsMEMTS Conversion Tool (ECT)\Good_Files
d File(s) Folder : j CAD ocurnents and Settings\kkuhn\Mii DocumentsMEMTS Conversion Tool (ECT)\Error_Files
- i 1 e (s) Fo 1 d e r : |C:\DocurnentsandSettings\kkuhn\MiiDocuments\EMTSConversionTool(ECT)\Val_Log
"Location of formatted output that has NO errors.
""Location of formatted output that has errors.
Set Path
Set Path
Set Path
Set Path
By default, the files in the "Input_Files" folder will be displayed on the right-hand side of the File Selection
Screen. The user can also use the folder navigation available on the left-hand side of the File Selection
Screen to find files in other folders. Either a single file or multiple files can be selected; if multiple files are
desired, the selection can be done through the use of the control (CTRL) key. Once the desired file(s)
is(are) selected, the "Process Existing File(s)" button should be clicked. The File Processing Screen will
automatically appear once per file selected. The user also has the option of clicking on "Build a New File,"
in which case it is not necessary to select an input file.
-------
EMTS Conversion Tool
Figure 4: File Section Screen (Advanced Version)
m EMTS Conversion Tool (ECT)
File Help File List View Show Front Page Show Log WinZip
: Welcome to Version 1.0.0.3.4. This application will format an Excel or delimited file for conversion to XML.
step Hi - ii using a delimited rue speciiy delimiter.
Start by putting your data in a compatible delimited format.
The default method is to let this application figure out if it is a tab, comma, or bar delimited format.
Otherwise, you may choose your own delimiter.
This option is not needed if using an excel file, application will determine file type by it's extension.
Delimiters
* Default
r Bar
r
r
Tab
Other: [
(~ Comma
step »z - select riieisj
Select a Folder on the left panel and File(s) on the right panel and click on the Process File(s] button to process OR Select the Build a New File button.
- I 1 EMTS Conversion Toe A
I- fn Error_Files
• Q Good_Files
:•• £3 lnput_Files
'•• Q Val_Log
: Q FormatXML
'•. ] My Data Sources
'. j My Music
: Q My Pictures
: Q My Videos
El D NetHood
H Q personal
Q PrintHood
; Q Recent
••••n SametirneTranscripts
: Q SendTo
El Q Start Menu
C~\ Templates
i-Pl LocalService
i[-C~\ NetworkService
: El-Q DRV
D FormatFile1002
: HQ PNPDRVRS
,-,,-. ^-,
^"l I > i
Name | Size | Modified | Attribute |
M BuyExample.xls 27,136 2/22/2010 1:06:04 PM A
'ft GenerateExample.xls 35,840 1/13/2010 2:32:46 PM A
M RetireExample.xls 27,136 1/13/2010 2:34:29 PM A
Process
Existing
File(s)
Or
Build a New
File
Or
Exit
rues
Input File(s) Folder: |C:\Documente and Settings\kkuhn\My Documents\EMTS Conversion Tool (ECT)\lnput_Files
*Good File(s) Folder: |CADocuments and Settings\kkuhn\My Documents\EMTS Conversion Tool (ECT)\Good_Files
**Bad File(s) Folder : |CADocuments and Settings^kkuhn\My Documents\EMTS Conversion Tool (ECT)\Error_Files
Log File(s) Folder : |CADocuments and Settings\kkuhn\My Documents\EMTS Conversion Tool (ECT)Wal_Log
"Location of formatted output thai has NO errors.
""Location of formatted output that has errors.
Set Path
Set Path
Set Path
Set Path
This screen is similar to the File Selection Screen for the Basic Version, except for the addition of an extra
section at the top where users can specify the delimiters used for text-based input files.
3. A File Processing screen where data can be edited and the actual conversion to XML is initiated. See
Figures 5 and 6 (Basic Version) and Figure 7 (Advanced Version) below.
-------
EMTS Conversion Tool
Figure 5: File Processing Screen (Basic Version, Input File Selected)
," Bu/Transaction - Processing file: Buy Examples Is
Process Rows Columns Help Show Log WinZip
Step #3 - Process And Write to XML File
Output File Options Enit/Stop Recessing
Progress:
Finished Loading Input Data.O data records read.
1 User Login: jepa • Submitial Creation Date: |£Q1D-02-Q1 Submission Comment:
1 Organisation ID: |
Transmission ID: TBD
W flppty User bgin Vsl
<
Figure 6: File Processing Screen (New File Being Built)
Process Rows Columns Help Show Log WinZip
Step 83 - Entei Company Info —^—
'"UserLjjgin: f - Submittal Creation Date: 12010-02-11 Submission Comment:
* Organization ID: |
Step 14 - Select column to upload
Add New Empty Row Copy Row(s) to New Row(s) Delete Row(s) I Show Legends
Step 85 - Piocess And Write to XML File
•mimmmmmmmmmmmmmmmmmmmmmmmmm
-------
EMTS Conversion Tool
Figure 7: Processing Screen (Advanced Version)
Header Infomalion
'Application User ID: [
Transmission ID: TED
Process Rows Columns Help Show Log WinZip
Step 83 - Enter Company Info ^
'User Login: |§B * Submitta! Creation Date: J2Q10-02-C! Submission Comrm
" Organisation ID- |
Step ft4 - Select column to upload
Add New Empty Row Copy Row(sJ to New Row(sJ I Delete Rowfs) Set Columns to Recommended Defaults Import/EKport Company Column Names |
op-down; below. Refer to the legend on the middle right as to whether fields are required or not. Rows can be sorted by clicking on the column header. Changes made will be reflected in the n
Apply User Login Vali
Select the appropriate columns fr
HOT on the input file.
Required Field:
' Transaction Partner Organisation Identifier
Ass ig n m ent C ode
[ Fuel Code
: RINQuantity
: Transaction Partn
1 RINYear
: BuyReasonCode
! Transaction Date
Supporting DocumentTent 1
Supporting Document NumberTentl
Supporting Doeum
Optional Fields
Batch Volume
RtNPriceAmount
Transaction Detail CommentTenl
PTDNumbi
Lists of Allowed Values and Leqend
RIM Price Amount
RIH Quantity •*
ionName [Transaction Partner Organisation Nam T
RIN Year _rj
5uy Reason Code ^|
JTrarisaction Date •*•]
- Up to 2 sets
-rTent!
>,TeK,2
'?up|."-'rtmq Document Tent 1 •»
Supporting Document Number Tent 1 f
?up|.'orliiig Document Tent 2 T
Supporting Document Number Tent 2 T
G a II on FV ic e Amou nt
GenerateOrganisationldentifier
Batch NumfaerTeKt
Generate Facility Identifier
AJI Remaining Fields
Gallon Price Amount _»J
Generate Organisation Identifier T
Batch Number Tent -r
Generate Facility Identifier T |
jj
"Required Fields Optional Fields
Originating Source Detail Fields are conditionally optional.
Acceptable combinations are:
All three elements absent.
Generate Organiser Identifier only.
Generate Organiser Identifier and Generate Facility only.
Generate Organiser Identifier, Generate Facility Identifier
and Batch Number
Supporting Document Details Fiddj are conditionally
optional.
Other Conditional Fields
Assgnment CodefAC) must be 1 or 2
If = 1 Rice Per RIN must be present or
If = 2 Price Per Gallon must be present.
If a reason code of 60 [cancel trade) is present you may
have only one data row per file.
Field Formats: AJI Organisation Identifiers must be 4 digits
AJI Facility Identifiers must be 5 digits
AJI 'Codes' must be numeric.
Date Formats: Must be YTvY-MM-DD and not * the
Submittal Creation Date. Formats of yyyy-m-d, yyyy/mm/dc
yyyymmdds, or m/d/yyyy hh:mm:5s will be converted.
Step tt5 - Piocess And Write to XML File
Output File Options Eriit/Stop Processing
Finished Loading Input Data.O data
Progress:
immiiiiiiiiiiiiiiiiiiiiiiimmmiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
mmimmmmimmmmimi
Compared to the basic processing screen, the advanced processing screen is different in the following ways:
• The data grid is always present (in the Basic Version, the data grid only appears if the "Build a New
File" button is clicked on the File Selection Screen);
• There is a set of labels and drop-down menus where column headings can be mapped to the labels
that will be written to the output XML; and
• There is a button which allows user-defined column names to be imported and exported.
-------
EMTS Conversion Tool
Transaction Types
The ECT can create XML files for all five of the transaction types supported by the EMTS: generate,
separate, buy, sell, and retire.
• A generate transaction is only to be used by organizations who produce renewable fuel;
• A separate transaction is to be used by parties who blend fuel and separate RINs;
• A sell transaction is to be used by parties who have sold RINs;
• A buy transaction is to be used by parties who have bought RINs; and
• A retire transaction is to be used by parties who have retired RINs for any reason.
For detailed information on the data elements for each transaction, including allowed values, please see the
EMTS Transaction Instructions -- Section 6, "How Do I Report Transactions Using XML Files?"
Required, Conditional, and Optional Fields
Fields are either required, conditional, or optional.
• A required field is necessary for every transaction;
• A conditional field is dependent on the data that was entered in previous fields and may be
required if certain conditions are met; and
• An optional field is not required by EPA, but may contain helpful descriptive information about the
transaction.
Setting Up the Input Files
The ECT (both Basic and Advanced Versions) accepts file types which are identified by the following
extensions:
• .txt - tab or bar delimited text;
• .csv - comma delimited text; and
• .xls - Excel spreadsheet
The Advanced Version also accepts text files which do not use commas, tabs, or bars as delimiters.
For these file types, the ECT accepts either a zipped or unzipped file (if zipped, the file extension will be
".zip").
Although the Basic Version accepts data in the formats listed above, the basic EPA templates are only
available in Excel format.
Any row in an input file that has two hash symbols (#) at the start will be omitted from the conversion and
the first row that does not have "##" and is not blank will be assumed to contain the column headings.
-------
EMTS Conversion Tool
Figure 8 shows a sample file in which one of the rows (line 4) starts with "##" and will therefore be ignored
by the ECT. In this example, the ECT will take the column headings from row 2 because this is the first row
that is not blank and does not start with "##."
Figure 8: Omitting a Data Line
1
2
3
4
5
6
7
8
9
10
A
Transaction
Partner
Organization
Identifier
##1234
2345
B
Transaction
Partner
Organization
Name
Bob's Ethanol
Mary's Ethanol
C
i
RIN
Quantity
5000
6000
D
Batch
Volume
5000
6000
E
RIN Year
2009
2009
F
i
RIN Price
Amount
0.19
0.18
GE
P
Arr
Transaction Data Elements in the ECT
The following sections describe, for each transaction, the elements that can be included in the files that are
input to the ECT. For each transaction, elements are grouped according to whether the ECT treats them as
required, conditional, or optional. The element names presented here are equivalent to the standard
column headers defined by EPA and used in the basic Excel templates.
Note that the absence of an element treated as optional by the ECT can still cause the output XML to fail
validation when it is submitted to the EMTS; for instance, the ECT treats "Denaturant Volume" as optional
even though the EMTS may require this element depending on the value of "Fuel Code."
Generate Transaction Data Elements
The following describes the generate transaction data elements. Up to three feedstocks and co-products
may be specified in the ECT.
The following elements are required:
• Fuel Code;
• Process Code;
• Production Date;
• Fuel Category Code;
• Batch Volume;
• RIN Quantity;
-------
EMTS Conversion Tool
• Generate Organization Identifier;
• Generate Facility Identifier;
• Batch Number Text;
• Feedstock Code 1;
• Renewable Biomass Indicator 1;
• Feedstock Volume 1; and
• Feedstock Measure Code 1.
The following elements are conditionally required:
• CoProduct Code 1;
• CoProduct Code 2;
• CoProduct Code 3;
• Feedstock Code 2, Renewable Biomass Indicator 2, Feedstock Volume 2, Feedstock Measure Code 2
(if any one is present, the others are required);
• Feedstock Code 3, Renewable Biomass Indicator 3, Feedstock Volume 3, Feedstock Measure Code 3
(if any one is present, the others are required); and
• Denaturant Volume (if ethanol is produced, this field is required).
The following elements are optional:
• Equivalence Value;
• Import Facility Identifier;
• Transaction Detail Comment Text;
• Feedstock Detail Comment Text 1;
• CoProduct Detail Comment Text 1;
• Feedstock Detail Comment Text 2;
• CoProduct Detail Comment Text 2;
• Feedstock Detail Comment Text 3; and
• CoProduct Detail Comment Text 3.
Separate Transaction Data Elements
This section describes the separate transaction data elements.
The following data elements are required:
• RIN Quantity;
• Batch Volume;
• Fuel Code;
-------
EMTS Conversion Tool
• Separate Reason Code; and
• RIN Year.
The following data elements are conditionally required:
• Supporting Document Text, Supporting Document Number (if one is present, the other is required);
• Generate Organization Identifier (required if Generate Facility Identifier is present);
• Generate Facility Identifier (required if Batch Number Text is present);
• Blender Organization Identifier (required if reason code is 40: Upstream Delegation for Blending);
and
• Blender Organization Name (required if reason code is 40: Upstream Delegation for Blending).
The following data elements are optional:
• Batch Number Text; and
• Transaction Detail Comment Text
Sell Transaction Data Elements
This section describes the sell transaction data elements.
The following data elements are required:
• Transaction Partner Organization Identifier;
• Transaction Partner Organization Name;
• RIN Quantity;
• Fuel Code;
• Assignment Code;
• RIN Year;
• Sell Reason Code; and
• Transaction Date.
The following data elements are conditionally required:
• Supporting Document Text, Supporting Document Number (if one is present, the other is required);
• Generate Organization Identifier (required if Generate Facility Identifier is present);
• Generate Facility Identifier (required if Batch Number Text is present);
• RIN Price Amount (required if GallonPriceAmount is not present);
• Gallon Price Amount (required if RINPriceAmount is not present); and
• Batch Volume (required if the RIN is assigned).
10
-------
EMTS Conversion Tool
The following data elements are optional:
• Batch Number Text;
• PTD Number; and
• Transaction Detail Comment Text
Buy Transaction Data Elements
This section describes the buy transaction data elements.
The following data elements are required:
• Transaction Partner Organization Identifier;
• Transaction Partner Organization Name;
• RIN Quantity;
• Fuel Code;
• Assignment Code;
• RIN Year;
• Buy Reason Code; and
• Transaction Date.
The following data elements are conditionally required:
• Supporting Document Text, Supporting Document Number (if one is present, the other is required);
• Generate Organization Identifier (required if Generate Facility Identifier is present);
• Generate Facility Identifier (required if Batch Number Text is present);
• RINPriceAmount (required if GallonPriceAmount is not present);
• GallonPriceAmount (required if RINPriceAmount is not present); and
• Batch Volume (required if the RIN is assigned).
The following data elements are optional:
• Batch Number Text;
• PTD Number; and
• Transaction Detail Comment Text.
Retire Transaction Data Elements
This section describes the retire transaction data elements.
The following data elements are required:
• RIN Quantity;
• Fuel Code;
• Assignment Code;
11
-------
EMTS Conversion Tool
• RIN Year; and
• Retire Reason Code.
The following data elements are conditionally required:
• Supporting Document Text, Supporting Document Number (if one is present, the other is required);
• Generate Organization Identifier (required if Generate Facility Identifier is present);
• Generate Facility Identifier (required if Batch Number Text is present);
• Compliance Year (if Retire Reason Code is Demonstrate Annual Compliance, this field is required);
• Compliance Facility Identifier (if Retire Reason Code is Demonstrate Annual Compliance, this field
is required); and
• Compliance Level Code (if Retire Reason Code is Demonstrate Annual Compliance, this field is
required).
The following data elements are optional:
• Batch Number Text;
• Batch Volume; and
• Transaction Detail Comment Text
Data Validation Checks in the ECT
The ECT performs many (but not all) of the data validation checks needed to ensure that the output XML
file can be successfully processed by the EMTS. Errors found by the ECT are written to the "Status"
message block above the progress bar at the bottom of the screen and to the log file. The following is a list
of checks that will be performed during processing.
• Checks that the "Enter Company Info" fields are filled in (see Figure F-20): All of these fields are
required except "Submission Comment" Only the "User Login" is saved between file processing.
Submittal creation date must be in the form of YYYY-MM-DD.
• Checks the drop-down lists of column headers for the required, optional, and conditionally optional
fields. The following will produce an error:
-- For a required field, if the user has not selected a column name from the drop-down list or
provided data;
-- For a conditional field or optional field, if the user has indicated that it should be included by
selecting a column name from the drop-down list, but has not provided the corresponding data;
12
-------
EMTS Conversion Tool
-- If there is no value selected on the drop-down list that corresponds to the column;
-- If the value selected in the drop-down list does not match any of the column names; and
- If the same column heading is selected in multiple drop-down lists for different required fields.
• Checks the transaction data. The following will produce an error:
- If data is missing in a required field (note that some fields are conditionally required, meaning
that if a certain value is present, then all the following fields are required);
- If "Assignment Code" has a value other than "1" or "2";
- If transaction codes contain letters;
- If "Transaction Partner Organization Identifier" is not four digits; and
- If dates not of the format YYYY-MM-DD. If dates are of the format YYYY/M/D, YYYY-M-D, or
MM/DD/YYYY the ECT will make the appropriate change which will be displayed in the data
grid without any error messages.
13
-------
EMTS Conversion Tool
[Thispage intentionally left blank.]
14
------- |