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, or
allows user to input data manually. EPA has created several basic Excel templates that use these
headers and which are intended to facilitate the use of the Basic Version. The templates are
installed with the conversion tool and are stored in the Input_Files folder. These templates are also
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.
If the user wishes to directly input 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).
&EPA
United Stoics
Environmental Protection
Agency
EPA-420-B-10-020a
May 2010
-------
EMTS Conversion Tool
Figure 1: Start-up Screen
SI EMTS Conversion Tool (ECT) Front Page
File Help
Convert a delimited text file or Excel file to EMTS XML file (files can be zipped).
Advanced Version
This version allows the flexibilty that the Basic version doesn't.
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.
N.B. Please keep in mind that the ECT performs only basic QA checks while EMTS performs high-level QA checks which are
company specific and are based on a company's registration with EPA.
Therefore, it may be possible for a user to successfully convert files to XML using the ECT but then
an upload to EMTS may fail.
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.
Transaction templates copied to C:\Documents and Settings\kkuhn\My Documents\EMTS Conversion Tool (ECT
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
Version, Advanced Version, and Build a New File options from the file selection screen, as depicted in
Figure 2.
-------
EMTS Conversion Tool
Figure 2: Toggling Between Basic, Advanced, and Build a File Versions
ffl EMTS Conversion Tool (ECT)
HP
1Ž
File | Help File List View Show Front Page Show Log WinZip
Go To Advanced Version
| * | Go To Basic Version
Process Existing Files
Build A New File
Exit
rmat an Excel or delimited file for conversion to XML. For questions <
's) on the right panel and click on the Process File(s) button to proce
;oncerning compatibilty refer to Help/About ECT
ss OR Select the Build a New File button.
1
A 1 Name
Size | Modified
1 Attribute ||
Process
Existing
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)
& EMTS Conversion Tool (ECT)
File Help File List View Show Front Page Show Log WinZip
Welcome: wgo . This application will format an Excel or delimited file for conversion to XML. For questions concerning compatibilty refer to Help/About ECT.
biep ģi - aeieci Mieisj
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.
d Error_Files
i E-d Good_Files
d History_Files
i fi-Q InpuLFiles
d Val_Log
i fid FinePrint files
id ForrnatXML
i d My Google Gadgets
d My Music
i d My Pictures
i d My Videos
! E d My Virtual Machines
i d New Folder
E d PDF files
\ E d TotarValidatorTool
ffld NetHood
Id PrintHood
i d PrivaclE
id Recent
i d SendTo
Eld Spark
fid Start Menu
id Templates v
Name | Size | Modified ] Attribute j
Mi BuyDenyTemplate.xls 30,720 3/10/2010 10: 56: 05 AM A
Ml BuyTemplate.xls 27,136 4/14/2010 3:49:52 PM A
Ml BuyTemplate2.xls 30,720 3/10/2010 10: 56: 05 AM A
Ml GenerateTemplate.xls 55,296 4/19/2010 11:44:35 AM A
Ml RetireTemplate.xls 40,960 4/1 4/201 03: 49:52 PM A
Ml SellTernplate.xls 33,792 4/14/2010 3: 49: 52PM A
Ml SellTemplateCancel.xls 37,376 3/9/2010 11:00:27 PM A
M SeparateTemplate.xls 39,424 4/14/2010 3: 49:52 PM A
Process
Existing
File(s]
Or
Build a New
File
Or
Exit
Files
Input File(s] Folder: |C:\Docurnents and Settings\wgo\My DocumentsVEMTS Conversion Tool (ECT)\lnput_Files
*Good File(s) Folder: C:\Docurnents and SettingsVwgoVMy Documents\EMTS Conversion Tool (ECT]\Good_Files
"Bad File(s) Folder: |C:\Docurnents and Settings\wgo\My DocumentsSEMTS Conversion Tool (ECT)\Error_Files
Log File(s) Folder: |CADocuments and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)Wal_Log
History File(s) Folder: C:\Documents and SettingsV.'go\My Documents\EMTS Conversion Tool (ECT)\History_Files
"Location of formatted output that has NO errors. "Location of formatted output that has errors.
Set Path
Set Path
SetPath
Set Path
Set Path
-------
EMTS Conversion Tool
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.
Figure 4: File Section Screen (Advanced Version)
S EMTS Conversion Tool (ECT)
F le Help File List View Show Front Page
Welcome: wgo . This application will format an Excel
Step 81 - If using a delimited file specify de
Start by putting your data in a compatible delimited fc
The default method is to let this application figure ou
Otherwise, you may choose your own delimiter.
This option is not needed if using an excel file, applit
HE
0Ž
Show Log WinZip
or delimited file for conversion to XML. For questions concerning cornpatibilty refer to Help/About ECT.
rrnat.
if it is a tab, comma, or bar delimited format.
ation will determine file type by it's extension.
Step ģ2 - 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
'>" Default r Tab <~ Comma
r Bar C Other: |
OR Select the Build a New File button.
1 ' 1 Error Files
i H O Qood_Files
Q History_Files
i S Ŗ3 InpuLFiles
Q Val_Log
+ _J FinePrint files
Q FormatXML
r~\ My Google Gadgets
! f~~l My Music
i-Q My Pictures
Q My Videos
! H-Q My Virtual Machines
i-Cl New Folder
i-Q PDF files
| El-Hi TotalValidatorTool
+ [J NetHood
i- CJ PrintHood
I--C1 PrivaclE
i- Q Recent
iO SendTo
[JO Spark
SO Start Menu
i f"l Templates
,--, , ,
< >
Name | Size Modified
g BuyDenyTemplate.xls 30,720 3/10/201010:56
Ģ BuyTemplate.xls 27,136 4/14/20103:49:5
1| BuyTemplate2.xls 30,720 3/10/201010:56:
1 GenerateTemplate.xls 55,296 4/19/201011:44
3 RetireTemplate.xls 40,960 4/14/20103:49:5
rf SellTemplate.xls 33,792 4/14/20103:49:5
B SellTernplateCancel.Kls 37,376 3/9/201011:00:2
1 SeparateTemplate.xls 39,424 4/14/20103:49:5
Attribute I
05AM A
2PM A
05AM A
35AM A
2PM A
2PM A
7PM A
2PM A
Process
Existing
FileM
Or
Build a New
File
Or
Exit
Input File(s] Folder: | C:\Docurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT]Mnput_Files Set Path
*Qood File(s) Folder: | C:\Docurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)\Good_Files Set Path
**Bad File(s) Folder : C:\Docurnents and Settings\wgo\My DocumentsVEMTS Conversion Tool (ECT]\Error_Files Set Path
Log File(s) Folder : 1 C:\Docurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)Wal_Log Set Path
History File(s) Folder: |C:\Documents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)MHistory_Files
"Location of formatted output that has NO errors. "Location of formatted output that has errors.
Set Path
^
-------
EMTS Conversion Tool
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.
Figure 5: File Processing Screen (Basic Version, Input File Selected)
" BuyTransaction - Processing file: BuyExample.xls
Process Rows Columns Help Show Log WinZip
Step B2 -Enter Company Info
:" User Login: [epa M Submitlal Creation Date: 12010-02-01 Submission Comment:
Organisation ID: |
Step 113 - Process And Write to XML File
Output File Options
Enit/Stop Processing
Progress:
Header Information
"typlicationUserlO: ffi
Transmission 10: TED
> User Ligh \AI
Status
Finished Loading
-ipi-it Data.Q data recoids read.
Figure 6: File Processing Screen (New File Being Built)
H BuyTransaction - Processing file:
Process Rows Columns Help Show Log WinZip
Step 83 - Enter Company Info
'User Login: || - Submittal Creation Dale: 12010-04-13 * \ Submission Comment:
Step IM - Select column to upload
Add New Empty Row I Copy Row(s) to New Row(s) I Delete Row(s) I
==> Show Legends
Headei Infomalion
'Application User ID: [
Transmission ID: TBD
"Transaction Partner
'Transaction Partner
-I
Step #5 - Process And Write to XML File
Output File Options Exit/Stop Recessing
Progress:
Columns set to Recommended Defaults
-------
EMTS Conversion Tool
Figure 7: Processing Screen (Advanced Version)
Process Rows Columns Help Show Log WinZip
Step tt3 - Enter Company Info
-UserLagin: | Submittal Creation Date: |2D 10-04-1!
1 Organization ID. [
Step ft4 - Select column to upload
Submission Comment:
Header Intonation
"Application User ID: |
Transmission ID: TBD
17 Apply User Login Value
Add Hew Empty Row Copy Rowfs) to New Row[s] Delete Rowfs] Set Columns to Recommended Defaults Import/Enpoit Company Column Names
'Select the appropriate columns tram the drop-downs below. Relei to the legend on the middle right as to whether fields are required or not. Rows can be sorted by diehnq on the column header Change/ made '.'.'ill be lerleeted in the
HOT on the input file.
Transaction Partner Organisation Identifie
FuelCode
RIHQuanticy
Traraaction Partner Orga^atio^ Name
RIHYear
BuyReasonCode
Trans act ion Date
Assigrim.
73
I Fuel Code
|RIN Quantity
[Transaction Partner Organization Ham T |
JRINYear^
|Euy Reason Code
Optional Fields
BalchVolume
RINRiceAmount
{Batch Volume
3
|R|H Price Amount
{Transaction Detail Comment Tew
|PTD Number
ballonPriGeAmouril
GenerateOrganizationldenlifier Generate Cirgamzalion Identifier
Batch NumberTewt | Batch Humber Tent
Gen ei ate F ac ility Identifier
pen
te Facility Identmer
Supporting Document Fields - Up to 2 sets
SupportingDocumentTentl
Supporting Document NumberText 1
Supporting Document NumberTeHt2 | Supporting Document Number Tent 2 *_\
All Remaining Fields
| Supporting Document Number Taut I T ]
Supporting Document Tent 2
(Fuel Codes _rj
EljyReason Codes T
Assignment Codes jģJ
'Required Fields Optional Fields
Acceptable combinations are
Generate Organizer Identifier only,
Generate Organizer Identifier and Generate Facility only.
Generate Organizer Identifier, Generate Facility Identifier
and Batch Humber
Supporting Document Details Fields are conditionally
optional.
Reason Code' If a reason code of 60 [cancel trade] is
present there may only be one data row per file.
Field Formats: All Organization Identities mun be 4 digits
All Facility Identifier; must be 5 digit;
All 'Codes' must be numeric
Date Formats: Must be VAY-MM-DD and not > the
Submittal Creation Date. Formats of yyyy-m-d, yyyy/mm/dd,
yyyymmdds, or m>dlyyyy hh:mm:ss will be converted.
Step U5 - Process And Wiite to XML File
Piogiess:
oading Input Dar.a.1 data records read.
(I
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.
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;
-------
EMTS Conversion Tool
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 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.
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 "##."
-------
EMTS Conversion Tool
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
^
RIN
Quantity
5000
6000
D
1
Batch
Volume
5000
6000
E
1
RIN Year
2009
2009
F
1
RIN Price
Amount
0.19
0.18
GE
P
An-
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;
Generate Organization Identifier;
Generate Facility Identifier;
Batch Number Text;
Feedstock Code 1;
Renewable Biomass Indicator 1;
-------
EMTS Conversion Tool
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;
Separate Reason Code; and
RIN Year.
-------
EMTS Conversion Tool
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).
-------
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. 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;
-------
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.
------- |