Transport Partnership
U.S. ENVIRONMENTAL PROTECTION AGENCY
Logistics, Multi-modal,and Shipper
Partner 2.O.15 Tools: ^^^S^
Guide to Importing Carrier Data Using
the Outside Data Import Function
2015 Data Year - United States Version
&EPA
United States
Environmental Protection
Agency

-------
Transport Partnership
U.S. ENVIRONMENTAL PROTECTION AGENCY
Logistics Multi-modal,and Shipper
Partner 2.O.15 Tools:
Guide to Importing Carrier Data Using
the Outside Data Import Function
2O15 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-16-057a
       Agency                              Ju|y2Q16

-------
Table of Contents
TABLE OF CONTENTS	3

OVERVIEW	4
        THE OUTSIDE DATA IMPORT FUNCTION IS RIGHT FOR YOU IF:	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 CARRIER CSV FILE	7
     Option 1: Export a Master CSV File	9
     Option 2: Manually Build a CSV File	10
     Option 3: Create a Carrier CSV File from Scratch	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 SMARTWAY PORTAL	17
   STEP4C: EXPORT YOUR MATCHED CARRIER FILE FROM THE SMARTW AY PORTAL	18
   STEPS: IMPORT AND VALIDATE YOUR CSV FILE	18
   STEPS: ENTER ADDITIONAL DATA INTO THE TOOL	22

APPENDIX	24
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 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 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 | p 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 SmartWay 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 |  p 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 SmartWay Portal

             Step 4c.  Export your matched carrier file from the SmartWay 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 | p 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 — 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
       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.

   •   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.
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 | p 7

-------
       Option 3 — 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.

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 SmartWay 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 SmartWay Portal
3. Export your matched carrier file from the SmartWay 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 | p 8

-------
Option 1: Export a Master CSV File
    1.  Select the "Export Carriers" Tab.
                                                
-------
Option 2: Manually Build a CSV File
    1.   Select the "Manual Input SmartWay Carriers" tab.
                                  Activity Data
                                                      »/b SmartWay
                              Export Carriers
                                              Match My Carriers
                                                               Outside Data Import
                                                                           Data Display
                                                        Manual Input Non-SrnartWs-.
                                                             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 SmartWay Category drop-down list
        box, the SCAC, MCN, DOT and/or NSC box, and the Address/City/State/Zip boxes). An
        example selection screen is shown below for the Logistics Tool.
             1 rgistics Tool: Logistics 1, Inc.: Group 1

                               Activity Data
                                                Va Smart Way
                                                                  Data Display
                           Export Carriers
                                         Match My Garners
                                                        Outside Data Import
           Filter
Name 1 ? 1
Mode ^ I
jd

Address!
aty!"
SmartU/av | , , State 1 3 ^ ^
Category _Lj SCAC _Lj HCN _Lj DOT JJ MSC Jj I 	
|AII _rj | Remove Filter

d
         Available SmartWay Carriers: 3,312
                                        Carrier Mode  Category
1st Choice Logistics LLC
3 Rivers Logistics, Inc
AaZTrucking, Inc,
ASiR Global Logistics
A.M.Transport Services, Inc,
Ace Logistics, Inc,
Adams Third Wave Logistics LLC: Freight Broker
< Delete Selected Carriers
             BACK  SAVE
    3.   As you find each of your carriers, select 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.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 10

-------
                  Add Selected Carriers to tist Bek™
          (Press the CTRL button to select multiple items)
X^
Selected SmartWay Carriers: 7





v
1st Choice Logistics LLC
3 Rivers Logistics, Inc
A &Z Trucking, Inc.
ASR Global Logistics
Fremont Contract Carriers, Inc. : Flatbed Division
Fremont Contract Carriers, Inc. : Van Division
FTI: 23 10026 Canada Limited

Smart Way
Carrier Mode Category
Logistics
Logistics
Logistics
Logistics
Truck
Truck
Truck



Flatbed
TL/Dry Van
TL/Dry Van

SCAC
FCOS
TVQI
AZTD
AMLQ
FRCI
FRCI
TEON

HtM
MC833561
MC 376 181
MC 510*19
MC316077
MC 128 190
MC 128 190
MC255483

