Understanding and Using Spreadsheets

Understanding Spreadsheet Files

Route4Me’s route planning engine requires users to input their data into the system for processing it and outputting the most efficient and optimized routes. Uploading spreadsheets is the fastest and the most effective method to get you started planning your routes.

A spreadsheet file is made of organized rows and columns. Spreadsheets can be exported from other sources, or they can be created using Microsoft Excel, Google Spreadsheets, OpenOffice Calc and other similar pieces of software. Route4Me currently supports CSV, XLS and XLSX files for upload. For the most accurate results, we recommend using spreadsheets in CSV format.

Understanding and Using Spreadsheets

 

Formatting Spreadsheet Files for Route4Me Upload

Route4Me supports spreadsheet files containing addresses and the data associated with various fixed attributes that are assigned to the corresponding addresses. Route4Me also allows you to import custom data that can be added to the corresponding sections of the system. Please note that Route4Me’s uploader does not support spreadsheet files containing macros, formulas or filters. If your file contains multiple sheets, the system will prompt you to select only one sheet for the upload.

 

For a spreadsheet to be successfully uploaded and processed, it must contain a header row and data rows. The header defines the type of information you’re adding, while the corresponding data rows contain the actual information you input into the system.

Using Spreadsheets

 

Depending on the import information, there can be two categories of rows and associated columns:

  • – Reserved attributes columns contain information that is processed by the system and automatically mapped to the corresponding attributes of the associated addresses. Reserved attributes can be used as headers. Please see the table of Route4Me’s reserved attributes in the Spreadsheet Glossary below;
  • – Custom columns are the columns where you can include additional data that is not encompassed within the list of reserved attributes. Custom columns are processed by the system and added to the Custom Data sections of the associated addresses.

Using Spreadsheets

The only mandatory column is the “Address” column, where you should include: the street name and number, city, state and zip code. Alternatively, you can only input the street name and number into the “Address” column, then enter separate columns for the city, state and zip code. The address format may differ from one country to another, so make sure that you write it in the manner that’s specific to your region.

 

Uploading a Spreadsheet

To upload a spreadsheet, first, prepare the file in accordance with the requirements mentioned above. Please see the list of all available Route4Me reserved attributes in the Spreadsheet Glossary below.

Understanding and Using Spreadsheets

 

When a spreadsheet is uploaded, Route4Me processes the data and displays it in the corresponding window. For the system to process the data automatically, select the “Auto (attempt detection)” option.

Understanding and Using Spreadsheets

 

Once the file is uploaded and processed, the imported data is added to the “Order” sections of the associated addresses/stops on the route.

Understanding and Using Spreadsheets

 

Spreadsheet Glossary

You can add columns with various reserved attributes to meet your routing and business needs. The Spreadsheet Glossary provides a detailed list of all supported attributes. Please make sure that the column header you add to your spreadsheet files matches one of the valid reserved attributes in the table below.

