Using Spreadsheets

Understanding Spreadsheet Files

Route4Me’s route planning engine requires users to input their address data into the system. This can be done manually, by typing the data in directly, by importing data from a third party application (such as Google Drive, Dropbox or Xero) or by uploading data using a spreadsheet file.

Spreadsheet upload provides the fastest and most effective method to get you started planning your routes. It is also the best way you can make use of some of Route4Me’s advanced features.

A spreadsheet file is made of cells of an array, organized in 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 software. Route4Me currently supports .CSV, .XLS and .XLSX files for upload. For best results, we recommend using .CSV spreadsheets.

Formatting Spreadsheet Files for Route4Me Upload

Route4Me supports spreadsheet files containing raw data containing addresses, data associated with various fixed attributes and custom information.

IMPORTANT:The Route4Me 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.

Your spreadsheet files must contain a header row and data rows. The table header defines the type of information you’re adding, while the corresponding data fields contain the actual information you input into the system. When preparing your file, 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)

Quality route planning through Route4Me's spreadsheet upload

Columns can be split into two categories, depending on the information they contain: reserved attributes columns and custom columns.

Reserved attributes columns contain information that will be mapped to one of Route4Me’s attributes.

The only required 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 differs from one country to another, so make sure you write it in the manner that’s specific to your region.

You can add various other reserved attribute columns to meet your routing needs. You can find a detailed list of all supported attributes below. Make sure that the column header you put into your spreadsheet file precisely matches one of the valid headers in the array corresponding to the reserved attribute.

Reserved Attribute Description Valid Headers
email A valid e-mail address assigned to this stop email, e-mail, email_address, emailaddress, customer_email, order_email, address_email
alias Alias assigned to the address alias, customeralias, customer, name, nickname, address_alias
address The destination Address Line 1 address, address 1, address_1, address1, addr1, destination_name, customer_address, street address, street_address, street address 1, street_address_1
address2 The destination Address Line 2 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
city The city the address is located in city, city_name, address_city, cityname, town, city (shipping)
state The state the address is located in state, state_name, address_state
country The country the address is located in country, country_name, countryname, nation
zip The zip code the address is located in zip, zipcode, addresszip, addresszipcode, address_zip, postal code, postal, postal_code, postcode, postal code (shipping)
phone The phone number for the address phone, phonenumber, telephone, phone_1, address_phone, phone 1
first_name The first name of the contact person at the receiving address first_name, firstname, first name, first, customer_first_name
last_name The last name of the contact person at the receiving address last_name, lastname, last name, last, customer_last_name
account_no The account number associated with the address account_no, account, accountnumber, account_number, account no
order_no The order number for the address order, order_no,
sequence_no The address sequence number inside the route seq, sequence_no, sequence_number, sequenceno
priority Visitation priority for the address: Priority: 1 is the highest, priority, 2 lower, etc. priority, importance
original_route_id The route id (number) to which the address is assigned route, route_no, route_id, routeid, original_route_id
customer_po The customer purchase order for the address customer_po, customerpo, customer_purchase_order, purchase_order, po_number, cust po
reference_no The reference number for the address reference_no, ref_no, reference_number, ref_number, ref num
invoice_no The invoice number associated with the address invoice_no, invoice, invoice_number
cost The order cost for the address cost, total_cost, order_cost, stop_cost
revenue The total revenue for the address revenue, income, earnings
cube The cubic volume of the cargo being delivered/picked up at the address dimensions, cube, cubic, volume
pieces Number of pieces to be delivered to the address pcs, pieces
weight The numeric weight that is being delivered/picked up at this stop weight, pounds, kilograms
service_time The expected amount of time the driver will be at the stop (integer minutes or HH:MM:SS) time, service_time, minutes, servicetime, service time
time_window_start The requested estimated beginning of the first time window (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_end The requested estimated end of the first time window (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_2 The requested estimated beginning of the second time window (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_2 The requested estimated end of the first time window (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
latitude The destination’s latitude coordinates (decimal form, rooftop) dst_lat, lat, latitude, address_latitude
longitude The destination’s longitude coordinates (decimal form, rooftop) dst_lng, lng, long, longitude, address_longitude
curbside_latitude The destination’s latitude coordinates (decimal form, curbside) curb_lat, curb_latitude, curbside_latitude, curbside_lat
curbside_longitude The destination’s longitude coordinates (decimal form, curbside) curb_lng, curb_longitude, curbside_longitude, curbside_lng
depot The destination is a depot (0 or 1) is_depot, depot
address_stop_type The type of stop that this is (PICKUP, DELIVERY, BREAK, MEETUP, VISIT) address_type, address_stop_type, stop_type, delivery_type, location_type, type
route_name Applies to depots: a text that is included in the names of generated routes that involve the depot route_name, depot_route_name
group Destination customer group group, address_group, destination_group

Custom columns are columns where you can include any additional data not encompassed within the list of reserved attributes. You can add an unlimited number of custom columns.

An easy route planner with Route4Me's easy use of spreadsheets

Sample Spreadsheets

Click on the links below to download sample spreadsheet files.

Basic Route Sample File

Address Book Sample File

Time Window and Service Time Constraints Route Sample File

Capacity Constraints Route

Multiple Depot Route

Was this article helpful?
Still can't find what you're looking for? Contact us