Transport Partnership
U.S. ENVIRONMENTAL PROTECTION AGENCY
Guide to Exporting/Importing
Data into SmartWay Tool
&EPA
United States
Environmental Protection
Agency
-------
Transport Partnership
U.S. ENVIRONMENTAL PROTECTION AGENCY
Guide to Exporting/Importing
Data into SmartWay Tool
Transportation and Climate Division
Office of Transportation and Air Quality
U.S. Environmental Protection Agency
United States Office ofTransportation and Air Quality
Environmental Protection EPA-420-B-11-040
Agency
November 2011
-------
Guide to Exporting/Importing Data into SmartWay Tools
The SmartWay 2.0 Shipper, Logistics, and Multi-modal Tools have a Data Import function. The purpose
of this Data Import function is to allow partners utilizing large numbers of carriers to extract and
combine data from their in-house computer systems, and to then import this data directly into the tool,
alleviating the need to key enter this carrier data.
The carrier data that you collect and combine needs to be stored in a Comma Separated Values (CSV)
file. In order for the CSV file to be properly read, it must be saved with the proper format, as specified
below.
Although CSV files can be viewed and updated using a variety of different PC applications, SmartWay
recommends that you use Microsoft Excel. Excel has standard support for CSV files, in that it can easily
read and write this type of file. Note that you MUST always save your file as a CSV file. If you accidently
save your file as an Excel workbook, you can easily change the format back to a CSV file by using the
"Save As" function in Excel.
The recommended steps in preparing your Data Import CSV file are as follows. Please note that EPA
does not have the resources or technical expertise to assist you in creating and importing this file. You
should rely on your own in-house IT/MIS computer staff.
Step 1: Run the tool and perform the first three steps listed on the Home screen. These are
a) Enter Company and Contact Information
b) Define Your Companies/Fleets
c) Download Latest Carrier Data File
Once you have performed these three steps, you will be ready to proceed with importing your carrier
data into the tool. After performing these first three steps, select a shipper partner or carrier fleet from
the Home Screen. This will display the Data Entry forms for the partner or fleet you selected. An
example selection screen is shown below from the Logistics Tool.
-------
SmartWay Logistics Tool: Fleet Three
Activity Data Emissions Summary
Carrier Performance
You may enter your carrier data directly into the tool using the Manual Entry Method, or you may upload a pre-populated file containing data on multiple carriers using the Data
Import Method. Since multiple non-SrnartWay carriers are entered on one line, be sure to indicate the number of non-SrnartWay carriers you use. When you have completed
this screen, proceed to the "Activity Data" screen by selecting the tab above or clicking NEXT at the bottom of the screen.
I Option #1:
Data Import Method:
Export Master File
Export Selected Carriers
Import Carriers from File
Detailed Instiuctiuns fur Data
Export/Import (pdf)
REMEMBER TO ADD YOUR NON-SMARTWAY CARRIER INFORMATION BY SELECTING THE "NON-SMARTWAY" OPTION FROM THE AVAILABLE CARRIERS LIST
_Ll Carrier Mode Bin Category J\ Available Carriers
I Option #2:
-^J Manual Entry Method:
[selsct a Mode f\ |
Add Selected Carrier to List Below
_ MCN: \
Apply Filter | Remove Filter |
F Select All for Deletion
Delete Selected Carriers
— Selected Carriers —
^"H" r >Bi" SmartWay
Mode Category Carriers
X Delete Checked Rows
Step 2: Select your carriers
When the tool imports your CSV file, it matches your carrier information with emission factors from
EPA's SmartWay Database. Carrier data is matched together in one of two ways: (1) by an internal
carrier Identifier, or (2) the carrier's name, mode, and bin category. The CSV file must also be in a
specific format. In order to satisfy both of these requirements, EPA recommends two approaches:
Option A:
On the Select Carriers screen, use the 'Export Master File' button. This will write all of the
current SmartWay Carriers into a CSV file with the correct format. You can then open this CSV
file in Excel and delete the carriers you don't use, leaving just the carriers you do use. If you
want to specify a carrier multiple times, either for a different pathway or different internal code
(applicable for the Shipper Tool), you can simply copy and paste that carrier row onto a new row
within Excel. The required steps for this Option are listed below.
1. Select the "Export Selected Carriers" button.
2. A "Save As" screen will pop-up.
3. Save document as "XXX.csv" file, where "XXX" is a name of your choosing, in any folder on
your computer.
4. Open this CSV file and delete the SmartWay carriers that you did not use during the
reporting year.
-------
5. Save this modified CSV file to your computer.
6. Go to Step 3 (Populate the CSV file with carrier data from your in-house computer systems)
below.
Option B:
On the Select Carriers screen, find and select your carriers using the tool user interface,
including the Carrier Mode drop-down listbox, the Bin Category drop-down listbox, and the
Available Carriers drop-down listbox. As you find each of your carriers, use the 'Add Selected
Carrier to the List Below' button. When you use this button, the carrier selected in the Available
Carriers drop-down listbox will be added to the list of Selected Carriers at the bottom of the
screen. Once you have selected your carriers, use the 'Export Selected Carriers' button. This
will write out just the carriers you selected into a CSV file with the proper format. The required
steps for this Option are listed below.
1. Select the "Export Selected Carriers" button.
2. A "Save As" screen will pop-up.
3. Save document as "XXX.csv" file, where "XXX" is a name of your choosing, in any folder on
your computer.
4. Go to Step 3 (Populate the CSV file with carrier data from your in-house computer systems)
below.
Although EPA does not recommend it, there is also a third option, which is to create your CSV from
scratch using one of your in-house systems. If you do use this approach, you should select a few carriers
on the Select Carriers screen and export them so you have a sample CSV file with the correct format.
This will aid your IT staff in producing a CSV file with the correct format.
EPA recommends that you save a backup copy of the CSV file at this point.
Step 3: Populate the CSV file with carrier data from your in-house computer systems
1. Open up the CSV file you created in the previous step. At the top of the file are important
warnings and descriptions of the data that needs to be entered in each column. These warnings
and descriptions can be deleted from your CSV, however do not change the header row which
begins with the label "Carrier ID". A list of the CSV fields can also be found at the end of this
document, including a list of valid values.
2. Populate the CSV file with carrier data from your in-house spreadsheet.
Step 4: Validate and Import your CSV file into the tool
You can import the data in your CSV file by using the 'Import Carriers from File' button on the Select
Carriers screen. This function will perform an initial validation of the data within your CSV file. After the
validation is complete, the tool will display a list of any errors and warnings that were found while the
tool was validating your CSV file., If you have any warnings or errors, the tool will ask you if you would
like to continue with the data import process. The steps required for file validation are listed below.
-------
1. Select the "Import Carriers from File" button.
2. An "Open File" box will pop-up.
a. For Option A, select "CarrierDataSelected.csv" from the appropriate folder;
b. For Option B, select "CarrierDataMaster.csv" from the appropriate folder.
3. Select Open.
4. A "SmartWay File Import" box will pop-up, asking "Do you wish to continue?"
5. Select Yes.
6. If your file contains errors or warnings, a "SmartWay File Import" box will pop-up identifying
what they are. If you have any warnings or errors, you can stop the import process and
correct these errors/warnings in your CSV file and try again. You may also choose to note the
warnings/errors and correct them within the tool. If your file did not contain any errors, a
"SmartWay File Import" box will pop-up, saying "Congratulations! Your data meets the
minimum standards required for import."
7. Select OK to continue importing your CSV file
Note: Any carrier records in your CSV file that had an error will not be imported into the tool. Fields
with warnings will be imported, but the field receiving the warning will either be ignored or replaced
with a zero. Zeros will need to be corrected in the tool if they are imported. For example, Total Miles
must be an integer value greater than zero and less than 999,999,999,999. If you enter 41457.3 as the
total miles for one of your carriers, this Total Miles field will receive a warning because it is not an
integer. The import process will not accept this data, and instead insert a zero for the Total Miles field
for this carrier.
Step 5: Adding or replacing imported carrier data
The tool Data Import function was designed so that you can also add additional carrier data at a later
date. To add additional carrier data, you should use the Export Selected Carriers data so that all of the
carrier data in the tool is exported to a CSV file. You should then add your additional carrier data to this
exported CSV file.
Once you have imported your CSV file into the tool, you may augment, correct, or modify any of the
carrier data using the tool user interface. If you do make any changes to your carrier data from within
the tool, you should export this data using the 'Export Selected Carriers' button on the Select Carriers
screen.
Note: When you perform a Data Import, all previously entered or imported carrier data in the tool is
deleted. If you need to merge carrier data from the tool with other carrier data, you can either key enter
the additional information into the tool, or enter it into an exported CSV file. If you chose the latter
approach, you should use the 'Export Selected Carriers' button to export that data, and then add or copy/
paste this additional carrier data into this newly exported CSV, then re-import the updated CSV back into
the tool.
-------
Note: When exporting and then re- importing your Selected Carriers, if you had also entered information
on the Activity Data screen prior to the export, , any values you may have typed into the Average Density
and Average Payload Percentage popups will be lost. The calculated values will still be retained - only
the calculator inputs themselves will be wiped out.
Step 6: Enter additional data into the tool
In addition to carrier-specific information, the tool will require you to enter additional information using
the tool user interface. This includes describing the Data Source for your Activity Data if you are
entering Emission Footprint information and the Data Source for your % SmartWay information (Shipper
tool only). You may also need to specify a % SmartWay option and a description of a Custom Factor if
you chose to use that option for your % SmartWay. Note that your Activity Data tab Data Source field is
deleted each time you perform a new Data Import.
After importing your carrier data, please select each tab that is enabled on the data entry screens. The
tool will prompt you when data is missing or in error. Alternatively, you may select the Validate
Screen/Fleet button at the bottom of each screen, which will allow you to see all of the errors at once.
If you are supplying EPA with either Shipper Strategies or Modal Shifts (Shipper Tool only) you use to
reduce your emissions footprint, you need to enter that information directly into the tool using the user
interface. This information cannot be supplied to EPA using the Data Import function.
-------
Glossary:
Bin Category - The operation type category used for ranking carriers (e.g., TL Dry Van, Dray, etc.).
Carriers are grouped by these categories to ensure an "apples to apples" comparison. The emissions
factors associated with truck carriers are the midpoint value for their bin, while factors associated with
rail carriers are partner-specific, based on publicly available information.
Emission Factors - Emissions factors have long been the fundamental tool in developing national,
regional, state, and local emissions inventories for air quality management decisions and in developing
emissions control strategies. More recently, emissions factors have been applied in determining site-
specific applicability and emissions limitations in operating permits by federal, state, local, and tribal
agencies, consultants, and industry.
EPA's SmartWay Database - EPA maintains a database containing key SmartWay partner information.
This database is used to create the SmartWay Carrier Data file.
MCN - This is a truck carrier's Motor Carrier Number (MCN) number. This is an optional field you can
use to visually determine if a SmartWay carrier is one of the carriers you use. This field is not currently
used to merge SmartWay data with your carrier data. If you created your CSV file using one of the two
Export buttons on the Select Carriers screen, you should not change these values.
SCAC - The Standard Carrier Alpha Code (SCAC) is a unique two-to-four-letter code used to identify
transportation companies. SCACs are assigned by the National Motor Freight Traffic Association, Inc.,
(NMFTA). If you cannot remember your SCAC(s), please contact NMFTA before proceeding. You can find
NMFTA contact information at http://www.nmfta.org/Pages/ContactUs.aspx.
-------
CSV File Fields:
Carrier ID - This is an internal identifier that can be used to identify your carriers. This number originates
from the SmartWay Database. A negative number identifies the carrier as a non-SmartWay carrier. If
you created your CSV file using one of the two Export buttons on the Select Carriers screen, you should
not change these values. If you are merging your carrier data with the SmartWay carrier data using the
carrier name, carrier mode, and carrier bin category, you do not need to specify this internal ID field.
Carrier Name -This is the name of the carrier. If you are using the internal Carrier ID field to merge your
data with the SmartWay data, you do not need to specify this field. If you are using this field to merge
the data together, the names must be an exact match, but the names are not case sensitive. If you
created your CSV file using one of the two Export buttons on the Select Carriers screen, you should not
change these values.
Mode - This is the mode of the carrier. This field is required and must be an exact match if you are using
carrier name, carrier mode, and bin category to merge the SmartWay data with your carrier data. Valid
values are "Truck", "Rail", "Multi-modal", and "Logistics". If you created your CSV file using one of the
two Export buttons on the Select Carriers screen, you should not change these values.
Bin Category - This is the SmartWay bin category for the carrier. For more information on Bin Categories,
please see the Glossary above. If you created your CSV file using one of the two Export buttons on the
Select Carriers screen, you should not change these values. Valid values for Truck carriers are TL/Dry
Van, LTL/Dry Van, Reefer, Flatbed, Tanker, Dray, and Package. The only valid value for Multi-modal
carriers is Truck/Rail. Rail carriers and Logistics carriers do not have bin categories.
SCAC - This is a truck carrier's SCAC code. This is an optional field you can use to visually determine if a
SmartWay carrier is one of the carriers you use. This field is not currently used to merge SmartWay data
with your carrier data. If you created your CSV file using one of the two Export buttons on the Select
Carriers screen, you should not change these values.
MCN - This is a truck carrier's MCN Motor Carrier Number. This is an optional field you can use to
visually determine if a SmartWay carrier is one of the carriers you use. This field is not currently used to
merge SmartWay data with your carrier data. If you created your CSV file using one of the two Export
buttons on the Select Carriers screen, you should not change these values.
# Non-SmartWay Carriers - This is a required field for non-SmartWay carriers. You can either enter the
number in the CSV file or enter it later on in the tool user interface. If you enter the number in the CSV
file, it must be an integer between 1 and 99,999. If you have selected more than one entry for "Non-
SmartWay Carrier" for a given mode, make sure to avoid double-counting these carriers. Also, only enter
carriers used during the current reporting year.
Business Unit - This is an optional identifier for Shippers who would like to display results for selected
internal business units. This field is alphanumeric and has a maximum length of 50 characters.
-------
ID Code -This Shipper Tool field can be used for internal company code descriptions of your carriers.
This field is alphanumeric and has a maximum length of 50 characters.
Inbound/Outbound/lnternal - This Shipper Tool field is optional to identify a carrier's pathway. The valid
values for this field are Inbound, Outbound, and Internal. If you specify this pathway information, it
needs to be an exact match, but is not case sensitive.
US/International - This Shipper Tool field is optional to identify the carrier's pathway. The valid values
for this field are US and International. When specified, this pathway information must be an exact
match, but it not case sensitive.
ISS1 - This Shipper Tool field is optional and allows you to further segment your operations by specifying
an Internal Shipper Segment associated with the carrier. This field is alphanumeric and has a maximum
length of 50 characters.
ISSS2 - This Shipper Tool field is optional and allows you to further segment your operations by
specifying a second Internal Shipper Segment associated with the carrier. This field is alphanumeric and
has a maximum length of 50 characters.
Data Availability - This is a required integer field whose valid values are 1 to 7. The Data Availability
Option entered/selected for a carrier identifies which carrier activity data you have available. The data
you enter in your CSV file for Ton Miles, Total Miles, Average Density, Average Load pet, and Average
Payload should be consistent with the carrier's Data Availability. If you do not specify a Data Availability
Option, the tool will insert a default value of 1 for the carrier entry.
The seven Data Availability Options are:
1 = (Preferred) I know both Ton-miles and Total Miles.
2 = I know Ton-miles and Average Payload.
3 = I know Ton-miles and have general information about number of loads and cargo density.
4 = I know Ton-miles only.
5 = I know Total Miles and Average Payload.
6 = I know Total Miles and have general information about number of loads and cargo density.
7 = I know Total Miles only.
Calc Metric - This is a required field. In general, there are three valid values, but some of these values
are only available for certain Mode and Data Availability combinations. The three valid values are
g/mile, g/ton-mile, and g/railcar-mile. This field specifies the method for calculating each carrier's
emission footprint. In making your selection, consider the most appropriate measure for the carrier's
operation type. In general, ton-miles are good for rail, multi-modal and logistics companies as well as
heavier truck loads (roughly above 10 tons), while g/mile is better for trucks with lighter payloads.
Railcar-miles are also an available option for the rail mode, although ton-miles are preferred. Also,
g/mile may be a more reliable metric for tracking TL operations, while g/ton-mile is probably more
-------
appropriate for LTL operations. The tool will default a valid Calc Metric if one is not supplied in the CSV
file.
Ton Miles - Depending on your Data Availability option, this field may be required or ignored. The Ton-
Miles field is an integer that must not exceed 999,999,999,999.
Total Miles - Depending on your Data Availability option, this field may be required or ignored. When
entered, this field will contain the total miles per year attributed to each truck and rail carrier. The tool
assumes that miles entered for truck carriers correspond to (full) truckload-equivalent miles. Rail-miles
correspond to truckload equivalent railcar miles. Please see the Shipper Tool Technical Documentation
for details regarding the calculation of truckload-equivalents for railcars. The Total Miles field is an
integer that must not exceed 999,999,999,999.
Average Density - Depending on your Data Availability Option, this field may be required or ignored.
This field is used to calculate ton-miles and/or total miles, accounting for shipper-specific freight
densities. Average Density is required for Data Availability Options 3 and 6. The Average Density field
must be a numeric field greater than 0.1 with up to one decimal place.
Average Load Pet - Depending on your Data Availability Option, this field may be required or ignored.
Use the Average Load Percent calculator to determine what fraction of your truck and rail carrier's cargo
space is used to ship your freight. Load percentage estimates can be weight-based, with full truckloads
(or railcar loads) corresponding to weighing out, or volume-based, with full loads corresponding to
cubing out. The Average Load Percent field is required for Data Availability Options 3 and 6. Enter the
load information as requested. The resulting fractions (shown on the Activity Data screen) are
multiplied directly by Total Miles to scale carrier mileage. The Average Load Pet field must be an integer
field between 1 and 100.
Average Payload - Depending on your Data Availability Option, this field may be required or ignored.
Enter payload (in short tons) representing the average cargo weight per loaded trip. The Average
Payload field is a numeric field that must be greater than 0.0001. The total number of digits, including
the decimal point, must not exceed 5 digits.
User-Defined Percentage (Shipper Tool only) - This optional numeric field must not exceed 6 digits,
including a decimal point. This field contains the fraction of carrier activity performed by each carrier,
based on the selected factor (e.g., % Spent, % Weight, etc.) This field is only needed in the Shipper Tool
when you are using a User-Defined weighting factor to determine your % SmartWay. If you are
providing Activity Data and will be using Ton-Miles, Total-Miles, or CO2 to derive your % SmartWay, you
do not need to supply this information.
------- |