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)
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 |
|---|---|---|
| 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.
Sample Spreadsheets
Click on the links below to download sample spreadsheet files.