Running the Basic Conversion Tool

Prior to starting the ECT, the user should place the Excel files that will be used for input in the following
folder:

    C:\Documents and Settings\\My Documents\EMTS Conversion Tool (ECT)\Input_Files

Figure 1 depicts this folder for a user "wgo."  The "Input_Files" folder has been circled in this figure. Note
that the extension for all Excel files saved in this folder should be .xls rather than .xlsx (the default for Excel
2007).
Figure 1: EMTS Conversion Tool Input File Directory
    EMTS Conversion Tool (ECT)
  File   Edit  View   Favorites  Tools  Help
  QBack '
Search
                                      Folders
Address  _/ C:\Documents and Settings\wgo\My Docurnents\EMTS Conversion Tool (ECT)

   File and Folder Tasks
                    •
                      C*)
        My Documents
        My Computer
    % J Mv Network Places
                                                               P
                                         Good Files
                                                                      Val_Log
                                                                                               Go
Running the ECT

The ECT can be opened from the Start Menu.  Upon starting the ECT, the start-up screen is displayed (see
Figure 2).
           United Stales
           Environmental Protection
           Agency
                                           EPA-420-B-10-010a
                                           March 2010

-------
 EMTS Conversion Tool
Figure 2: Start-up Screen
 EH EMTS Conversion Tool (ECT) Front Page
   File
                 Convert a delimited text file or Excel file to EMTS XML file (files can be zipped).

        Advanced Version
             Go     I   This version allows the flexibly that the Basic version doesn't.
        Basic Version
             Go
-,  This is a basic version which requires:
_ I   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.
                                                  Exit
On the  start-up screen, the user has the  option  to  click the checkbox beside "Don't show this page
anymore." This will cause the ECT to open on the File Selection screen instead of the start-up screen. Click
the "Go" button under Basic Version.


Selecting Files
After clicking the "Go" button, the File Selection Screen appears (see Figure 3).

-------
 EMTS Conversion Tool
Figure 3:  File Selection Screen
    EMTS Conversion Tool (ECT)
   File   Help   File List View   Show Front Page   Show Log   WinZip
   wgo: Welcome to Version 1.0.0.8.4. This application will format an Excel ot delimited file for conversion to XML.
   Step tt1 - Select Hle(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.
                - I_J EMTS Conversion To< A
                   •-Pi Error_Files
                   +  LJ Good_Files
                •   B -£3 lnput_Files
                     Pj Val_Log
              !  ffl-Pj FinePrint files
                l-Pj FormafXML
                ;  Pi My Google Gadgets
                  Pi My Music
                •••Pj My Pictures
                !  Pj My Videos
              :  If Pi My Virtual Machines
                :  Pj New Folder
                + _| PDF files
                t LJ TotafvalidatorTool
             + _|  NetHood
              |--CJ  PrintHood
              • Pj  PrivaclE
              • -Pi  Recent
              ; • D  SendTo
             ffl Pj  Spark
             IJl Pj  Start Menu
              i- Pj  Templates            v
Name
Size |  Modified
                                                       Attribute
Ml BuyDenyEnample1_Feb3.Kls         37,888
1 BuyExample1_Feb3.Kls            37,888
H GenerateAdvancedExarnple1_Feb3.xls 61,952
H GenerateEnample1_Feb3.xls         61,852
B RetireExample1_Feb3.xls           44,544
B SellCancelExample1_Feb3.xls        37,376
PJ SellExarnple1_Feb3.xls            37,376
"Ml SeparateExample1_Feb3.xls         43,008
     2/3^201 02: 07: 47PM A
     2/3/2010 1:28:37 PM A
     2/3/2010 2:1 1:08 PM A
     2/3/201 01:54:20PM A
     2/3/2010 1:27:28 PM A
     2/3/201 02: 02: 24PM A
     2/3/2010 1:57:57 PM A
     2/3/2010 1:26:35 PM A
    Files
    Input File(s] Folder:    |CADocuments and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)Mnput_Files
    *Good File(s) Folder:  C:\Documents and Settings\wgo\My DocumentsVEMTS Conversion Tool (ECT]\Good_Files
    "Bad File(s) Folder: |CADocurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)\Error_Files
    Log File(s) Folder:   |C:SDocurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)Wal_Log

             "Location of formatted output that has NO errors.
            ""Location of formatted output that has errors.
                                          Exit
                                                                          Set Path
                                                                                                                  Set Path
                                                                          Set Path
                                                                          Set Path
