Quick Guide: Running the Conversion Tool (Advanced Version)


Prior to starting the ECT, the user should place the 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


  t3 Back -              Lj Search [l   Folders
Address •_} C:\Documents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)



   File and Folder Tasks   ^
    \f\ My Documents

       My Computer

       My Network Places
                                                                                           /'
                                                                                            Go
P
                                    Error File',;
                                    I  Input_Files
            9
                                                                    Good Files
                                                                   Val_Log
Running the ECT


This guide describes how to run the ECT in its advanced mode. Once the ECT is installed, it can be opened

from the Start Menu. Upon starting the ECT, the start-up page displayed in Figure 2 appears.
v>EPA
          United Stales
          Environmental Protection
          Agency
                                          EPA-420-B-10-009
                                           February 2010

-------
Figure 2: Start-up Page of the ECT
 ffl 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     |  This version allows the flexibility that the Basic version doesn't.
        Basic Version
                      This is a basic version which requires:
             Go     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.

        [— 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
The user should click on the "Go" button under "Advanced Version." Prior to doing this, the user can click
on 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.


Selecting Files
Once the "Go" button for the Advanced Version is selected, the File Selection Screen appears (see Figure 3).

-------
Figure 3: File Selection Screen
 m EMTS Conversion Tool (ECT)
   File   Help  File List View   Show Front Page   Show Log  WinZip
   wgo: Welcome to Version 1.0.0.8.3. This application will format an Excel or delimited file for conversion to XML.
   Step fl I - If using a delimited file specify 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
Tab
Other:
         Comma
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.

- LJ EMTS Conversion To( A
1 1 Error_Files
1 E+l-Pl Good_Files
: ffl f^t lnput_Files
CJ Val_Log
i $ Cl FinePrint files
; CJ FormalXML
; CJ My Google Gadgets
; CJ My Music
: f 1 My Pictures
i CJ My Videos
i ffl CJ My Virtual Machines
• Cj New Folder
+ CJ PDF files
t LJ TotaWalidatorTool
+ _J NetHood
| CJ PrintHood
: CJ PrivaclE
i LJ Recent
: CJ SendTo
!+• CJ Spark
+ LJ Start Menu
: Cj Templates v
< >
Name | Size
S BuyDenyExample1_Feb3.xls 37,888
1 BuyExample1_Feb3.xls 37,888
Hi GenerateAdvancedExample1_Feb3.xls 61,952
Ml GenerateExample1_Feb3.xls 61,952
'm RetireExample1_Feb3.xls 44,544
1 SellCancelExample1_Feb3.xls 37,376
m SellExample1_Feb3.xls 37,376
s| SeparateExample1_Feb3.xls 43,008
Modified I Attribute
2/3/201 02:07: 47PM A
2/3/201 01:2B:37PM A
2/3/2010 2:1 1:08 PM A
2/3/201 01:54:20PM A
2/3/201 01:27:28PM A
2/3/2010 2:02:24 PM A
2/3/201 01:57:57PM A
2/3/201 01:26:35PM A

Process
Existing
File(s]
Or
Build a New
File
Or
Exit

i-iies
Input File(s) Folder: | C:\Documents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT]\lnput_Files
*Qood File(s) Folder: |C:\Docurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)\Good_Files
"Bad File(s) Folder : | C:\Docurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT]\Error_Files
Log File(s) Folder : |C:\Documents 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.
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 input files, the user should press "Ctrl" on
the keyboard and click on all of the files to be processed.  The input files must be either Excel files or text
files. If a text file is used as  input, the delimiter should be specified (see "Step #1" in Figure 3 above).  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 9).

Processing Files

