Importing and Exporting Routes

If you have a large number of routes, you can choose to import them using a comma delimited CSV format import file. You can also export routes with associated jobs created in the Scenarios user interface in the same format, for backup purposes.

Importing Routes

To import a file of routes:

 

1.Click the More menu button in the upper right corner of the Routes panel and select the Import Routes option.

 
2.Drag and drop the CSV format file from its saved location into the dialog box. Alternatively, click Choose File, browse to the file on your computer, select it, and then click Open.

 
3.Click Manage Mappings. The Manage Mappings dialog box opens. From here you can use one of the following methods to map the columns in your import file to the columns in the Routes list:

 
oIf you have used the column naming conventions described in the route import file format table below, you can click Auto Map and the columns in your import file are mapped to the correct route values automatically. You can use the Sort button to toggle between sorting the columns on the left alphabetically or in the order they appear in the file.
oIf you have previously saved a JSON file containing the column mappings, you can click Load to use the saved mappings.
oIf your import file uses your own custom column names, follow these steps:
a.Click a column name in the left panel, and then click the right-facing arrow .
b.Select the field in the right panel, and click Edit Mapping.
c.Select the field that matches the values contained in the import column.
d.Click OK.

 

 
Repeat this process for each column in your import file.

4.(Optional) When you have mapped your columns from the file to the Routes list, you can perform any of the following actions:

 
oClick on the left-facing arrow to remove the selected field from the Routes list.
oClick Edit Mapping to change the mapping for the selected field.
oClick Clear to clear all mappings you have made and start over.
oClick Download to save the current mappings so that you can reuse them another time.
oClick the Settings button to select the formatting to use for dates.

 

5.When you have finished mapping all your columns from the import file to the Routes list, click OK to complete the import.

Route Import File Format

Your route import CSV files can contain some or all of the following columns. Each row in this file represents a job (the job.[Value] columns), and the route it is assigned to (the route.[Value] columns):
 

Column Name

Value

route.id

The unique identifier of the route.

route.name

The name of the route.

route.date

The date when the route starts.

route.driver

The driver to use for the route.

route.driver.name

The driver's name.

route.vehicle

The vehicle to use for the route.

route.vehicle.name

The vehicle's name.

route.start_time

The start time of the route.

route.start_location_id

The identifier of the route's start location marker. This matches the marker.id that is listed in Marker Import File Format.

route.start_lat

The latitude coordinates of the route's start location.

route.start_lon

The longitude coordinates of the route's start location.

route.start_address

The formatted address of the route's start location, including the country.

route.end_location_id

The identifier of the route's end location marker. This matches the marker.id that is listed in Marker Import File Format.

route.end_lat

The latitude coordinates of the route's end location.

route.end_lon

The longitude coordinates of the route's end location.

route.end_address

The formatted address of the route's end location, including the country.

route.normal_working_time

The hours that the driver on the route may work before overtime is paid.

route.max_working_time

The maximum number of hours that the driver on the route is permitted to work, including overtime.

route.max_driving_time

The maximum time the driver on the route is permitted to spend driving.

route.optimize_start_time

Whether to use a variable shift for the route. "TRUE" or "FALSE".

route.end_time

The time of day when the route ends.

route.max_jobs_on_route

The maximum number of jobs permitted on the route.

route.time_on_site_at_start_location

The time spent at the route's start location, which is typically the depot.

route.time_on_site_at_return_to_depot

The time spent at the depot location, if the route requires a return to depot stop to reload the vehicle.

route.time_on_site_at_end_location

The time spent at the route's end location, which is typically the depot.

route.attributes

The attributes associated with the route.

route.breaks_relative_to_start

Whether breaks on the route have a start time relative to the shift start. If so, breaks take place for a certain duration after the start of the route, rather than at a set time of the day. "TRUE" or "FALSE".

route.cost_per_mi

The cost per mile of driving time on the route in US dollars.

route.fixed_cost

The fixed cost of carrying out the route in US dollars.

route.cost_per_hour

The hourly pay rate of the driver on the route.

route.overtime_cost_per_hour

The hourly overtime pay rate of the driver on the route.

route.enable_start_stem_time

Whether to include the time it takes to drive to the first job from the depot in the route calculations. "TRUE" or "FALSE".

route.enable_end_stem_time

Whether to include the time it takes to drive from the last job to the depot in the route calculations. "TRUE" or "FALSE".

route.driving_speed_percentage

The percentage of the speed that vehicles are expected to drive on the route.

route.load_capacity

The total amount of load that can be delivered on the route.

route.forbid_crossing_roads_at_jobs

Whether the route must stop on the same side of the road as the job location. "TRUE" or "FALSE".

route.vehicle_type

The vehicle type for which the route is calculated. For example, "Automobile" or "Straight Truck".

route.height_in

The height of the vehicle for which to calculate the route, in inches.

route.weight_lb

The weight of the vehicle for which to calculate the route, in pounds.

route.unladen_weight_lb

The weight of the vehicle for which to calculate the route, in pounds.

route.hazmat_load_type

The type of hazardous material permitted to be carried by vehicles on the route.

route.has_next_multiday_day

Whether the route is part of a multi-day route, which continues on the following day.

route.number_of_axles

The number of axles of the vehicle for which to calculate the route.

route.number_of_wheels

The number of wheels of the vehicle for which to calculate the route.

route.number_of_trailers

The number of trailers of the vehicle for which to calculate the route.

