Transport Partnership
U.S. ENVIRONMENTAL PROTECTION AGENCY
Logistics,Multi-modal,and Shipper
Partner2.o.l4 Tools: ^^^M^
Guide to Importing Carrier Data Using
the Outside Data Import Function
2O14 Data Year - United States Version
United States
Environmental Protection
Agency
-------
Transport Partnership
U.S. ENVIRONMENTAL PROTECTION AGENCY
Logistics Multi-modal,and Shipper
Partner 2.O.14 Tools:
Guide to Importing Carrier Data Using
the Outside Data Import Function
2O14 Data Year - United States Version
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-15-073a
July 2015
-------
Table of Contents
TABLE OF CONTENTS 3
OVERVIEW 4
HOW THE OUTSIDE DATA IMPORT FUNCTION WORKS 5
PREPARING YOUR CARRIER DATA TO IMPORT INTO YOUR TOOL 6
STEP 1: ENTER COMPANY LEVEL DATA 7
STEP 2: CREATE YOUR CSV FILE 7
Option 1: Create a Custom CSV File from Scratch 9
Option 2: Manually Build a CSV File 10
Option 3: Export a Master CSV File 13
STEP 3: POPULATE THE CSV FILE WITH CARRIER DATA FROM YOUR IN-HOUSE COMPUTER SYSTEMS 14
STEP 4: CARRIER MATCHING (OPTIONAL) 15
STEP4A! POPULATE THE CSV FILE WITH YOUR COMPANY-SPECIFIC CARRIER IDENTIFICATION INFORMATION 15
STEP 4s: UPLOAD YOUR CARRIER CSV FILE INTO THE PARTNER PORTAL 17
STEP4C: EXPORT YOUR MATCHED CARRIER FILE FROM THE PARTNER PORTAL 17
STEPS: IMPORT AND VALIDATE YOUR CSV FILE 17
STEPS: ENTER ADDITIONAL DATA INTO THE TOOL 22
APPENDIX 23
***SPECIAL CASES/CONSIDERATIONS*** 23
YOU WISH TO ADD MORE CARRIER DATA AT A LATER DATE 23
YOU HAVE MANUALLY ENTERED CARRIER DATA INTO THE TOOL AND THEN DECIDE TO IMPORT OUTSIDE DATA 23
YOU CHOOSE TO EXPORT THE DATA AS A CSV FILE AFTER YOU HAVE ENTERED ACTIVITY DATA IN THE "Average
Density" AND "Average Load Percentage" POP-UP CALCULATORS 23
GLOSSARY: 24
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 3
-------
Overview
The SmartWay Logistics, Multi-modal, and Shipper Tools have an Outside Data Import function.
This function reduces the need to manually enter carrier activity data by allowing you to create
a data file that contains your company's in-house carrier data in SmartWay's prescribed data
format.
In response to companies that have had difficulty matching the carrier information in their
databases with the data that carriers submit to SmartWay, a new carrier matching function has
been added this year that enables partners to identify which of their current carriers are
SmartWay Partners. This matching function can be found in the SmartWay Partner Portal.
THE OUTSIDE DATA IMPORT FUNCTION IS RIGHT FOR YOU IF:
you use a relatively large number of carriers (both SmartWay and/or non-
SmartWay) and you have an in-house data collection system that contains
carrier activity data necessary to populate your SmartWay Tool for
submission to EPA.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 4
-------
How the Outside Data Import Function Works
This function allows you to import data from your in-house data tracking systems directly into
your Tool.
To input your company's carrier data into the Tool, you will create a Comma-Separated Values
(CSV) file that meets certain basic SmartWay specifications, and merge that file with your in-
house data to produce a complete profile of your carriers' activity.
This CSV file will be uploaded into the Tool and will save you the time of manually entering data
to calculate your emissions footprint and/or percent SmartWay (% SmartWay).
In addition to your carriers' activity data, starting this year you may add company-specific
carrier identification information and/or carrier contact information into the CSV file described
in the previous section. You may now add up to 12 extra fields of carrier identification
information and 3 fields of carrier contact information to your file. The new fields include:
- Carrier name as it appears in your company's system/database
- Up to three Internal Carrier ID codes from your company's database system
-SCAC
-MCN
-DOT
- Street address
-City
- State / Province abbreviation
-ZIP/Postal code
- Phone number
- Contact's email address
- Contact's correspondence name
- Preferred language of correspondence
In future years this information will be available as additional match criteria in the SmartWay
Partner Match function accessible from the Partner Portal. These new fields must be formatted
in a specific way in order to work with the SmartWay tools and database; file preparation and
format is described in the next section.
SmartWay suggests that you use Microsoft Excel to develop and modify your CSV files. Excel
has standard support for CSV files, in that it can easily read and write this type of file.
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.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 5
-------
Preparing Your Carrier Data to Import into Your Tool
There are six steps to preparing your CSV file for import into the Tool.
Step 1: Enter your company level data
Step 2: Open your Logistics Business Unit/Shipper Company and proceed to the
Import/Export screens to create your CSV File
Step 3: Populate the CSV file with carrier data from your in-house computer systems
Step 4: Carrier matching (OPTIONAL)
Step 4a: Populate the CSV file with your company-specific carrier identification
information
Step 4b: Upload your Carrier CSV file into the Partner Portal
Step 4c. Export your matched carrier file from the Partner Portal
Step 5: Import and validate your CSV file
Step 6: Enter additional data into the Tool
Please note that due to the variability in Partner data systems, EPA does not have the
resources to assist you in creating and importing this file. You should rely on your own in-
house IT/MIS computer staff.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 6
-------
Step 1: Enter Company Level Data
Before you can import outside data, you must take these initial steps for completing your Tool:
1. Enter your company name and contact information.
2. Define your business unit(s)1.
3. Download the latest carrier data file.
4. When you have at least company defined, you can then import carrier data for that
business unit.
5. Double click on the business unit name on the Home screen.
6. On the Input Carriers tab, read the Instructions screen and check the box to indicate
that you understand these instructions.
Once you have performed these six steps, you will be ready to use the Outside Data Import
function.
Step 2: Create Your Carrier CSV File
There are three basic methods for creating your Carrier CSV file:
• Option 1 — Create a Carrier CSV File from Scratch: If you have a large number of
carriers (e.g., 100 or more) you may choose to create your CSV from scratch using your
in-house system. If you choose this approach, the best way to ensure compatibility
between your file and the Tool is to review the example Carrier CSV file. This will aid
your IT staff in producing a CSV file with the correct format.
• Option 2 — Manually Build a Carrier CSV File: This method is a good option if you have
a smaller number of carriers (e.g. 100 or fewer) that you can manually identify. In this
approach, you manually select carriers in the Tool and then export a CSV file (using the
"Export Master File" button on the "Export Carriers" tab) that can then be populated
with data from your in-house data system.
• Option 3 — Export a Master CSV File: This method can be used if you have a very large
number of SmartWay Carriers. With this approach, you generate a CSV file complete
1 Terminology varies with Tool: Logistics users define "business units", and Shippers define "companies".
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 7
-------
with all current SmartWay Carriers in the correct format. You can then open this CSV
file in Excel and delete any SmartWay Carriers you don't use, and enter data regarding
activity and other details related to how you use your carriers.
Once you have created a Carrier CSV file, you can use a new matching function to identify and
match which of your carriers are SmartWay carriers. In the first reporting year (2014) you can
match your carrier data with data SmartWay Carriers have submitted using identification fields
you have in your database, including the carrier's address, phone number, SCAC code, MCN
code, and/or DOT number.
In future years you can match your carrier data with carrier data in SmartWay by including your
own unique identification data in the Carrier CSV. For example, if you have a unique Carrier ID
for each of your carriers, include that in the Carrier CSV this year so that this information is
stored in the SmartWay database. Then in subsequent years, when you include your unique
identifier in the Carrier CSV file, you can use the Partner Portal to find your carriers for the
current reporting year. Your company-specific match data is not shared with other companies.
The steps to use the new carrier matching function are:
1. Create your Carrier CSV file
2. Import your Carrier CSV file into the Partner Portal
3. Export your matched carrier file from the Partner Portal
4. Import your matched carrier file into the Tool
5. Edit your carrier identification information (optional)
These steps are incorporated into the complete set of steps for the import function in the
"Preparing Your Carrier Data to Import into Your Tool" section below.
Step by step instructions for all three data import/export methods follow.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 8
-------
Option 1: Create a Carrier CSV File from Scratch
You may choose to create your Carrier CSV file from scratch using your in-house data system.
Provide these instructions to your technical support staff that manage your in-house system.
They should follow these general steps:
a) Review the example Carrier CSV file for the required format.
b) Carrier CSV files generated by a SmartWay tool contain several rows of explanatory text
at the top of the file. These rows are ignored when the file is being read by either the
SmartWay tool or the Carrier Matching function in the Partner Portal. The code that
processes these files looks for the value of 'Carrier ID' or 'id du transporteur' in the first
comma delimited field. The SmartWay code that reads this file ignores all of the rows in
the CSV file until it finds one of these values in the first field. These values indicate that
this is the column header row. Following the column header row are one or more carrier
data rows.
c) The first 24 fields are fixed. This data contains either SmartWay identification
information to match this data with the carrier performance metrics in the SmartWay
Carrier Data File as well as your activity data for each of your carriers.
d) Starting in the 25th field is the new Carrier Identification data you can optionally include
to match your carrier data from your in-house system with the current reporting year
carrier data in SmartWay. The data in these columns is not fixed but instead identified
by column header. The column header names are not case sensitive.
Once you have the Carrier CSV file in the correct format, you are now ready to proceed to Step
3 ("Populate the CSV file with Carrier Data from Your In-house Computer Systems").
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 9
-------
Option 2: Manually Build a CSV File
1. Select the "Manual Input SmartWay Carriers" tab.
Activity Data
OB SmartWay
Outside Data Import
Mitch My Carriers
Export Carriers
2. Select your SmartWay Carriers using the Tool's user interface filters (e.g., the Carrier
Name box, the Carrier Mode drop-down list box, the Ranking Category drop-down list
box, the SCAC, MCN and/or DOT box, and the Address/City/State/Zip boxes). An
example selection screen is shown below for the Logistics Tool.
Filter
Carrier Name
Carrier Mode
m
2\
Available SmartWay Carriers: 3,018
AEP River Operations: Captive
AEP River Operations: Commercial
Ingram Barge Company
* 1 Solutions Inc
1-30Q-PACK-RATLLC: 3PL
3 Rivers Logistics, Inc.
3PD Inc
Activity Data
lutside Dan Import MMch My C
egory _H| SCAC jj MSC 2\
«.'.:, SmartWay Data Display
arricrs Export Carriers 1 Manual Input Non-5martWay
Addressl 1
Qty 1
_lJ Postal Code 1
^ Remove Filter ,
IX Carrier Mode
Barge
Barge
Barge
Logistics
Logistics
Logistics
Logistics
to List Below
Ranking J?'
Category SCAC MSC ^P Address >»
16 150 Main Cirde Drive Chesterfield ^
16 150 Main Cirde Drive Chesterfield ~
•MOO Harding Pike Nashville
NOSI 22077 mound rd Warren
OPRT 11640 North Park Drive Wake Forest
TVQI 60 Doughboy Road Gillett
THPW 63 14 Fly Rd East Syracuse "
f
3. As you find each of your carriers, click the 'Add Selected Carriers to List Below' button.
When you use this button, the carrier selected in the upper list box will be added to the
list in the "Selected SmartWay Carriers" box at the bottom of the screen.
Add Selected Carriers to List Below
(Press the CTRL button to select multiple Items)
Selected SmartWay Carriers: 57
Carrier Mode Category
ASR Global Logistics
A.M. Transport Services, Inc.
AAA Cooper Transportation
Abbyland Trucking, Inc.
ABT Freight Lines Inc.
Ace Forwarding, Inc
Ace Hardtt'are Corporation
Ace Logistics, Inc. : Ace Logistics, Inc.
Acme Truck Line, Inc.
Logistics
Logistics
Truck
Truck
Truck
Truck
Trud<
Logistics
Truck
LTL/Dry Van
Refrigerated
Refrigerated
Mixed
LTL/Dry Van
Flatbed
AMLQ
AMNO
AACT
ABYL
ABTL
AFWP,AFWO
ACHW
ACB
ATEQ
MC 3 16077
MC224875
MC226H5
MC713583
MC 307158
MC 186924
MC41M79
MC 120675
DOT2222961
DOT092261
DOT378067
DOT20 28302
DOT659111
DOT76209
DOTO 52767
X Delete Selected Carriers
Screen
Demo
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 10
-------
4. Select the "Manual Input-Non SmartWay Carriers" tab.
Input Carriers
Instructions
Activity Data
°/a SmartWay
Data Display
Outside Data Import
Match My Carriers
Export Carriers
Manual Input SmartWay
Carriers
Enter your non-SmartWay earner information on this screen. You can reference the complete fist of SmartWay earners on the previous screen in order to
determine if particular carriers are currently Partnership members, and return to this screen as needed.
Required Information Regarding Your Non-SmartWay Carriers
Total Number of Non-SmartWay Carriers for this Business Unit: I
Please check this box to indicate that all Non-SmartWay Carriers have been added for this business unit
5. Enter the total number of non-SmartWay carriers used by your company in the
reporting year, and specify their modes (truck, logistics, rail, barge, air, and/or multi-
modal) by selecting the appropriate non-SmartWay carrier(s) from the list and clicking
the "Add Selected Carriers to List Below" button.
Activity Data
% SmartWay
Data Display
Outside Data Import
Hatch My Carrie:
Export Carriers
Manual Input SmartWay
Enter your non-SmartWay carrier information on this screen. You can reference the complete list of SmartWay carriers on the previous screen in order to
determine if particular carriers are currently Partnership members, and return to this screen as needed.
Required Information Regarding Your Non-SmartWay Carriers
Total Number of Non-SmartWay Carriers for this Business Unit: H21
Please check this box to indicate that all Non-SmartWay Carriers have been added for this business unit &
AVAILABLE NON-SMARTWAY CARRIER TYPES
Carrier Name
non-SmartWay Air Carrier - Long Haul
non-SmartWay Air Carrier - Short Haul
non-SmartWay Barge Carrier
non-SmartWay Logistics Carrier
non-SmartWay Multi-modal Carrier
non-SmartWay Truck Carrier
Add Selected Carrier Types to List Below
(Press the CTRL button to select multiple items)
SELECTED NON-SMARTWAY CARRIERS
Carrier Mode
non-SmartWay Air Carrier - Short Haul Air
non-SrnartWay Barge Carrier Barge
non-SmartWay Logistics Carrier Logistics
non-SmartWay Multi-modal Carrier Multi-modal
X Delete Selected Carrier Types
6. Once you have selected all of your SmartWay and non-SmartWay carriers, go to the
"Export Carriers" tab. S
Activity Data
% SmartWay
Instructions
Outside Data Import
Match My Carriers
Manual Input SmartWay
Carriers
la! Input Ncm-SmartWay
Ca Friers
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 11
-------
Note: Steps 7 and 8 are for Shipper Tool users only.
7. Select the "Optional Carrier Differentiation" tab.
8. Enter any distinguishing data you wish for each carrier selected, such as
inbound/outbound and domestic/international designations on this screen. Begin by
selecting a carrier in the "All Selected Carriers" box at the top of the screen. Then
highlight the carrier name in the lower box. At this time the optional carrier descriptor
cells will become active at the bottom of the screen. After entering the desired
information, select the "Apply Descriptive Data to Selected Carrier" button to save your
data. Repeat this process for as many of your selected carriers as needed.
INPUTS ON THIS SCREEN ARE OPTIONAL - NOT REQUIRED FOR EPA SUBMITTAL
All Selected Carriers: 7
Ranking
Carrier Mode Category
non-SmartWay Logistics Carrier
non-SmartWay Multi-modal Carrier
non-SrnartWay Truck Carrier
3 Rivers Logistics, Inc.
CONLIS Transportation Inc
National Delivery Systems, Inc.
Logistics
Multi-modal
Truck
Logistics TVQI
Truck TL/Dry Van CNUT
Truck LTL/Dry Van NDSN
MC376181
MC2047772 DOT305794
MC429253 DOT966390
Select Carrier for Descriptive Data
Carrier Rows for J.B. Hunt Transport, Inc.: Intermodal Division: 1
Carrier Name
Business Unit I IDCgde-
I...Inbgund/Oijtbciund I Dnmestic/Internatinnal | IntgfnajShipper Segment 1 1
Inbound/Outbound
Internal Shipper Segment 1
ID Code
? ||34321
? 1 1
|lnbound j*J ? | J23D
Domestic/International Internal Shipper Segment 2
1 Domestic
? If
Apply Descriptive Data to Selected Carrier
>< Delete Selected Carrier's Optional Information
Page 14
9. Select the "Export Selected Carriers" button.
Export Selected Carriers
Export Master File
Detailed Instructions for Data Export/Import (pdfi
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 12
-------
10. A "Save As" screen will pop-up.
11. Save document as "XXX.csv" file, where "XXX" is a name of your choosing, in any folder
on your computer for which you have read/write access.
12. Proceed to Step 3 below ("Populate the CSV file with Carrier Data from Your In-house
Computer Systems").
Option 3: Export a Master CSV File
1. Select the "Export Carriers" Tab.
Activity Data
Instructions
Outside Data Import
Hatch My Garners
2. Select the "Export Master File" button.
Export Status
ual Input SmartWay
Camei
Manual Input Non-5martWav
Carriers
Export Master File Detailed Instructions for Data EKport/Import f pdf)
3. A "Save As" screen will pop up.
4. Save document as "XXX.csv" file, where "XXX" is a name of your choosing, in any folder
on your computer for which you have read/write access.
Filename: CarrierDataMaster.csv
Save as type: C5V Files p.csv)
5. Open this CSV file in Microsoft Excel (recommended) or other software that supports
CSV files.
6. Delete any SmartWay Carriers that you did not use during the reporting year.
7. Note that the first six rows in the master file represent non-SmartWay carriers. If you
used any non-SmartWay carriers you must account for their combined mileage and/or
ton-mileage in the appropriate non-SmartWay designation.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 13
-------
8. Save this modified CSV file to your designated computer folder.
9. Proceed to Step 3 on page 12 ("Populate the CSV file with Carrier Data from Your In-
house Computer Systems").
Step 3: Populate the CSV file with Carrier Data from Your In-house Computer
Systems
1. Open the CSV file you created in the previous step.
2. At the top of the file are important warnings and descriptions of the data that need to
be entered in each column. These warnings and descriptions can be deleted from
your CSV; however, do not change the header row that begins with the label "Carrier
ID."
NOTE: A list of the CSV fields can also be found at the end of this document, including a
list of valid values.
EPA recommends that you save a backup copy of the CSV file at this point
and at periodic intervals with different file names in case you make a
mistake.
3. Populate the CSV file with carrier data from your in-house spreadsheet.
You will need to work with your company's IT department to develop the
best way to merge your in-house data with the CSV file's fields; EPA cannot
provide support on data merging procedures.
4. Save your completed CSV on your computer — again, make sure that you save the file
as a CSV file (with the .csv extension), or it will not load into the Tool.
At this point, if you do not wish to use the carrier matching function and include company-
specific carrier identification information, you should skip to Step 5: Otherwise you are now
ready to upload and import your final CSV file into the SmartWay Tool. Proceed to Step 4 to
include carrier matching information.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 14
-------
Step 4: Carrier Matching (OPTIONAL)
If you wish to take advantage of the carrier matching function in the Partner Portal, you will
continue populating the CSV file at this point before visiting the Partner Portal and running the
matching process.
Step 4a: Populate the CSV file with your company-specific carrier
identification information
1. Continue working with the CSV file you created in the previous step.
2. Starting in Column Y, you may populate up to 15 columns with your own company-
specific carrier identification information and carrier contact information. Start by
populating the header row (i.e. the row that includes the label "Carrier ID" and the
other activity data column headers) with one of the 15 allowable column headers for
carrier identification information. The allowable column headers and the expected
information for the columns are:
3.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Carrier Identification Information
Carrier name as it appears in your company's
system/database
An Internal Carrier ID code from your
company's database system
An Internal Carrier ID code from your
company's database system
An Internal Carrier ID code from your
company's database system
SCAC
MCN
DOT
Street address
City
State abbreviation
Postal code
Phone number
Column Header
MyCarrierName
MyCarrierlDl
MyCarrierlD2
MyCarrierlDS
MyCarrierSCAC
MyCarrierMCN
MyCarrierDOT
MyCarrierStreetAddress
MyCarrierCity
MyCarrierState
MyCarrierZipCode
MyCarrierPhoneN umber
1.
2.
Carrier Contact Information
Contact's email address
Contact's correspondence name
Column Header
MyCarrierEmailAddress
MyCarrierContactName
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 15
-------
3.
Carrier Contact Information
Preferred language of correspondence
Column Header
MyCarrierContact Language
You may include as many or as few of these 12 allowable identification information
columns and 3 contact information columns as you wish. The order of the columns in
your CSV file does not matter, however you must add them in a contiguous section (i.e.
you cannot have a blank column between populated columns), starting in the 25th field,
which is column Y if you are viewing in Excel. For example, if you wanted to include a
column for an internal carrier ID from your database, the carrier name as it appears in
your database/system, and the SCAC from your database, you would enter
"MyCarrierlDl" in the header row in the 25th field (Column Y in Excel), "MyCarrierName"
in the 26th field (Column Z in Excel), and "MyCarrierSCAC" in the 27th field (Column AA in
Excel).
NOTE: The column header(s) you add to the CSV file must match those listed above
EXACTLY. Your CSV file will be invalid if the header column names do not match.
The carrier contact information shown above can be used to send emails to your carriers
once these data have been uploaded into the Partner Portal (see Step 4b below). You
can use this information to send emails to your carrier reminding them to submit their
data to SmartWay for the current reporting year, or to encourage non-SmartWay
carriers to join the Partnership.
In order to send an email to one of your partners, you will need to supply the email
address of your contact with that carrier (i.e., you will need to add a column with header
MyCarrierEmailAddress to your CSV file). In addition to their email address, you can also
provide the contact's name which will then be inserted into the email automatically
(included in the column MyCarrierContactName). Emails to partners can be sent in
either English or French (in column MyCarrierContactLanguage).
4. For each column you have added starting in the 25th field (Column Y in Excel), populate
the carrier rows with your company-specific information. The values you enter are
validated to ensure they are 100 characters or less. In addition, if you include the
preferred language of correspondence (in a column labeled
MyCarrierContactLangauge), the value must be "English" "French" "Anglais" or
"Francais" (the value must match EXACTLY to one of these four values).
You will probably need to work with your company's MIS/IT department to
develop the procedures to extract your in-house data with this file format;
EPA cannot provide support on data extraction procedures.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 16
-------
5. Save your completed CSV on your computer — again, make sure that you save the file
as a CSV file (with the .csv extension), or it will not load into the Partner Portal or the
Tool.
You are now ready to upload and import your CSV file into the Partner Portal.
Step 4b: Upload your Carrier CSV file into the Partner Portal
After creating your Carrier CSV file, you can upload your file into the Partner Portal to find
matching carrier records in the SmartWay database. The new Portal function will attempt to
match your carriers using the fields contained in your CSV file (e.g. street address, SCAC, etc.).
The Partner Portal contains detailed instructions on how to run the matching process, including
the ability to send 'reminder' emails to your carriers. Once you have finished using the Carrier
Matching function in the Partner Portal you will then create a new Carrier CSV file that can then
be imported back into your Tool.
NOTE: If this is the first time you have included unique carrier identification information in your
CSV file (e.g., an Internal Carrier ID), the function in the Partner Portal will not be able to find
matches using these new fields, but will be limited to common identification fields such as the
carrier's address, SCAC, MCN, and DOT. However if you include your unique carrier
identification information it will be stored in the SmartWay database so that it can be used in
subsequent years for quick and accurate matching. Any unique carrier identification
information you supply is not shared with any other SmartWay partners, but is strictly used for
matching your carriers in subsequent years.
You can log on to the Partner Portal at
http://app6.erg.com/smartwayweb/partneraccess/emailSubmlD.cfm. Then follow the
instructions regarding carrier identification to upload your CSV file and match your carriers.
Step 4c: Export your matched carrier file from the Partner Portal
When you are finished with the matching process within the Partner Portal, follow the
instructions to export your carriers to a Carrier CSV file. You are now ready to import your final
CSV file into the SmartWay Tool.
Step 5: Import and Validate Your CSV File
Once you have created your matched carrier data from the Partner Portal, you will import it
into your SmartWay Tool. On the main Home screen of the SmartWay Tool, select the business
unit into which you will be importing your CSV file.
1. You will now select the "Outside Data Import" tab under "Input Carriers."
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 17
-------
'-V'o SmartWav
Data Display
Match My CarH
Export Garners
IT
Manual Input Smart
Carriers
Manual Input Non-SmartWay
Carriers
2. Click the "Select File" button on the Outside Data Import screen.
Activity Data
Outside Data Import
Match Mv Gamers
USE OF THIS SCREEN IS OPTIONAL - NOT REQUIRED FOR NRCAN SUBMITTAL
Business Unts with a brge number of carriers may choose to prepare their carrier activ
systems. Please refer to the document titled 'Detailed Instructions for Data Export/In
prepare a CSV (comma separated values) file for import into the Tool.
If you have prepared a CSV file containing your company's carrier actvity datar you rrc
to find the appropriate file on your computer or network. Once you have specified yo
and content. Depending upon the size of the file this process may take several secon
Once completed, the number of valid and invalid records will be shown on the screen,
bottom of the screen. After reviewing these messages, you may either update your
selecting the 'Import File' button. A separate progress bar will indicate when your file
Fife Path:
Import Status
Total Records Valid Records Invalid Records
3. Select the matched Carrier CSV file you created in the Partner Portal, and click the
"Open" button.
4. After specifying the file path, you need to validate the data in your CSV file by using the
"Validate File" button. This performs an initial validation of the data in your CSV file.
Select File...
Validate File
5. After the validation is complete, the Tool will display a list of errors and warnings for
your review, and it will display a count of total records and valid records summarizing
the validation results.
File Path:
|c:\Documents and Settings\TJacks01\Desktop\test.csv
Validate File Import File Detailed Instructions for Data Export;Import (pdD
Import Status
Total Records Valid Records Invalid Records Validating Record 2 of 2
r
If your file contains errors or warnings, a message box will appear at the bottom
of the screen identifying what they are. You may still import the file even if
warnings or errors are detected, although any data field failing validation may
be ignored or may have been set to zero. In that case, corrections can be made
to the CSV file or within the Tool after importing your data.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 18
-------
6. The "Import File" button will now be enabled if there is at least one valid carrier entry in
the file, allowing you to import your CSV file.
7. To import the file, select the "Import File" button.
WARNING: At this point the system will delete any current carrier records
you may have entered for this company using the Manual Input screens, as
well as any Data Source descriptions you may have entered on the Activity
Data screen.
8. After importing your file, text will appear saying "Import successfully completed."
Once you have imported your Carrier CSV file into the Tool, you may augment, correct, or
modify any of the carrier data using the Tool screens. You may also view and edit any of the
carrier identification information you included in your CSV file (i.e. any of the 15 fields discussed
above that you included in your file). To view and/or edit this carrier identification information:
9. Select the "Match My Carriers" tab under "Input Carriers."
Activity Data % SrnartWay Data Display
Outside DM Import Export Carrier. Manual InputSmarMtty Manual Inpi* Non-SinartWa.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 19
-------
10. Select the "Step 5. Edit my carrier identification information" button.
Logistics and Shipper companies may have difficulty matching their carriers with the SmartWay carriers listed in the Logistics and
differences in company names, listed address, point of contact, etc. SmartWay has developed a new function that allows partne
from their in-house data systems with the data submitted by current SmartWay carriers. Matching carrier lists will make it easier
number of carriers to quickly and accurately complete their SmartWay Tool. Follow the steps below to match your carrier list wit
SmartWay carriers, allowing you to fill out your Tool more accurately and efficiently.
Step 1. Create your Carrier CSV file
Step 1 Import your Carrier CSV file into the Partner Portal
Step 3. Export your matched carrier file from the Partner Portal
Step 4. Import your matched carrier file into the Tool
Step 5. Edit my carrier identification Information
You can find more information by selecting the "Detailed Instructions for Data Export/Import (pdf)" or "Visit the Partner Portal"
Detailed Instructions for Data Export/Import fpdfl Visit the Partner Portal
11. Select "Next" on the popup screen.
Match My Carriers
Step 5. Edit my carrier identification Information
If you would like to view or edit any of the carrier identification information you included in your imported
CSV file (i.e. any of the 16 fields described in Step 1 that you included in your file), you may do so on the
next screen. The carriers you have imported/selected are listed there, including the identification fields
you imported. You can scroll through your carriers to view and/or edit the data for each carrier. Any
changes you make will be saved in the Tool and uploaded into the SrnartWay database. Because these
identification fields will be used to find your carriers in future years, we suggest using data values that are
unlikely to change overtime.
Select "Next" to continue and view/edit your carrier identification information or "Close" to return to the
previous screen.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 20
-------
12. The carriers you have imported are included in the listbox on the screen, including any
identification information columns and data you imported. You can scroll through the
list to view the data for each partner.
Input Carriers
Activity Data
%SmartWay
Data Display
Outside Data Import
Export Carriers
Manual Inpi.
Can
The carriers you have imported or selected are included in the list below, including the identification information you impc
view the data for each carrier. To edit the information for a carrier, select the carrier from the list and edit the values in tl
"Apply Edits" to save the changes to the carrier or "Cancel Edits" to discard changes. Your edits will be reflected in the list
columns included, return to the "Outside Data Import" tab and follow the instructions for importing the new carrier ideni
Back button to return to the instructions for "Match My Carriers," or select the Next button to continue.
AH Selected Carriers:
f
Ingram Barge Company
non-SmartWay Air Carrier - Short Haul
non-SmartWay Barge Carrier
non-SmartWay Logistics Carrier
non-SmartWay Multi-modal Carrier
Zeitner a Sons, Inc.
Zimmerman Truck Lines, Inc.
MyCarnerlDl
MyCa rrierNa me
M y C a rrierEmailAdd ress
MyCarrierlDl | MyCarrierName [ MyCarnerEmailAddress
:
def Ingram EC defj2gmail.com
ghi Short air ghi@gmail.com
jkl jkl@gmail.com
mno mno@gmail.com
pqr pqr@gmail.com
stu Zeitner stu@gmail.com ^^^
vwx Zim vwx@gmail.com ^^^^^^^^^
abc
Mum One
abc@gmail.com
Apply Edits
X Cancel Edits
13. To edit the information for a carrier, select the carrier from the list and edit the values in
the text boxes below the list. Select "Apply Edits" to save changes to the carrier or
"Cancel Edits" to discard changes. Your edits will be reflected in the listbox once applied.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 21
-------
Input Carriers
Activity Data
* Smart Way
Data Display
Outside Data Import
Export Carriers
Manual Inp
Car
The carriers you have imported or selected are included in the list below, including the identification information you imp
view the data for each carrier. To edit the information for a carrier, select the carrier from the list and edit the values in t
"Apply Edits" to save the changes to the carrier or "Cancel Edits" to discard changes. Your edits will be reflected in the lib
columns included, return to the "Outside Data Import" tab and follow the instructions for importing the new carrier iden
Back button to return to the instructions for "Match My Carriers," or select the Next button to continue.
AH Selected Carriers
Ingram Barge Company
non-SmartWay Air Carrier - Short Haul
non-SmartWay Barge Carrier
non-SmartWay Logistics Carrier
non-SmartWay Multi -modal Carrier
Zeitner Si Sons, Inc.
Zimmerman Truck Lines, Inc.
MyCarrierlDl
def
ghi
JW
mno
pqr
stu
vwx
| MyCarrierNa
Ingram BC
Short air
Zeitner
Zim
ne [ ! lyCarrierEmailAddress
def@gmail.com
ghi@gmail.com
jkl@gmaii.com
mno@gmail.com
pqr@gmail.com
stu@gmail.com
vwx@gmail.com
MyCarrierlDl
MyCarrierName
MyCarrierEmailAddress
|abc edit
Num One
labciSgmail.com
Apply Edits
X Cancel Edits
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,
• Explanations for any data that is significantly outside expected ranges (e.g., high/low
payloads).
You will also need to specify a % SmartWay option, and (for Shipper Tool users) a description of
a Custom Factor and associated data sources if you chose to use that option for your %
SmartWay allocation.
After importing your carrier data, please review every screen among the data entry screens to
verify completeness. The Tool will prompt you when data is missing or in error. Alternatively,
you may select the Validate Screen button at the bottom of each screen, which will allow you to
see all of the errors.
You are now ready to review your Tool, view data reports, and submit your final Tool to EPA for
approval.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 22
-------
Appendix
***SPECIAL CASES/CONSIDERATIONS*
YOU WISH TO ADD MORE CARRIER DATA AT A LATER DATE.
The Outside Data Import function was designed so that you can also add more carrier data at a
later date. To add carrier data, you should use the Export Selected Carriers option so that all of
the carrier data in the Tool is exported to a CSV file. You can then insert your additional carrier
data into this exported CSV file using Excel and then re-import the modified CSV file.
YOU HAVE MANUALLY ENTERED CARRIER DATA INTO THE TOOL AND THEN
DECIDE TO IMPORT OUTSIDE DATA.
When you perform an Outside 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 manually 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 your additional carrier data into this newly
exported CSV, then re-import the updated CSV back into the Tool.
YOU CHOOSE TO EXPORT THE DATA AS A CSV FILE AFTER YOU HAVE ENTERED
ACTIVITY DATA IN THE "Average Density" AND "Average Load Percentage" POP-
UP CALCULATORS.
When exporting and then re- importing your selected carriers, any values you may have entered
into the Average Density and Average Load Percentage pop-up calculators prior to export will be
lost. The previously-calculated output values from the calculators will still be retained, although
the calculator inputs themselves will be wiped out. You will also lose any previously entered
Out of Range explanations.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 23
-------
Glossary:
DOT Numbers - Companies that operate commercial vehicles transporting passengers or
hauling cargo in interstate commerce must be registered with the Federal Motor Carrier Safety
Administration and must have a USDOT Number. Also, commercial intrastate hazardous
materials carriers who haul quantities requiring a safety permit must register for a USDOT
Number. The USDOT Number serves as a unique identifier when collecting and monitoring a
company's safety information acquired during audits, compliance reviews, crash investigations,
and inspections. DOT numbers are strictly numeric and can be up to 7 digits in length.
Emissions 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.
Ranking Category - This refers to 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
values for their ranking category. Note that other carrier types do not have differentiated
ranking categories (i.e., all Barge carriers are grouped in the same category for ranking). Also
note that performance factors associated with rail carriers are based on modal averages (for
both SmartWay and Non-SmartWay carriers), based on publicly available information, rather
than on actual carrier performance.
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.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 24
-------
CSV File Field Descriptions:
• 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 Export 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 ranking category, you do not need to specify this
internal ID field.
• Carrier Name - This is simply 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 Export Carriers screen, you should not change
these values.
• Mode - In this field, the carrier mode must be identified as "Truck," "Rail," "Multi-
modal," "Barge," "Air," or "Logistics." This field is required and must be an exact match
if you are using carrier name, carrier mode, and ranking category to merge the
SmartWay data with your carrier data. If you created your CSV file using one of the two
Export buttons on the Export Carriers screen, you should not change these values.
• Ranking Category - This refers to the operation/equipment type category SmartWay
uses for ranking carriers; e.g., TL, Dry Van, Dray, etc. (For more information on Ranking
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 as follows:
o Auto carrier o Moving
o Dray o Package
o Expedited o Refrigerated
o Flatbed o Specialized
o Heavy/Bulk o Tanker
o LTL/DryVan o TL/DryVan
o Mixed
The only valid value for Multi-modal carriers is Truck/Rail. Rail, Barge, Air, and Logistics
carriers do not have specific names for their ranking category.
• SCAC - This is a truck carrier's Standard Carrier Alpha 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
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 25
-------
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 Motor Carrier JNumber. 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.
• DOT Number - This is a truck carrier's US Department of Transportation 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.
• Data Availability - This is an integer field whose valid values are 1 to 7 for most
carrier types. The Data Availability Option entered/selected for a carrier identifies
which carrier activity data you have available. The data that you enter in your CSV file
for Ton-Miles, Total Miles, Average Density, Average Load pet, and Average Payload
must be consistent with the carrier's Data Availability. If you do not specify a Data
Availability Option, the Tool will select a default.
The seven Data Availability Options are:
1. 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 type of cargo.
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 type of cargo.
7. I know Total miles only.
Note that Data Availability Options for LTL, package, and multi-modal carriers are
restricted to 1, 2, 3, 4 and 5 due to the difficulty allocating mileage fractions for these
carriers without payload-related information.
• Calc Metric - The Calculation Metric 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
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 26
-------
operation type. In general, ton-miles are good for LTL, package delivery, barge, air, rail,
multi-modal and logistics companies as well as heavier truck loads (roughly above 10
tons), while g/mile may be better for trucks with lighter payloads. Railcar-miles are also
an available option for the rail mode, although ton-miles are preferred.
• 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 209,207,446,000.
• 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, barge, air, and/or rail carrier. The Tool assumes that miles entered for truck
carriers correspond to (full) truckload-equivalent miles. Rail-miles correspond to railcar
miles. The Total Miles field is an integer that must not exceed 999,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 Total Miles, accounting for shipper-
company-specific freight densities. The density calculator must be used for Data
Availability Options 3 and 6. The Average Density field is a numeric field greater than
zero, with up to one decimal place. Note that density data is not available for air freight
at this time, and air carriers cannot select Data Availability Options 3 or 6.
• Average Load Pet - Depending on your Data Availability Option, the Average Load Pet
(percent) field may be required or ignored. Use the Average Load Pet calculator to
determine what fraction of your truck and rail carrier's cargo space is used to ship your
freight. The Load Percent calculator must be used for Data Availability Options 3 and 6.
Enter the load information as requested. The resulting percentages (shown on the
Activity Data screen) are multiplied directly by Total Miles to scale carrier mileage. The
Average Load Pet field is an integer field that must be between 1 and 100.
• Average Payload - Depending on your Data Availability Option, this field may be
required or ignored. Enter payload (in short tons - i.e., 2,000 Ibs) representing the
average cargo weight per loaded trip. Average Payload must be entered for Data
Availability Options 2 and 5. The Average Payload field is a numeric field that must be
greater than zero. Maximum allowable payloads vary by mode and truck operation
category - please see the Shipper Tool Technical Documentation for details. The total
number of digits, including the decimal point, must not exceed 5 digits.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | Pg 27
------- |