DOT
DQT2423750
DOT2225931
DOT2222961
DOT733
DOT733
DCfT 100969

NSC


N/A
N/A
111839582

^>





J

                    ^ Delete Selected Carriers
    4.   Select the "Manual  Input-Non SmartWay Carriers" tab.
                                     Activity Data
                                Export Carriers
I
                                                           % SmartWay
                                                  Match My Carriers
                                                                                Data Display
                                                                    Outside Data Import
                                 Manual Input SmartW;
                                      Carriers
           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: |

                 [pieasecheck this box to indicate that all Non-SmartWay Carriers have been added fur this businessiiinitl
    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 selecting
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function |  p 11

-------
         the "Add Selected Carriers to List Below" button.
                                     Activity Data
                                                           °/o Smart Way
                                                                                  Data Display
              Instructions
                                Export Carriers
                                                  Match My Carriers
                                                                     Outside: Data Import
                                      Manual Input SmartWay
                                           Garners
          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:  s2

                 Please check this box to indicate that all Non-SmartWay Carriers have been added for this business unit *
          AVAILABLE NOH-SMARTWAY CARRIER TYPES j>j

          Carrier Type
non-SmartWay Logistics Carrier
non-SmartWay Mulfi -modal Carrier
non -SmartWay Truck Carrier - Auto Carrier
non-SmartWay Truck Carrier - Dray
non-SmartWay Truck Carrier * Expedited
non-SmartWay Truck Carrier - flatbed
non-SmartWay Truck Carrier - Heavy/Bulk
Logistics
Multi-modal
Truck
Truck
Truck
Truck
Truck™^^P
^J
q

                Add Selected Carrier Types to List Below
                                                'tPress the CTRL button to select multiple items)
          SELECTED NON-SMARTWAY CARRIERS

          Carrier Type
                                                Carrier Mode

           non-SmartWay Air Carrier - Long Haul
           non-SmartWay Barge Carrier
           non-SmartWay Logistics Carrier
           non-SmartWay Multi-modal Carrier
           non-SmartWay Truck Carrier - General
                  X Delete Selected Carrier Types
Air
Barge
Logistics
Multi-modal
Truck
    6.   Once you have selected all of your SmartWay and non-SmartWay carriers, go to the

         "Export Carriers" tab.
                                                            % SmartWay              Data Display
                                                   Match My Carriers
                                                                      Outside Data Import
                                        Manual Input SmartWay     Manual Input Non-SmartWs
                                            Carriers               Carriers
         Note: Steps 7 and 8 are for Shipper Tool users only.

    1.   Select the "Optional Carrier Differentiation" tab.
          SmartWay Shipper Tool: Example Shipper Company
                      Outside Data Import
                                         EKport Carrier:
                                                       Manual Input SmartUfay Carriers
                                                                              Manual Input Non-SmartUfay Carriers
                                                                                                        Optional Section
    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
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function |  p 12

-------
        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.
         SmartWay Shipper Tool: Example Shipper Company
                                                    Activity Data
                                                                  % SmartWay
                                                                                Data Display
                                                                                               Optional Sections
                                                  Manual Input SmartUlay Carriers

          INPUTS ON THIS SCREEN ARE OPTIONAL - NOT REQUIRED FOR EPA SUBMITTAL
                                                      Ranking
          All Selected Carriers: 7                      Carrier Mode  Category
non- SmartWay Logistics Carrier
non-5martWay Multi-modal Carrier
non- SmartWay Truck Carrier
3 Rivers Logistics, Inc.
CONUS Transportation Inc
National Delivery Systems, Inc.
Logistics
Multi-modal
Truck
Logistics TVQI MC376181
Truck TL/DryVan CNUT MC2G47772 DGT3Q5794
Truck LTL/DryVan ND5N MC429253 DOT96639Q
             Select Carrier for Descriptive Data
                                    Jj
          Carrier Rows for J.B. Hunt Transport, Inc.: Intermodal Division: 1
                                                                I Inbound/Outbound	I Dornet-t '.''J'li >.-• > v.. -:nal I Internal Shipper Segment 1   I
                   ort. Inc.: Intermodal Division
             Business Unit
                               Inbound/Outbound
                                                   Internal Shipper Segment 1
                               ]lnbound
                      |23D
          jdF
  Domestic/International
