Job and Route Import

The Job Import and Route Import tools are useful when you want to create a large number of jobs or routes quickly using a CSV format import file rather than manually adding individual jobs from the Add Job dialog, or from the route creation tools available in the platform and in external systems such as Verizon Connect Dispatch. The job importing tool is accessed from the jobs Import button located on the Jobs List in suitably configured accounts, and the route Import button is located on the Jobs Gantt Chart.

Both tools can also be accessed directly from the Jobs & Routes section of the main menu, by clicking either the Route Import or Job Import links.

Importing Jobs

To import a jobs file:
 

1.Click the Import button to the right of the Jobs List, or open the Tasks section of the main menu and select Job Import. The Job Import window opens.

 
2.Click the Choose a file to import button at the center of the screen:

 
3.Browse to the CSV format file describing the jobs you want to import. This file should contain columns of data as described in the Import File Structure section. If the column names in the file differ from those listed in this section, you will need to perform manual mapping of the file's column names with the required database columns; this is described in the File Mapping section.
 
The import file should be closed before you import it.
The import file should be no larger than 50MB in size.
 
4.Select the file and click Open. The File Preview screen opens:

 
5.Select the delimiter type used to separate columns of data in your import file from the Delimiter drop-down menu, in the upper right corner. This step is only required if the tool does not detect the format automatically. Job import files using comma (,), pipe (|), semicolon (;), colon (:) and tab delimiters to separate column values are accepted.

 
6.Click Import in the lower right corner of the screen. Note that if you have not yet created a file map, this button will not be available until you have opened the Mapping... menu and created an initial mapping between the columns in the import file and the required job properties. The import file is validated and the Import Data dialog opens, listing the number of jobs in the file that will be imported. Click Continue and the jobs are imported. Once the file has imported, click Restart to import another file, if required.

 
If the import process fails as a result of errors in the file, an Import Data error dialog message is shown, indicating the number of jobs in the file that will not be imported. Click the Edit button on the failure dialog to review the error messages created and the jobs that they relate to, or Continue to ignore them (the failed jobs will be ignored). If you choose to edit the file, you can change the individual data values directly in place by double-clicking a value and editing it. Once you have corrected any problems in the import file, click the Revalidate button to confirm that the errors are no longer present, and then click Import to begin the job import procedure from step 6 above.
Importing Routes

To import a routes file:
 

1.Click the Import button to the right of the Jobs Gantt Chart, or open the Jobs & Routes section of the main menu and select Route Import. The Route Import window opens.

 
2.Click the Choose a file to import button at the center of the screen:

 
3.Browse to the CSV format file describing the routes you want to import. This file should contain columns of data as described in the Import File Structure section. If the column names in the file differ from those listed in this section, you will need to perform manual mapping of the file's column names with the required database columns; this is described in the File Mapping section.
 
The import file should be closed before you import it.
The import file should be no larger than 50MB in size.
 
4.Select the file and click Open. The File Preview screen opens:

 
5.Select the delimiter type used to separate columns of data in your import file from the Delimiter drop-down menu, in the upper right corner. This step is only required if the tool does not detect the format automatically. Route import files using comma (,), pipe (|), semicolon (;), colon (:) and tab delimiters to separate column values are accepted.

 
6.Click Import in the lower right corner of the screen. Note that if you have not yet created a file map, this button will not be available until you have opened the Mapping... menu and created an initial mapping between the columns in the import file and the required route properties. The import file is validated and the Import Data dialog opens, listing the number of routes in the file that will be imported. Click Continue and the routes are imported. Once the file has imported, click Restart to import another file, if required.

 
If the import process fails as a result of errors in the file, an Import Data error dialog message is shown, indicating the number of routes in the file that will not be imported. Click the Edit button on the failure dialog to review the error messages created and the routes that they relate to, or Continue to ignore them (the failed routes will be ignored). If you choose to edit the file, you can change the individual data values directly in place by double-clicking a value and editing it. Once you have corrected any problems in the import file, click the Revalidate button to confirm that the errors are no longer present, and then click Import to begin the route import procedure from step 6 above.
Import File Structure

Job and route import files should contain the following columns of information about individual jobs and routes. The column names in your files can differ from those shown below if you want, but these columns will require you to manually map them during the import process. To simplify future imports, you can also save any custom mappings you create as an import profile. These profiles can then be re-used when importing similar files in the future.

If the field names listed in the table below are used, the mapping process can be performed automatically without the need to manually map columns or create a custom import profile.

Job Import File Structure

Field

Purpose

Example

ID

A unique ID used to represent the job.

12876437

Name

An arbitrary but typically descriptive name used to represent the job, such as the job address or the marker name.

Smith Steel Pickup

TerritoryID

The name of the territory the job will occur within. This is the territory that the job marker is assigned to. Required.

West Territory

StreetNumber

The number of the address at which this job occurs. Required if no latitude and longitude coordinates are supplied.

10

StreetName

The name of the street on which this job occurs. Required if no latitude and longitude coordinates are supplied.

Main Street

City

The city where the job is located. Required if no latitude and longitude coordinates are supplied.

Springfield

Region

The State, Province, or Region where the customer is located.

CA

PostalCode

The zip code or postal code of the job location.

95062

Country

The country where the job and customer are located. Required if no latitude and longitude coordinates are supplied.

USA

Lat

The latitude coordinates of the job location, between -90 and 90 degrees. Required if no job address details are supplied.

34.86269

Lon

The longitude coordinates of the job location, between -180 and 180 degrees. Required if no job address details are supplied.

-89.955

RouteID

