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