The user should select the desired file(s) and click the "Process Existing File(s)" button, or click the "Build a
New File" button without selecting any files.  To select multiple  files, the user should press "Ctrl"  on the
keyboard and click on all of the files to be processed.  If the file name contains the transaction type, the ECT
will assume  that the named transaction type is the type to process and will go immediately to the File
Processing screen (Figure 4)  when  "Process Existing File(s)"  is clicked.  If the transaction  type is  not
contained in  the file name, the user will be asked to specify the transaction type (see Figure 8).

-------
EMTS Conversion Tool
Processing Files
The File Processing screen is depicted in Figure 4. First, the user should complete Step #3, "Enter Company
Info." The required company information includes:
    •  User Login (case sensitive and must be the same as the EMTS login);
    •  Organization ID (EPA assigned, four-digit ID); and
       Submittal Creation Date.
Figure 4: File Processing Screen
S! BuyTransaction - Processing file: BuyBizoBlenders.xls
 Process  Rows  Columns  Help  Show Log WinZip
 Step tt2 - Entei Company Info
              iubmtttal Cration Dste 2010-02-01  Submission
Header Intonation
".•^plication User ID: |epa
Transmission ID: TED
                                                                                             y ^pply User Login \fal
 Step «3 - Process And Write to XML File

  Output File Options  Exit/Stop Recessing
  Progress:
                     Finished Loading Input Dala.l data records read.
The ECT uses the following default settings when processing files:  the output file is an uncompressed XML
file,  and the output file name  is formatted as "TtOrgId_YYYY-MM-DD-HH-MM-SS"  where  "Tt"  is the
transaction type (i.e., Generate) and "Orgld" is the four-digit ID entered in the "Organization ID" field.  To
change these default settings, the user should click the "Output File Options" button (see Figure 5).
Figure 5: "Output File Options" Button
S BuyTransaction - Processing file: BuyExamplel .xls - H X 1
Process Rows C
Step tt2 Enter Co
:* User Login: |
1 Organisation ID: |