The File Processing Screen is depicted in Figure 4.  The user should first fill in the "Enter Company Info"
portion of the File  Processing Screen, indicated by "Step #3" in the figure. The company information
required includes:

    •   User Login (is 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 should be visible with the column headers and data
from the input file displayed.
Figure 4: File Processing Screen
 Process  Rows  Columns  Help Show Log  WinZip

 Step tt3 - Enter Company Info      	
: * User Login: |§S       * Submittal Creation Date: [2010-02-01   Submission Comment:
 • Organisation ID: |
 Step 114 - Select column to upload
; Header Infomation
'Application User ID: j
Transmission ID: TED
 Add New Empty Row
             Copy Row(s) to New Row|s)   Delete Row(s)  Set Columns to Recommended Defaults
                                                         Imporl/Enport Company Column Names
 Select the appropriate columns from the drop-downs below. Refer to the legend on the middle right as to whether fields are required o
 NOT on the input File.
                                                                  n be sorted by clicking on the column header. Changes made will be reflected in She new output file and








6




170




12/1/2008



Code
-10



Volume
850000




10



Value
1.0



Quantity
650000



Identifies



Generate Of anization
Identifies
43247

Required Fields
•FuelCode Fuel Code jj
1 BatchVolume Batch Volume f

' ProcessCode Process Code T
* RINQuantity RIN Quantity f
* Generate Facility Identifier Gene ate Facility Identifier T
* Batch NumberTent Batch Number Tent _rj
DenaturantVolume [Denaturan, Volume jrj
EquivalenceValue [Equivalence Value T
Generate Organization identifier [Generate Organisation Identifier T
TransactionDetailCommentTent [Transaction Detail Comment Tent » |
ImportFacilityldentifier Import Facility Identifier ^J
All Remaining Fields ^J
Oo r-roduct Fields - Up to 3 sets
CoRoductCodel CoProduct Code 1 -r \
Co Product Detail CommentTeKtl CoProduct Detail Comment Tent 1 ^
CoRoductCode2 CoProduct Code 2 _»J
Co Roduct Detail Comment TeHt2 CoProduct Detail Comment Tent2 _^J
CoRoductCodeS CoProduct Code 3 T I
Co Roduct Detail CommentTeKtS CoProduct Detail Comment Text 3 _»J

Feedstock Fields - Minimum = 1 Eel
1 Feedstock Codel
* Renev-iableBiomasslndicatarl
' Feedstock Volume!
* Feedstock Measure Codel
* FeedstockDetailCommentTentl
' Feedstock Code2
" Renewable Biomass Indicator2
" FeedstockVolume2
* Feedstock Measure Code2
1 FeedstockDetailCommentTe«t2
* Feedstock CodeS
' Renewable Biomass Indicators
* FeedstockVolumeS
" Feedstock Measure CodeS
* FeedstockDetailCommentTextS
Step tt5 - Process And Write to XML File —
Output File Options Ewit/Stop Recessing
Rocess File
Progress:
ta records read.
Maximum - 3 seis
Feedstock Code 1
Renewable Biomass Indicator 1
Feedstock Volume 1
Feedstock Measure Code 1
Feedstock Detail Comment Tent 1
Feedstock Code 2
Renewable Biomass Indicator 2
Feedstock Volume 2
Feedstock Measure Code 2
Feedstock Detail Comment Tent 2
Feedstock Code 3
Renewable Biomass Indicator 3
Feedstock Volume 3
Feedstock Measure Code 3
Feedstock Detail Comment Tent 3


3
d
d
d
d
d
Tl
a
d
d
d
a
d
d
d


•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••ill

-------
The File Processing Screen includes a set of drop-down menus under the data grid. These menus include as
options all of the column headers in the input file.  For each field, the drop-down menu should be used to
specify the column header in the input file that is associated with the field name that will be written to the
XML file.  Figure 5 shows an example  of this; the user has selected "RIN Amount" in the drop-down to
match the "RIN Amount" header shown  in the data grid.
Figure 5: Selecting Column Headers
                  Process Code

                 200
                             Production
                             Date
Fuel Category
Code
Batch
Volume
Denature nt
Volume
 Required Fields
• Fuel Code
* BatchVolume
* FuelCategoryCode

" RoductionDate
* RocessCode
" RINQuantity
1 GenerateFacilityldentifier
" BatchNumberTeKt
 Optional Fields
 Denaturant Volume
                            Fuel Code
                            iatch Volume
                            Fuel Category Code
                            Production Date
                            Batch Number Tent
                            | Denaturant Volume
          CoRoduct&elds - Up to 3 sets ~~
           CoRjxJuctCodel

         jS&> Roduct Volume 1

           CoRoductMeasureCodel

           CoRoductDetailCommentTeKt!

           CoRoductCode2

           Co Roduct Volume2

           Co Roduct Measure Code2

           Co Roduct Detail CommentTeHt2

           Co Roduct Code3

           CoRoductVolumeS
                                                                             CoProduct Code 1
                                                                                                 T |
                                                                             Co Product Volume 1
                                                                                                 -r |
                                                                             CoProduct Measure Code 1
                                                                                                 T |
                                                                             CoProduct Detail Comment Tent 1
                                                                             CoProduct Code 2
                                                                                                 •* \
                                                                             CoProduct Volume 2
                                                                                                 T t
                                                                             CoProduct Measure Code 2
                                                                             Co Product Detail Comment Tent 2
                                                                             CoProduct CodeS
                                                                                                 * |
                                                                            i Co Product Volumes
A column header only needs to be selected if there is a non-null value in the  data grid - in the example
above, a column does not need to be  selected for "DenaturantVolume." Note that upon installation,  the
drop-down menus will have the default EPA names but can be changed as needed by the user.  The user  can
also set any names  he has  changed back to the defaults be clicking on "Set  Columns to Recommended
Defaults"  (see Figure 6).  Note also that there is no need to select the column headers if the default EPA
column names are used in the input files and the drop-down menus.
Figure 6: Set Columns to Recommended Defaults
 Step 84 - Select column to upload
 Add New Empty Row   Copy Row(s) to New Rowls)    Delete Row(^^  Set Columns to Recommended Defaults   J Import/Ewport Company Column Names

 Select the appropriate columns from the drop-downs below. Refer to the legend on the'WiiJUIe liyht mt lu YvliyL^ei litJjs are required or not. Rows can be sorted by clicking
 NOT on the input file.

-------
 EMTS Transaction Instructions
Once the column headers are selected and the user information is complete, the user can adjust the ECT
default settings.  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 7).
Figure 7:  "Output File Options" button
  Step tt5 - Process And Write to XML File

  MDutput File Options 1) Exit/Stop Recessing
Status
     Rocess File
  Progress:
                              Finished Loading Input Data.3 data records read.
 I
On the Formatted Output File Options screen (see Figure 8), 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
file name. In the example in Figure 8, if a Generate file were being processed and the user wanted to name
the file "01-26-2010," 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 8:  Formatted Output File Options Screen
    Formatted Output File Options
                 File type XML is the ONLY option required for submission/transmission
         File Type(s)
        W  XML - This is the ONLY option required for submission/transmission
            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
        f~"  Compress only the XML File for Node Submission/Transmission
   Compress only the XML File for Node Submission/Transmission
'-""  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_YYYY-MM-DD-HH-MM-SS where Tt is the Transaction Type.
         Overridden File Name:  101-26-2010
             Do NOT include the Folder (Path) name. The "Good Files" folder is assumed.
          Other File Option(s)
        l~ Display Processed File
                                                 (Text or Excel file only)
                                                              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 9). 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.
Figure 9:  "Next file" Button
  Step ttb - Process And Write to XML File
    Output File Options
                                      Status
                                     Finished Loading Input Data.5 data records read.
  Piogiess:

-------
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 10).
Figure 10: Selecting the Transaction Type
                          Choose the Transaction Type
ESS
                        Select the transaction type from the drop-down pick list below for file:
                                     Select Transaction Type:
                                     Enter      Skio this file
Once the user selects the transaction type and clicks "Enter," the File Processing Screen appears (Figure
11).

-------
Figure 11:  File Processing Screen
 ggj Gene rate Transaction  Processing file:
  Process  Rows  Columns  Help   Show Log   WinZip
  Step H3 Entei Company Info       	
 "User Login: [SB       - Submitial Creation Dste: |zQ 10-02-01   Submissi
 " Organisation ID: |
 Step 84  Select column to upload
                                                        Headei Infomation
                                                       "^plication User ID: [ep7~
                                                              ID: TED
                               fV Apply User Login Va
  Add New Empty Row  Copy Rowls) to New RowlsJ
                                Delete Row(s)
                                            Show Legends
  Select the appropriate columns fro
  NOT on the input file.
                     e drop-downs below. Refer to the legend on the middle right as to whether fields are required or not. Rows can be sorted by (.licking on the column header. Changes made will be reflected in the new output file and
   Required Fields
  " Fuel Code
  - BatchVolume
  1 Fuel Category Code
  * Production Date
  " Process Code
  * RINQuantity
  * GenerateFacilityldentifier
  1 Batch Number Text
   Optional Fields	
  DenaturantVolume
  EquivalenceValue
  Gen erat e Or ga nizat ion Ide ntif ie r
  Transaction Detail CommentTem
  ImportFacilityldentiher
Co Product Fields - Up to 3 sets
 Co Product Code 1
 Co Product Detail Comment Text 1
 Co Product Code2
 Co FVoduct Detail Comment Text2
 Co Product Code3
 Co Product Detail CommentTextS
Feedstock Fields - Minimum = 1 set. Maximum = 3 sets
* Feedstock Code 1
* Renewable Biomass Indicator 1
• Feedstock Volume 1
" Feedstock Measure Codel
" FeedstockDetailCommentTewtl
* Feedstock Code2
" Renewable Biornass Indicator2
                                           • Feedstock Measure Code2
                                           * Feedstock Detail Comment Tenr.2
                                           ' Feedstock Code3
                                           • Renewable Biomass Indicators
                                           " FeedstockVo!ume3
                                           ' Feedstock Measure CodeS
                                           * Feedstock Detail Comment Te«t3
  Step tt5 - Piocess And Write to XML File
   Output File Options  Exit/Stop Processing
  Progress:
                                s set to Recommended Defaults
Note that in this version of the File Processing Screen, there are no drop-down menus with column headers,
since the default column headers will always be used when the user elects to build a new file.
The  user should first fill in the "Enter  Company Info"  portion of File Processing Screen, indicated by "Step
#3" in Figure 11.  The company information required includes:
     •   User Login (is 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 should be visible.  The user should fill in data.  Once
the data is entered and the user information is complete, the user should click the "Process File" button.

-------
 EMTS Transaction Instructions
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 12).
Figure 12: A Successfully Processed File
 Step tt5 - Process And Write to XML File
  Output File Options
&it/Stop Processj
     Rocess File
Recessing Data, Approx Records to be processed = 2
Finished processing Input Data. Records processed = 2.2 records were successfully processed.
Formatted to: XML file.
Compressed to: Ho compressions were selected.
 Progress:
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." In addition, the appropriate cell(s) in
the data grid will change color to indicate where the error has occurred (see Figure 13).
                                                                                                     10

-------
Figure 13:  Indication of Error in File

#
1


"Fuel
Code
4


* Pro cess
Code
200


"Production
Date
2009-12-02


"Fuel Category
Code
40 <


"Batch
Volume
^ i -^
\

Dendiurant
Volume



Equivalence
Value
1.0


"RIN
Amoi
1900

Required Fields
* Fuel Code
1 BatchVolume
Fuel Category Code
* Reduction Dale
* RocessCode
1 RINQuantity
* Generate Facility Identifier
* Batch NumberTeKt
Fuel Code T |
Batch Volume T \
Fuel Category Code f |
Production Date T |
Process Code T |
R IN Amount _^J
Generate Facility identifier ^ [
Batch Number Tent ^J

Denaturant Volume
EquivalenceValue
Generate Organisation Identifier
Transaction Detail CommenlTent
Import Facility Identifier
^11 Remaining Fields
Denaturant Volume -f |
Equivalence Value ^ |
Generate Organisation Identifier T |
Transaction Detail Comment Tent T |
Import Facility Identifier ^ |
^

CoRoduct Fields - Up toB sets
CoRoduclCodel V
CoRoductVolumel \
Co Product Measure Code 1
1
Co Roduct Detail Comment"fent 1
CoRoductCode2 \
Co Roduct Volume2 \
Co Roduct Measure Code2 1
Co Roduct Detail CommentTeKtfe
CoRoductCodeS \
CoRoductVolumeS 1
Co Roduct Measure CodeS i
CoRoductDetailCommentTeKtS \

Co Roduct Code 1 •»•
CoProductVolumel -r J
CoProduct Measure Code 1 jrj
CoProduct Detail Comment Tent 1 T |
Co Product Code 2 •»• |
CoProduct Volumes j-rj
CoProduct Measure Code 2 w \
Co Product Detail Comment Tent 2 T |
Co Roduct CodeS T |
Co Roduct Volumes T |
Co Roduct Measure Code3 T |
Co Roduct Detail Comment Tent 3 T |
\
 Step 115  Process And Write to XML File
  Output File Uptions
F_HJt/Stop Recessing
     Rocess File
Status
Missing or incorrect value for required element (s) in record(s): XTefer to columns
Recessing Data, ^pprow 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  14).  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.
                                                                                                              11

-------
Figure 14: Errors Reported in Log File
 H C:\Docurnents and Settings\wgo\My Documents\EMTS Conversion Tool (ECT)\Val_Log\EmtsConversion.
 Finished processing Input Data. Records processed = 1.1 records were successfully processed.
 Formatted to: XML file.
 Compressed to: No compressions were selected.
 IN FIT     tn\
 iiiru.     V-fi	
                Input file: \Generate2.xls <><><>
INFO:    {0}
INFO:    {0}
INFO:    {0}
INFO:    {0}
INFO:    {0}
INFO:    {0}
INFO:    {0}
INFO:    {0}
               Missing: Batch Volume
                -12/11/2009 4:50:15PM
               Missing data for required fields on record(s): 1, Record will not be processed.
               Missing or incorrect value for required elernent(s) in record(s): 1, refer to columns marked

               Processing Data. Approx 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.
                                                                                                      >
                                                                  Print Log
                                                                             Open Another Log
4
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 the user chose to process multiple existing files and wishes to process the next file in the batch, the user
should click "Next file," as indicated in Figure 15.  Before processing the next file, the user will have to re-
enter the Organization ID and the Submittal Creation Date.  Also, if the input files do not use the same
column headers as the default Excel templates created by EPA, the user may have to re-specify the column
headers in the dropdown menus. Note that the "Next file" button will be present for all files except the last
one in the batch.
                                                                                                         12

-------
Figure 15:  Processing Next File
  Step 85 - Process And Write to XML File
    Output File Options
But/Stop Processing
       Rocess File
 Status
Recessing Data, .Approw Records to be processed = 1
Finished processing Input Data. Records processed =1.1 records were successfully processed.
 Formatted to: XML file.
 Compressed to: No compressions were selected.
  Progress:
                                                                                                                          13

-------