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.
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.
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.
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.
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.
Once the file is uploaded and processed, the imported data is added to the “Order” sections of the associated addresses/stops on the route.
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 Attribute | Description | Valid Headers |
|---|---|---|
| A valid e-mail address assigned to the corresponding address/stop. | email, e-mail, email_address, emailaddress, customer_email, order_email, address_email | |
| alias | A custom name/alias assigned to the corresponding address/stop. | alias, customeralias, customer, name, nickname, address_alias |
| address | The 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 |
| address2 | The 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 |
| city | The city in which the corresponding address is located. | city, city_name, address_city, cityname, town, city (shipping) |
| state | The state in which the corresponding address is located. | state, state_name, address_state |
| country | The country in which the corresponding address is located. | country, country_name, countryname, nation |
| zip | The 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) |
| phone | The phone number of the contact/prospect associated with the corresponding address/stop. | phone, phonenumber, telephone, phone_1, address_phone, phone 1 |
| first_name | The first name of the contact/prospect associated with the corresponding address/stop. | first_name, firstname, first name, first, customer_first_name |
| last_name | The last name of the contact/prospect associated with the corresponding address/stop. | last_name, lastname, last name, last, customer_last_name |
| account_no | The account number associated with the corresponding address/stop. | account_no, account, accountnumber, account_number, account no |
| order_no | The order number for the corresponding address/stop. | order, order_no |
| sequence_no | The sequence number of the corresponding address/stop on the route. | seq, sequence_no, sequence_number, sequenceno |
| priority | Visitation priority of the corresponding address/stop where 1 is the highest priority, and 255 is the lowest priority. | priority, importance |
| original_route_id | The identification number of the route to which the corresponding address/stop is assigned. | route, route_no, route_id, routeid, original_route_id |
| customer_po | The customer purchase order for the corresponding address/stop. | customer_po, customerpo, customer_purchase_order, purchase_order, po_number, cust po |
| reference_no | The reference number for the corresponding address/stop. | reference_no, ref_no, reference_number, ref_number, ref num |
| invoice_no | The invoice number associated with the corresponding address/stop. | invoice_no, invoice, invoice_number |
| cost | The total cost of visiting and servicing the corresponding address/stop. | cost, total_cost, order_cost, stop_cost |
| revenue | The total revenue that is expected to be generated from the corresponding address/stop. | revenue, income, earnings |
| cube | The cubic volume of the cargo being delivered/picked up at the corresponding address/stop. | dimensions, cube, cubic, volume |
| pieces | The number of pieces/items being delivered/picked at the corresponding address/stop. | pcs, pieces |
| weight | The numeric weight that is being delivered/picked up at the corresponding address/stop. | weight, pounds, kilograms |
| service_time | The 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_start | The 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_end | The 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_2 | The 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_2 | The 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 |
| latitude | The latitude coordinates (decimal form, rooftop) of the corresponding address/stop. | dst_lat, lat, latitude, address_latitude |
| longitude | The longitude coordinates (decimal form, rooftop) of the corresponding address/stop. | dst_lng, lng, long, longitude, address_longitude |
| curbside_latitude | The curbside latitude coordinates (decimal form, curbside) of the corresponding address/stop. | curb_lat, curb_latitude, curbside_latitude, curbside_lat |
| curbside_longitude | The curbside longitude coordinates (decimal form, curbside) of the corresponding address/stop. | curb_lng, curb_longitude, curbside_longitude, curbside_lng |
| depot | Marks the corresponding address/stop as a depot (“1” is a depot and “0” is an address/stop). | is_depot, depot |
| address_stop_type | The type of the corresponding address/stop (PICKUP, DELIVERY, BREAK, MEETUP, VISIT). | address_type, address_stop_type, stop_type, delivery_type, location_type, type |
| route_name | When 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 |
| group | The 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).