(T Output File Options
Process File
Progress:
Dlumns Help 5how Log WinZip
• Submittal Creation Date: 12010-01-26 Submission Comment:


J Exil/Stop Processing
Finished Loading Input Data.3 data records read.

".^plication User ID: | '** ApP1*1 Usel Login Value
Transmission ID: TED
1



•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••I
•C 1 >
On the  Formatted  Output File Options screen (see Figure 6), the user can specify the output file type,
compression, and file name  (compressed XML is the only format accepted by EMTS).  To specify the file
name, the user should check the checkbox to override the default name and then enter a new file name.
The ECT automatically appends the transaction type of the file being processed to the beginning of the new

-------
 EMTS Conversion Tool
file name. In the example in Figure 6, if a Generate file were being processed, the output file name would be
"Generate01-26-2010.xml."  Once a  user updates the Formatted Output File  Options screen, the updates
become the default setting for subsequent file conversions and remain so until the user goes back to the
Formatted Output File Options screen and changes the settings.
Figure 6:  Formatted Output File Options Screen
    Formatted Output File Options
-  n  x
                File type XML is the ONLY option required for submission/transmission
         File Type(s)
        1^  XML - This is the ONLY option required for subrnissionAransmission
        I"  Excel - For your use only, not to be transmitted (if transmitted, it will be ignored.)
        l~  Text - For your use only, not to be transmitted (if transmitted, it will be ignored.)
         File Compression (using WinZip32)
        '•'  Do Not Compress any Files
        C*  Compress only the XML File for Node Submission/Transmission
        r  Compress All Output File Types Selected Above (XML, Excel, Text) - It is NOT necessary to include Excel or Text in the compression.
         Override Output File Name
        [^ Override the default name of TtOrgld_YYrY-MM-DD-HH-MM-SS where Tt is the Transaction Type. The new file name will be
            prepended with the Transaction Type.
         0 verridden File N ame:  101-26-2010
            Do NOT include the Folder (Path) name. The "Good Files" folder is assumed.
         OtherFileOption(s) —
        l~~  Display Processed File
                                                          OK
After specifying the output file options, the user should click the "Process File" button. Note that if multiple
files have been selected for processing, only one file is processed at a time.  In this case, a "Next file" button
will be visible next to the "Process File" button (see Figure 7).  The user should first click the "Process File"
button, verify that the file  processes successfully (see the Results of File Processing section in this Quick
Guide), and then press the "Next file" button.

-------
EMTS Conversion Tool
Figure?:  "Next file" Button
SJ BuyTransaction - Processing file: Buy8izoBlenders.xls
 Process  Rows  Columns  Help  Show Log  WinZip
 Step tt2 -Enter Company Info
 User Login: I

 Organisation ID:
               lubmittnl Creation D=l* 2010-02-01
Header Infomation
"Application User ID: |epa
Transmission ID: TED
  Step «3 - Process And Write to XML File
        File   (^ Newt file J)
  Progress:
                        Finished Loading input Data.1 data records read.
Creating New Files

If the "Build a New File" button is clicked on the File Selection Screen (see Figure 3), a screen will appear
asking the user to specify the transaction type (see Figure 8).
Figure 8: Selecting the Transaction Type
                          IS Choose the Transaction Type
                            Select the transaction type from the drop-down pick list below for file:
                                          Select Transaction Type:
                                          Enter       Skip this file
Once the user selects the transaction type and clicks "Enter," the File Processing screen appears (Figure 9).

-------
 EMTS Conversion Tool
Figure 9: File Processing Screen
 '• GenerateTransattion - Processing file:
  Process  Rows  Columns   Help  Show Log  WinZip
  Step 83 - Enter Company Info       	^	
 -User Login: f^S' Submitlal Creation Date: J2Q 10-02-01   Submission Comment:
 * Organisation ID: |
  Step 1M - Select column to upload
                                                                                          Headei Infomation
                                                                                          Vippli cation User ID: j
                                                                                          Transmission ID: TBD
                                1 Appfy User Login Va
  Add New Empty Row  Copy Rowfs) to New Row(s)   Delete Rowfs)
  Select the appropriate columns from the drop-downs below. Refer to the legend on the middle right as to whether fields are required or not. Rows can be sorted by flicking on the column header. Changes made will be reflected in the new output file and
  NOT on the input file,
#
Production
Date
Batch Denaturant
Equivalence
Value
PIN
Quantity
Import Faci
Identifier
ity
Iran
Com
action Detail
nentText
Identifier
Generate Facility
Identifier
Bat
Nur
   Required Fields
  * Fuel Code
  " Batch/Volume
  " FudCalegoryCode

  * Production Date

  1 Process Code

  ' RINQuantily

  1 Generate Fadityiden

  1 Batch NumberTeHt
   Optional Fields
  DenaturantVolume
  EquivalenceValue
  Generate Organization
  Transaction Detail Comi

  Import Facility Identifier
Identifier
•peritTenl
                                  CoProduct Fields - Up to 3 sets
                                  Co Product Code 1

                                  Co Product Detail Comment Tent

                                  Co Product Code2

                                  Co Product Detail Comment Tent

                                  Co Product CodeS

                                  Co Product Detail CommentTewt
Feedstock Fields - Min
* Feedstock Code 1

1 Renewable Biomass Indicator!

" Feedstock Volume 1

' Feedstock Measure Code 1

* FeedstockDetailCommentTewtl

* Feedstock Cotte2

" Renewable Biomass Indicator2

' FeedstockVolume2

* Feedstock Measure Code2

* Feedstock Detail Comment Te«t2

* Feedstock Code3

" RenewableSiomassindicatorS

" FeedstockVolume3

' Feedstock Measure CodeS

1 Feedstock Detail CommentTeKt3
  Step 85 - Process And Write to XML File
   Output File Qptk
                Enit/Stop Proi
     Process File

  Progress:
                  s set to Recommended D
  iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiifii
                                         iiiimmmmimmmimimiiimimmmmmmmmi