The ID of the route the job is assigned to (corresponds with the 'ID' value of a route import file). If the job's status is 'Assigned' or 'Unassigned', then the job can be removed from the route by setting the RouteID to '0'.

WestTerritorySteelRoute

EarliestArrivalTime

The earliest date and time that the driver can arrive at the job location. Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

10/21/2015 08:00

LatestArrivalTime

The latest date and time that the driver can arrive at the job location. Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

10/21/2015 15:30

ExpectedArrivalTime

The date and time when a driver is expected to arrive at the job location. Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

10/21/2015 08:00

ExpectedDepartureTime

The date and time when a driver is expected to depart from the job location. Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

The Time On Site value for jobs is determined by the time difference between 'ExpectedArrivalTime' and 'ExpectedDepartureTime'. For example, using an 'ExpectedArrivalTime' time of 14:20 and an 'ExpectedDepartureTime' time of 14:45 results in a Time On Site value of 25 minutes.

10/21/2015 08:15

JobTypeID

The name of the job type. Required, and accepted in the form of 'Stop', or matching the job type configured from the Job Types screen.

Stop

Priority

The priority of the job. Accepted values are 'Lowest', 'BelowNormal', 'Normal', 'AboveNormal' and 'Highest'. These correspond with the job priority values (when created using the Create Jobs dialog) of 'Lowest', 'Low', 'Normal', 'High' and 'Highest'. Higher priority jobs are typically routed ahead of lower priority jobs when building routes.

AboveNormal

RequiredDriverTags

Tags that specify the driver skills or properties that must be present to complete the job. These tags must already exist: they cannot be created during import.

CraneOperator

RequiredVehicleTags

Tags that specify the vehicle capabilities or properties needed to complete the job, such as refrigeration, a boom arm, or PTO connections. These tags must already exist: they cannot be created during import.

PTO

Tags

Tags that have been attached to the job. These tags must already exist: they cannot be created during import.

Heavy goods

Notes

Notes that have been attached to the job.

Use side entrance

Code

An arbitrary code associated with the job. These codes are typically used for filtering operations, together with tags.

3256

Route Import File Structure

Field

Purpose

Example

ID

A unique identifier used to represent the route. Job import files should specify a 'RouteID'  that corresponds with this value when assigning a job to a given route. If this value matches an existing route ID, the import will be ignored (that is, the existing route will not be overwritten).

192759

Name

An arbitrary but typically descriptive name used to represent the route, such as the territory, driver or customer names.

Team 1 Morning Route

DriverID

The ID of the driver assigned to this route. Maps to 'nickname'. Required if no UnitID is supplied.

Philadelphia Driver 5

UnitID

The ID of the unit assigned to this route. Corresponds with either the unit serial number (available from the Hardware tab of the Vehicle Details pop-up), or the unit name. Required if no DriverID is supplied.

veh_5db0a834-95hg-4bda-a6f7-b8b1c69871b5

TerritoryID

The ID (name) of the territory in which the route will occur. Maps to 'name'. This is the territory that the job marker locations in the route are assigned to.

Philadelphia

StartTime

The time of day when the route is scheduled to start (when the vehicle is to leave the depot). Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

10/21/2018 08:00

EndTime

he time of day when the route is scheduled to end (when the vehicle is to return to the depot). Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

10/21/2018 17:00

EarliestStartTime

The earliest time of day that the route can start (when the vehicle leaves the depot). A route's shift is 'Variable' if both the 'EarliestStartTime' and 'LatestEndTime' are specified (i.e. the shift's beginning and ending times might vary within the window specified). If only the 'EarliestStartTime' is specified, then the route's shift is 'Fixed' (i.e. the shift begins at the 'EarliestStartTime'). Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

10/21/2018 06:00

LatestEndTime

For variable schedules, the latest time of day that the route can end (when the vehicle is to return to the depot). Accepted as M/d/yyyy H:mm, d/M/yyyy H:mm or yyyy/M/d H:mm format. The associated time zone is selected from the mapping window.

10/21/2018 22:00

NormalShiftDuration

The duration for which the driver can be paid at the normal rate, before triggering overtime. Typically accepted as either hours or minutes; the chosen time interval is selectable from the mapping window.

8

MaximumShiftDuration

The maximum duration that the driver's shift can be scheduled for, including overtime. Typically accepted as either hours or minutes; the chosen time interval is selectable from the mapping window.

10

File Mapping

Mappings specify which columns of job or route data will be imported from the import file, and how these columns map to the available job or route properties.

If you already have an import profile that describes the columns to import and how they are mapped, open the Mapping drop-down menu and choose it. When you select the import profile, the mappings are automatically added and you can click Import to import the jobs or routes immediately.

 

1.Open the Mapping drop-down menu, and then select the Create new... option. The Create New Mapping dialog opens:

Job Import Mapping Screen
 
2.Open the drop-down menus beside the job or route properties and select the names of the columns containing the corresponding properties in the import file, if they exist. Where necessary, choose the appropriate time format and time zone for timestamp values in your file.

 
3.Click Save when you have completed configuring your file map. The Save Mapping dialog opens.

 
4.Type a suitable name for your new mapping profile into the 'Name' field, and then click Save. The profile is saved, and can be selected in future from the Mapping drop-down menu.

To edit an existing mapping profile, select it from the Mapping drop-down menu, and then click the Edit mapping... button. The Editing dialog opens. Manually edit the columns associated with each job or route property. When you have finished editing, click the Save button to overwrite the current profile settings, or click Save As to save the edited profile with a new name. Click Delete to permanently remove the profile.