route.allow_toll_road

Whether the toll roads are permitted on the route. "TRUE" or "FALSE".

route.allow_u_turns

Whether u-turns are permitted on the route. "TRUE" or "FALSE".

route.allow_ferries

Whether ferry crossings are permitted on the route. "TRUE" or "FALSE".

route.same_side_routing

Whether vehicles are required to approach job locations on the same side of the road as the site entrance. "TRUE" or "FALSE".

route.enable_historic_traffic

Whether historic traffic data is considered for the route. "TRUE" or "FALSE".

route.length_in

The length of the vehicle for which to calculate the route, in inches.

route.width_in

The width of the vehicle for which to calculate route, in inches.

route.kingpin_to_last_axle_in

The length, from kingpin to last axle, of the vehicle for which to calculate the route, in inches.

route.custom.[custom]

The custom fields for the route.

route_values.cost

The cost of the route in US dollars.

route_values.internal_cost

The internal cost of the route in US dollars.

route_values.distance_mi

The length of the route in miles.

route_values.violations

The violations present on the route.

route_values.working_time

The total number of working hours required to complete this route.

route_values.driving_time

The total number of driving hours required to complete this route.

route_values.next_multiday_day

The date of the following day, if this route is part of a multi-day route.

route_values.previous_multiday_day

The date of the previous day, if this route is part of a multi-day route.

job.id

The unique identifier of the job that is part of this route.

job.name

The name of the job.

job.location_id

The identifier of the job's location marker. Can be supplied as an inline marker or the identifier of a marker (the marker.id that is listed in Marker Import File Format).

job.lat

The latitude coordinates of the job. Required if job.address is not provided.

job.lon

The longitude coordinates of the job. Required if job.address is not provided.

job.address

The formatted address of the job, including the country. Required if job.lat and job.lon are not provided.

job.time_on_site

The length of time the driver is expected to spend at the job site.

job.time_window_start

The start time of the job's time window. Drivers can arrive at the job site between the job.time_window_start and job.time_window_end times.

job.time_window_end

The end time of the job's time window. Drivers can arrive at the job site between the job.time_window_start and job.time_window_end times.

job.time_window2_start

The start time of the job's second time window. Drivers can arrive at the job site between the job.time_window2_start and job.time_window2_end times.

job.time_window2_end

The end time of the job's second time window. Drivers can arrive at the job site between the job.time_window2_start and job.time_window2_end times.

job.priority

The priority of the job. Available priority values are "lowest", "low", "normal", "high" or "highest".

job.date

The date when the job must be routed.

job.allowed_variance_before

The number of days the job can be routed before job.date, if job.date is set.

job.allowed_variance_after

The number of days the job can be routed after job.date, if job.date is set.

job.allowed_days_of_week

The days of the week when the job can be scheduled on a route. Uses a comma-delimited list in the format "sun,mon,tue,wed,thu,fri,sat". If this field is empty, all days are permitted.

job.allowed_dates

The dates when this job can be routed.

job.required_driver

The driver.id of the driver required to complete this job.

job.forbidden_drivers

The driver.id values of the drivers forbidden from completing this job.

job.required_route_attributes

The required attributes of the route needed to assign this job to a route.

job.disallowed_route_attributes

The attributes on a route that would prohibit this job being assigned to the route.

job.required_driver_attributes

The required attributes of the driver needed to assign the job to a route assigned to the driver.

job.disallowed_driver_attributes

The attributes on a driver that would prohibit this job being assigned to a route assigned to this driver.

job.required_vehicle_attributes

The required vehicle attributes needed to assign this job to a route assigned to a vehicle.

job.attributes

The tags for the job.

job.code

The code of the job.

job.notes

Notes for the job.

job.load

The amount of load delivered at the job.

job.custom.[Custom]

The custom fields associated with the job.

job_values.arrival_datetime

The arrival date and time for the job.

job_values.distance_to_meters

The distance to the job, in meters.

job_values.time_to

The time that it takes to get to the job.

job_values.violations

The violations for the job.

job_values.next_multiday_day

The date of the following day, if this job is part of a multi-day route.

job_values.previous_multiday_day

The date of the previous day, if this job is part of a multi-day route.

job.potential_date_range.from_date

The start date of the potential date range in which the job could be completed.

job.potential_date_range.num_days

The number of days of the potential date range in which the job could be completed.

job.allowed_days_of_week

The days of the week on which jobs in the route can be.

job.exit_location_id

The unique location identifier of the job that is linked to a job that is part of this route.

job.exit_lat

The latitude coordinates of the job that is linked to a job that is part of this route.

job.exit_lon

The longitude coordinates of the job that is linked to a job that is part of this route.

job.exit_address

The address of the job that is linked to a job that is part of this route.

job.exit_time_on_site

The time that drivers are likely to spend at the location of a job that is linked to a job that is part of this route.

job.type

The job's type.

job_values.idle_time

The idle time that is permitted between jobs.

job.stop_number

The number of the stop. For example, the first job on the route is 1.

job.sequence_number

The order of this job in the route, starting from 0.

Exporting Routes

To export a file of routes:

 

1.In the Routes panel, select the check box next to each route that you want to export.

 
2.Click the More menu button in the upper right corner of the Routes panel and select the Export Routes and Jobs option.

 
3.Click Choose Columns and select the columns that you want to export. By default all columns are selected. Click Export.

 
4.The CSV format export file is saved to the default download location on your computer.