^J (Domestic
n^r
                                                   Internal Shipper Segment Z
                                                                            Apply Descriptive Data to Selected Carrier
                                                                          •?< Delete Selected Carrier's Optional Information
                                                                             jj
                                                                             ±J
                                                                                                       Page 14
    9.  Select the "Export Selected Carriers" button.

    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: Create a Carrier CSV File from Scratch
You may also choose to create your Carrier CSV file from scratch using your in-house data
system.
         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 | p 13

-------
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 SmartWay 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").
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.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 14

-------
     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.
Step 4: Carrier Matching (OPTIONAL)

If you wish to take advantage of the carrier matching function in the SmartWay Portal, you will
continue populating the CSV file at this point before visiting the SmartWay 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:
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
Column Header
MyCarrierName
MyCarrierlDl
MyCarrierlD2
MyCarrierlDS
MyCarrierSCAC
MyCarrierMCN
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 15

-------
Carrier Identification Information
DOT
NSC
Street address
City
State abbreviation
Postal code
Phone number
Column Header
MyCarrierDOT
MyCarrierNSC
MyCarrierStreetAddress
MyCarrierCity
MyCarrierState
MyCarrierZipCode
MyCarrierPhoneN umber
Carrier Contact Information
Contact's email address
Contact's correspondence name
Preferred language of correspondence
Column Header
MyCarrierEmail Address
MyCarrierContactName
MyCarrierContact Language
      You may include as many or as few of these 13 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 as 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 SmartWay 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).
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 16

-------
     3.  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
        MyCarrierContactLanguage), 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.

     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 SmartWay Portal or
        the Tool.

You are now ready to upload and import your CSV file into the SmartWay Portal.

      Step 4b: Upload your Carrier CSV file into the SmartWay Portal

After creating your Carrier CSV file, you can upload your file into the SmartWay 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 SmartWay 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 SmartWay Portal  you will then create  a new Carrier CSV file
that  includes the SmartWay Carrier ID information which can then be imported back into your
Tool. Because this new CSV file contains SmartWay Carrier ID  information, your carriers will
automatically match and be recognized as SmartWay carriers.

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 SmartWay 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, NSC, 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 SmartWay Portal at
http://app5.erg.com/smartwayweb/portal/epalogin.cfm?language=ENG. Then follow the
instructions regarding carrier identification to upload your CSV file and match your carriers.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 17

-------
        Step 4c: Export your matched carrier file from the SmartWay Portal

When you are finished with the matching process within the SmartWay 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 SmartWay 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."
                                    Activity Data
                                                         Vo SmartWay
              Instructions
                               Export Carriers
                                                Match My Carriei
                                                                         Manual Input SmartWay
                                                                             Carriers
    Manual Input Noft-SmartWa
          earners
    2.  Choose the "Select File" button on the Outside Data Import screen.
                                    Activity Data
                                                          SmartWay
                               Export Garners
                                                Match MY Carriers
                                                                         Manual Input SmartWay
                                                                             Carriers
J.
Manual Input Non-SrnartWa
     Carriers
          USE OF THIS SCREEN IS OPTIONAL - NOT REQUIRED FOR EPA SUBMITTAL
          Business Units with a large number of carriers may choose to prepare their carrier activity data offline, using outputs from their own freight management software or related data
          systems. Please select the button labeled 'Detailed Instructions for Data Export/Import' below to obtain detailed information on how to prepare a CSV (comma separated values)
          file for import into the Tool.

          If you have prepared a CSV file containing your company's carrier activity data, you may import the data file into the Tool by selecting the 'Select File' button to the right in order
          to find the appropriate file on your computer or network. Once you have specified your CSV file's location, select the 'Validate File1 button below to check the file for proper format
          and content. Depending upon the size of the file this process may take several seconds.  A progress bar indicates the percentage of your file that has been validated.
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 C
selecting the 'Import File' button. A separate progress bar will indicate when your file ii
          File Path:
                                                                 .ith a description of errors and/or warnings associated with specific records at the
                                                                 iffline and repeat the above steps, or continue and import the file into the Tool by
                                                                 complete. When finished importing, select the Next button to continue.
                                                                                 Detailed Instructions for Data Export/Import fpdfi
            Import Status

              Total Records    Valid Records   Invalid Records
    3.  Select the matched Carrier CSV file you created in the SmartWay Portal, and select 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
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 18

