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 ------- |