Reserved AttributeDescriptionValid Headers
emailA valid e-mail address assigned to the corresponding address/stop.email, e-mail, email_address, emailaddress, customer_email, order_email, address_email
aliasA custom name/alias assigned to the corresponding address/stop.alias, customeralias, customer, name, nickname, address_alias
addressThe main address of the corresponding stop.address, address 1, address_1, address1, addr1, destination_name, customer_address, street address, street_address, street address 1, street_address_1
address2The secondary address of the corresponding stop which is not used for geocoding (suite or apartment number).address 2, address2, address_1, addr2, street address2, street_address2, street address 2, street_address2
cityThe city in which the corresponding address is located.city, city_name, address_city, cityname, town, city (shipping)
stateThe state in which the corresponding address is located.state, state_name, address_state
countryThe country in which the corresponding address is located.country, country_name, countryname, nation
zipThe zip code of the area in which the corresponding address is located.zip, zipcode, addresszip, addresszipcode, address_zip, postal code, postal, postal_code, postcode, postal code (shipping)
phoneThe phone number of the contact/prospect associated with the corresponding address/stop.phone, phonenumber, telephone, phone_1, address_phone, phone 1
first_nameThe first name of the contact/prospect associated with the corresponding address/stop.first_name, firstname, first name, first, customer_first_name
last_nameThe last name of the contact/prospect associated with the corresponding address/stop.last_name, lastname, last name, last, customer_last_name
account_noThe account number associated with the corresponding address/stop.account_no, account, accountnumber, account_number, account no
order_noThe order number for the corresponding address/stop.order, order_no
sequence_noThe sequence number of the corresponding address/stop on the route.seq, sequence_no, sequence_number, sequenceno
priorityVisitation priority of the corresponding address/stop where 1 is the highest priority, and 255 is the lowest priority.priority, importance
original_route_idThe identification number of the route to which the corresponding address/stop is assigned.route, route_no, route_id, routeid, original_route_id
customer_poThe customer purchase order for the corresponding address/stop.customer_po, customerpo, customer_purchase_order, purchase_order, po_number, cust po
reference_noThe reference number for the corresponding address/stop.reference_no, ref_no, reference_number, ref_number, ref num
invoice_noThe invoice number associated with the corresponding address/stop.invoice_no, invoice, invoice_number
costThe total cost of visiting and servicing the corresponding address/stop.cost, total_cost, order_cost, stop_cost
revenueThe total revenue that is expected to be generated from the corresponding address/stop.revenue, income, earnings
cubeThe cubic volume of the cargo being delivered/picked up at the corresponding address/stop.dimensions, cube, cubic, volume
piecesThe number of pieces/items being delivered/picked at the corresponding address/stop.pcs, pieces
weightThe numeric weight that is being delivered/picked up at the corresponding address/stop.weight, pounds, kilograms
service_timeThe expected amount of time a driver should spend on servicing the corresponding address/stop (integer minutes or HH:MM:SS).time, service_time, minutes, servicetime, service time
time_window_startThe beginning of the time window (working hours) during which the corresponding address/stop can be visited and serviced. (HH:MM:SS, military time).time_window_start, window_start, window_open, start_time, time_start, time window start, time window start 1, time_window_start_1
time_window_endThe end of the time window (working hours) during which the corresponding address/stop can be visited and serviced. (HH:MM:SS, military time).time_window_end, time_window_close, window_end, window_close, end_time, time_end, time window end, time window end 1, time_window_end_1
time_window_start_2The beginning of the second time window (working hours) during which the corresponding address/stop can be visited and serviced. (HH:MM:SS, military time).time_window_start_2, window_start_2, window_open_2, start_time_2, time_start_2, time window start 2, time_window_start_2
time_window_end_2The end of the second time window (working hours) during which the corresponding address/stop can be visited and serviced. (HH:MM:SS, military time).time_window_end_2, time_window_close_2, window_end_2, window_close_2, end_time_2, time_end_2, time window end 2, time_window_start_2
latitudeThe latitude coordinates (decimal form, rooftop) of the corresponding address/stop.dst_lat, lat, latitude, address_latitude
longitudeThe longitude coordinates (decimal form, rooftop) of the corresponding address/stop.dst_lng, lng, long, longitude, address_longitude
curbside_latitudeThe curbside latitude coordinates (decimal form, curbside) of the corresponding address/stop.curb_lat, curb_latitude, curbside_latitude, curbside_lat
curbside_longitudeThe curbside longitude coordinates (decimal form, curbside) of the corresponding address/stop.curb_lng, curb_longitude, curbside_longitude, curbside_lng
depotMarks the corresponding address/stop as a depot (“1” is a depot and “0” is an address/stop).is_depot, depot
address_stop_typeThe type of the corresponding address/stop (PICKUP, DELIVERY, BREAK, MEETUP, VISIT).address_type, address_stop_type, stop_type, delivery_type, location_type, type
route_nameWhen planning multiple routes with different depots from a single spreadsheet, the “route_names” are the names that are assigned to the planned routes based on their depots.route_name, depot_route_name
groupThe name of the Address Book group that is associated with the corresponding address/stop.group, address_group, destination_group

 

Spreadsheet Samples

Click on the links below to download sample spreadsheet files.

Basic Route Sample File
basic_route.xlsx

Address Book Sample File
address_book_sample.xlsx

Time Window and Service Time Constraints Route Sample File
time_window_and_service_time_constraints_route.xlsx

Capacity Constraints Route
capacity_constraints_route.xlsx

Multiple Depot Route
multiple_depot_route.xlsx

When preparing your files, please consider the following limitations:

  • – Planning a single driver route: maximum 1,000 rows/addresses;
  • – Planning a multiple drivers route: maximum 500 rows/addresses;
  • – Uploading data to the Address book: maximum 10,000 rows / addresses (recommended: 1,000 rows).
Was this article helpful?
Still can't find what you're looking for? Contact us