-------
   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:
                                 Select File...   |  Validate File  [  Import File Jf  Detailed Instructions for Data Export/Import fpdfl
  Import Status
    Total Records   Valid Records   Invalid Records  Validating Record 8 of 8
             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.

    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."
                                                                    Manual Input SmartWay
                                                                                   Manual Input Non-SmartWa
                                                                                       Carriers
    10. Select the "Step 5. Edit my carrier identification information" button.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 19

-------
                Input Carriers
                                                              W> SmartWay
                                                                                      Data Display
                                                                        Outside Data Import
Manual Input SrnaitWay
    Garners
Manual Input Non-SmartWa
      Carriers
           Logistics and Shipper companies may have difficulty matching their carriers with the SmartWay carriers listed in the Logistics and Shipper Tools, due to blight
           differences in company names, listed address, point of contact, etc. SmartWay has developed a function that allows partners to match their carrier lists
           from their in-house data systems with the data submitted by current SmartWay carriers. Matching carrier lists will make it easier for partners with a large
           number of carriers to quickly and accurately complete their SmartWay Tool.  Follow the steps below to match your carrier list with the list of current
           SmartWay carriers, allowing you to fill out your Tool more accurately and efficiently.
              Step 3. Export your matched carrier file from the Partner Portal
              Step 4. Import your matched carrier file into this Tool
           You can find more information by selecting the "Detailed Instructions for Data Export/Import (pdf)" or "Visit the Partner Portal" buttons below.
                 Detailed Instructions for Data Export/Import fpdfi
                                                              Visit the Partner Portal
           Help us improve the carrier matching function by providing recommendations on the Suggestions screen under Step 7 of the Home screen.
     11. Select "Next" on the popup screen.
          Match My Carriers

            Step 5. Edit my carrier identification Information
                        X
            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 SmartWay 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 over time.

            Select "Next" to continue and view/edit your carrier identification information or "Close" to return to the
            previous screen.
                                                         CLOSE
     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.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function |  p 20

-------
           SmartWay Logistics Tool: Logistics 1, Inc.: T
                                                                                             Data Display
                                                                              Outside Data Import
                                                                                                  Manual Input SirwartWay
                                                                             Manual Input Non-SmartWay
                                                                                   Carriers
             The earners you have imported or selected are included in the list below, including the identification information you imported. Scroll through the fist to
             view the data for each carrier. 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 the changes to the carrier or "Cancel Edits" to discard changes. Your edits will be reflected in the list box once applied. To add/remove
             any of the columns displayed below, return to the "Outside Data Import" tab and follow the instructions for importing the new carrier identification
             information. Select the Back button to return to the instructions for "Match My Carriers/ or select the Next button to continue.
All Selected Garners;

B&H Freight, Inc
B.E.S.T. Inc, dba Go Best Logistics
Backhaul Direct, LLC
Badger Refrigerated Express, Inc,
Barefoot Express Inc
Barton Logistics LLC
non -SmartWay Logistics Garner
«l
MyCarrierName |

BSH Freight
Go Best Logistics
Backhaul Direct
Badger Refrigerates
Barefoot Express
Barton Logistics
US Trucking



a002
a003
a004
aOOS
aooe
a007
aOOS
1


def
oh,
jkl
mno
pqr
stij
vwx



omega
phi
zeta
delta
P
rho
lambda


^_
BHFR *^
GBLW
BHDR.BHDS
BXGI
BFXP
BNBH
LAMB



^1C423651 6497315
^^^ 3578951
MCSSNi 7531598
MC294455^^> S525852
^^^96369
MC326257 ?3*«1
32574S9 1591595

1 HV<


325

326

123-

>]
                 llyCarrierNanie

                 HYCarrierlDJ

                 MvCarrierlDJ

                 MvtarrierJD3

                 MvCarrierSCAC

                 HyCarrierHCN

                 MyCarrierDOT

                 HyCamerNSC

                 HyCarrierStreetAddress

                 HvCarrierCrty




                          I,
                   BACK   SAVE
                                          |AMX Logistics
