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

-------