First,  the user should  complete  Step  #3, "Enter  Company  Info."   The  required  company  information
includes:

     •    User Login (case sensitive and must be the same as the EMTS login);
     •    Organization ID (EPA assigned, four-digit ID); and
     •    Submittal Creation Date.

Under the "Enter  Company Info" section, a data grid is displayed.  The user  should enter  the transaction
data in the grid and click the "Process File" button.

Results of Processing
If processing is  successful,  the  screen  will  display a message that  reads "Records  were  successfully
processed.  Formatted to:  XML file" (see Figure 10).

-------
EMTS Conversion Tool
Figure 10: A Successfully Processed File
 Step tt3 - Process And Write to XML File
  Output File Option;
^nit/Stop Process*
     Process File
 Progress:
                                  Finished processing
                                   Formatted to: XML file
Upon successful processing, the output XML file is written to the following directory:

    C:\Documents and Settings\\My Documents\EMTS Conversion Tool (ECT)\Good_Files

If processing is not successful, the screen will display a message that includes, "Missing or incorrect value
for required element(s) in record(s):<...>, refer to columns marked."  Figure 11 depicts an example of this.
If the user had previously selected the  "Build  a  New File" option, a data grid will be visible  and the
appropriate cell(s) in this grid will change color to indicate where the error has occurred.
Figure 11: An Unsuccessfully Processed File

     Step it3 - Process And Write to XML File
      Output File Options
      But/Stop Processing
         FVocess File
 Status
Missing or incorrect value for required element (s) in record (s): 1,
Processing Data, Approw Records to be processed = 1
Finished processing Input Data. Records processed = 1.
                                         1 records were found with errors, these files can not be loaded to the server.
The ECT maintains a log file, which can be used to view additional descriptions of the errors if processing is
unsuccessful. Clicking the "Show Log" button at the top of the File Processing screen displays the log file in
the log viewer (see Figure 12). This viewer includes buttons which allow the user to print the log and to
select another log file for viewing.  Clicking "Exit" closes the log viewer.

-------
EMTS Conversion Tool
Figure 12: Errors Reported in Log File
11 C:\Documents and Settings\wgo\My DocumentsVEMTS Conversion Tool (ECT)Wal_Log\... | _ || D || X |
NFO: {0} Finished Loading Input Data. 1 data records read.
INFO: {0}
INFO 'fl!
INFO: {0} Input file: \GenerateAnneMcPhee2.xls <><><>
INFO: {0}
INFO: {0} Missing: RIN Quantity
INFO: {0} -12/1 8/2009 3: 18: 48PM
NFO: {0} Missing data for required fields on record(s): 1 , Record will not be processed.
NFO: {0} Missing or incorrect value for required element(s) in record(s): 1 ,
INFIT In!
NFO: {0} Processing Data. Approx Records to be processed = 1
rinished processing Input Data. Records processed = 1.
1 records were found with errors, these files can not be loaded to the server.
INFO !ri! 	 	 	 	 - 	 - 	 	 - —.— 	 -

Print Log OpenAnot

A.

=
V
ill"
ler Log Exit

If a selected input file is not successfully processed, the ECT will still attempt to create an output XML file.
This file will be located in the following directory:

    C:\Documents and Settings\\My Documents\EMTS Conversion Tool (ECT)\Error_Files

Only the data rows with no errors will be written to the output file. All data rows that contain errors will
not be converted to XML.
Processing Multiple Files

If multiple files were selected for processing and the user wishes to process the next file in the batch, the
user should click "Next file," as indicated in Figure 13. Before processing the next file, the user will have to
re-enter the Organization ID and the Submittal Creation Date. Note that the "Next file" button will be
present for all files except the last one in the batch.

-------
EMTS Conversion Tool
Figure 13: Processing Next File
  Step tt3 - Process And Write to XML File
Output File Options
Process File
Blit/Stop Processing
(^Next 'ile
)
                                          Status
                                          Recessing Data, ^pprow Records to be processed = 1
                                          Finished processing Input Data. Records processed =1.1 records were successfully processed.
                                          Formatted to: XML file.
                                          Compressed to: Ho compressions were selected.
  Progress:
                                                                                                                     10

-------