|a001
|abc
|MC195425
|31f>4975
|5278596
1123 Main Street
MvtarrierState

MyCarrierZIPCode

MvCarriepPhoneNumber

MyCarrierEmailAddress

MvCarrterContactName

Mv^arrierContactLanguage
                                                 Apply Edrts
178958
|5124878598
                                                             13lpha@5olutions.com
                                                             | Brad Carson
| ENGLISH
IBRENHAH
            X Cancel Edits
                                                                                                      PageL-23
     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  | p 21

-------
               Input Carriers
              Instructions
        SmartWay Logistics Tooi: Logistics 1, Inc.: Truck Division

                                       Activity Data
                                  Export Carriers
                                                                          Outside Data Import
Manual Input Smart
     Carriers
          The carriers you have imported or selected are included in the list below, including the identification information you imported.
          view the data for each carrier. To edit the information for a carrier, select the carrier from the list and edit the values in the text
          "Apply Edits" to save the changes to the carrier or "Cancel Edits" to discard changes. Your edits will be reflected in the list box t
          any of the columns displayed below, return to the "Outside Data Import" tab and follow the instructions for importing the new
          information. Select the Back button to return to the instructions for "Match My Carriers," or select the Next button to continu
All Selected Carriers:
1 AMX Logistics: Loqistics Division
BSH Freight, Inc
B.E.S.T. Inc, dba Go Best Logistics
Backhaul Direct, LLC
Badger Refrigerated Express, Inc,
Barefoot Express Inc
Barton Logistics LLC
non-SmartWay Logistics Carrier

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

-------
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.
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 24

-------
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 express the rate emissions are generated by a given
source per unit of activity, such as grams per mile travelled or grams per gallon of fuel
consumed.  The SmartWay tools combine emission factors from various sources with activity
estimates provided by partners to estimate total emissions for C02, NOx, and PM.

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.

NSC - The National Safety Code (NSC) number,  issued to fleets with Canadian operations, has a
different format depending on the province.

SmartWay Category - This  refers to the operation type category used  for SmartWay truck
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 SmartWay 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 | p 25

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

   •   SmartWay Category - This refers to the operation/equipment type category
       SmartWay uses for SmartWay carriers; e.g., TL, Dry Van, Dray, etc. (For more
       information on SmartWay 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 SmartWay 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  | p 26

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

       NSC - The National Safety Code (NSC) number, issued to fleets with Canadian
       operations, has a different format depending on the province, as shown below, where #
       signifies a number and @ signifies a letter:

            BC: nnnnnnnnn
            AB: AB###-####
            SK: ####
            MB: MB#######
            ON :#########
            NB:
            NS: @@@@@nnnnnnnnn
            PE: P £######
            NL: NF@@########
            YT: YT-###
            NT: NT-#####

            For ON, the NSC corresponds to the Commercial Vehicle Operator's Registration
            (CVOR).

            For QC, the NSC corresponds to the Politique devaluation des proprietaires et des
            exploitants de vehicules lourds (PEVL).

       Data Availability - This is an integer field whose valid values are a through d 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, and Average Payload must be consistent with the carrier's
SmartWay Guide to Importing Carrier Data Using the Outside Data Import Function | p 27

-------
       Data Availability Option.  If you do not specify a Data Availability Option, the Tool will
       select a default.

       The four Data Availability Options are:

                 a.  I know both Ton-miles and Total Miles (preferred)
                 b.  I know Ton-miles and Average Payload
                 c.  I know Total Miles and Average Payload.
                 d.  I know Total miles only.

       Note that Data Availability Options for LTL, package, logistics, and multi-modal
       carriers are restricted to a, b, and c due to the difficulty allocating mileage fractions
       for these carriers without payload-related information.

   •   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 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 b and c. 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 | p 28

-------