Spreadsheets Formatting Requirements – CSV, XLS, XLSX
Route4Me enables you to plan and optimize last-mile routes with hundreds of thousands of addresses and locations. For your convenience and maximum efficiency, you can easily import all your addresses, customer details, order information, and any other route data entries in a single spreadsheet instead of adding them manually. Furthermore, formatting Route4Me spreadsheets is as simple as adding column names and populating them with your data.
In this guide, you can learn about formatting Route4Me spreadsheets, supported spreadsheet file types, available spreadsheet column names, and more. Additionally, you can download a spreadsheet template, populate it with your own data, and use it for optimizing routes, importing customer addresses into your Synced Address Book, etc.
Table of Contents
Route4Me Spreadsheets Formatting Rules and Requirements
A Route4Me spreadsheet is made of organized Columns and Rows. To create a column, simply add the preferred column name to the header (first row) of your spreadsheet and then populate its following rows with the respective data. For example, adding “Address” to the header (first row) of the spreadsheet creates the Address column that can be populated with an actual address on each of its following rows. Similarly, you can add multiple columns and populate their rows with the respective data. Accordingly, all data added in the same row with an address is assigned to this address.
Equally important, the only mandatory column for creating a Route4Me spreadsheet is the Address column, and the rest of the columns can contain the data you want to assign to imported addresses. A valid address in the Address column should include street name and number, city, state, ZIP code, and country. If you add a partial address, Route4Me will automatically suggest one or multiple geocoded versions. Alternatively, you can input the street name and number into the Address column, and then add separate columns for the City, State, ZIP Code, and Country.
Route4Me supports a specific set of Software Entities and Attributes that can be associated with addresses. For example, address alias, customer email, order number, order weight, etc. To attach specific information to your addresses that can’t be added through Route4Me’s reserved software entities and attributes, you can easily attach this information as Custom Data.
To do so, simply add the preferred Custom Column Names to your spreadsheet and then populate custom column rows with the preferred information next to each address. When uploading your spreadsheet, Route4Me will automatically recognize your custom columns. Additionally, learn more about adding and using Route4Me’s Custom Data.
Supported Spreadsheet File Types and Extensions
Route4Me supports the following file types that can be used to import data into the system: CSV (comma-separated values), XLS and XLSX (Microsoft Excel). These spreadsheet file types can be created and exported using Microsoft Excel, Google Sheets and Google Workspace, OpenOffice Calc, Apple Numbers, Zoho Sheet, and other similar software types. For the fastest upload and data processing speed, we recommend using spreadsheets with the CSV file extension.
If your file contains multiple sheets or tabs, you will be prompted to select only one sheet or tab for the upload. Additionally, Route4Me doesn’t support spreadsheets that contain macros, formulas, or filters.
Equally important, when exporting an Apple Numbers file (macOS) to Excel, remove the first row from the Numbers file before exporting it to ensure that the header row headings remain on the first row in the exported Excel file.
Download and Use Route4Me Spreadsheet Template
For your convenience, you can download Route4Me’s CSV Spreadsheet Template, populate it with your own information and use it for importing data. The template contains a set of columns that can be used for optimizing routes, importing addresses into your Route4Me Address Book, etc. Accordingly, you can add more columns and Custom Data to the downloaded template based on your routing and business needs. Furthermore, glossaries with the available column names for route optimization and Address Book import are provided further in this guide.
Download Route4Me Spreadsheet Template
Spreadsheets Upload and Data Verification
When you upload or import a spreadsheet into the system, Route4Me automatically verifies, validates, and matches column names in the header of your spreadsheet to the respective entities and attributes in the system. Accordingly, the header of each column should correspond to the data in this column. Equally important, Custom Data columns are verified and displayed with the “Custom” column name and the column name from the uploaded spreadsheet in brackets.
Additionally, you can manually match data columns from the uploaded spreadsheet to the system’s reserved entities and attributes in case certain column names don’t match the data in them. To do so, click on the preferred column name and then select the correct column name from the drop-down list.
Moreover, you can select different “Upload Encoding” options depending on your spreadsheet formatting. By default, Route4Me uses the Auto (attempt detection) option that automatically verifies and validates data in uploaded spreadsheets.
Route Planning and Optimization Spreadsheets Glossary
To make your route planning and optimization process faster and more efficient, Route4Me lets you import spreadsheets with thousands of addresses and additional route data instead of adding each entry manually. Specifically, you can upload route spreadsheets from your device or import route spreadsheets from Google Drive, Dropbox, and other cloud services.
In your route spreadsheets, you can include stop addresses, order information, customer details, time requirements, route optimization constraints, etc. Additionally, you can include various types of Route Custom Data that don’t fit in Route4Me’s reserved software entities and attributes. Accordingly, once the routes are planned and optimized, you can easily access the data uploaded from your spreadsheets in your routes using Route4Me’s Route Editor, Routes Map, etc.
To include specific route data, optimization constraints, order information, customer details, etc., in your spreadsheet, add the corresponding column names into the header of the spreadsheet. For example, to include aliases for addresses, add the Alias column by using any of the following column names: alias, customeralias, customer, name, nickname, address_alias.
Below, you can find the list of the available column names that can be added to your spreadsheets depending on what route data you want to upload. Route4Me Entities are the names of the Route4Me system entities and attributes that can be found in the software. Accordingly, Valid Column Names (Headers) are the available column names that can be added to the header of your spreadsheets. You can pick one preferred Valid Column Name to create the respective column in the spreadsheets.
| Route4Me Entity | Entity / Attribute Description | Valid Column Names (Headers) | |
|---|---|---|---|
| ↓ ADDRESS DETAILS | |||
| Alias | Сustom alias or name of the route stop (address). You can assign aliases to route stops (addresses) for faster and easier route search or other operating requirements. | alias, customeralias, customer, name, nickname, address_alias | |
| Address | Primary address of the route stop. The Address column is the only mandatory spreadsheet column for planning and optimizing routes. Accordingly, other columns can contain the data you want to assign to imported addresses. | address, address 1, address_1, address1, addr1, destination_name, customer_address, street address, street_address, street address 1, street_address_1, street address 1 (shipping), street address 1(shipping) | |
| City | The City part of the stop’s address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | city, city_name, address_city, cityname, town, city (shipping) | |
| State | The State part of the stop’s address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | state, state_name, address_state | |
| ZIP Code | The ZIP Code part of the stop’s address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | zip, zipcode, addresszip, addresszipcode, address_zip, postal code, postal, postal_code, postcode, postal code (shipping) | |
| Country | The Country part of the stop’s address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | country, country_name, countryname, nation | |
| Latitude | Rooftop latitude coordinates (decimal form) of the route stop (address). Instead of adding the address of a stop, you can add the stop’s latitude and longitude coordinates. | dst_lat, lat, latitude, address_latitude, cached_lat, destination latitude | |
| Longitude | Rooftop longitude coordinates (decimal form) of the route stop (address). Instead of adding the address of a stop, you can add the stop’s latitude and longitude coordinates. | dst_lng, lng, long, longitude, address_longitude, cached_lng, destination longitude | |
| Curbside Latitude | Curbside latitude coordinates (decimal form) of the route stop (address). Instead of adding the address of a stop, you can add the stop’s curbside latitude and longitude coordinates for curbside routing. | curb_lat, curb_latitude, curbside_latitude, curbside_lat, destination’s curbside latitude | |
| Curbside Longitude | Curbside longitude coordinates (decimal form) of the route stop (address). Instead of adding the address of a stop, you can add the stop’s curbside latitude and longitude coordinates for curbside routing. | curb_lng, curb_longitude, curbside_longitude, curbside_lng, destination’s curbside longitude | |
| Stop Type | You can add the following route stop types to your routes: PICKUP, DELIVERY, BREAK, SERVICE, MEETUP, and VISIT. Accordingly, Route4Me’s route optimization engine accounts for each stop type to optimize the most efficient stops sequences and the fastest route directions. NOTE: When adding stop types to your spreadsheets, make sure that they are added in upper case only. | address stop type, address_type, address_stop_type, stop_type, delivery_type, location_type, type | |
| ↓ ORDER DETAILS | |||
| Priority | Visitation priority of the route stop (address). The highest stop priority is 1 and the lowest stop priority is 999999. Accordingly, route stops (addresses) with higher priority are visited earlier on the route. | priority, importance | |
| Order Number | Custom order number of the order associated with the route stop (address). | order, order_no, order_number, orderno, order no | |
| Reference Number | Custom reference number of the order associated with the route stop (address). | reference no, reference_no, ref_no, reference_number, ref_number, ref num | |
| Invoice Number | Custom invoice number of the order associated with the route stop (address). | invoice no, invoice_no, invoice, invoice_number | |
| Weight | Weight route optimization constraint. Total weight expected to be delivered to or picked up at the route stop (address). | weight, pounds, kilograms | |
| Pieces | Pieces route optimization constraint. Total number of items or pieces expected to be delivered to or picked up at the route stop (address). | pcs, pieces | |
| Cube | Cube route optimization constraint. Total cubic volume expected to be delivered to or picked up at the route stop (address). | dimensions, cube, cubic, volume | |
| Revenue | Revenue route optimization constraint. Total revenue expected to be generated from the route stop (address). | revenue, income, earnings | |
| ↓ CUSTOMER DETAILS | |||
| First Name | First name of the contact or customer associated with the route stop (address). | first_name, firstname, first name, first, customer_first_name | |
| Last Name | Last name of the contact or customer associated with the route stop (address). | last_name, lastname, last name, last, customer_last_name | |
| Email address of the contact or customer associated with the route stop (address). | email, e-mail, email_address, emailaddress, customer_email, order_email, address_email | ||
| Phone Number | Phone number of the contact or customer associated with the route stop (address). | phone, phonenumber, telephone, phone_1, address_phone, phone 1, address_phone_number | |
| Customer PO | Purchase order (PO) of the contact or customer associated with the route stop (address). | customer po, customer_po, customerpo, customer_purchase_order, purchase_order, po_number, cust po | |
| Group | Custom ID of the Address Book Group associated with the route stop (address). | group, address_group, destination_group | |
| ↓ TIME DETAILS | |||
| Service Time | Service Time (integer minutes or HH:MM:SS) required to service the route stop (address). | time, service_time, minutes, servicetime, service time | |
| Time Window Start | Start time of the time window (working hours) when the route stop can be visited and serviced (Day 1, + HH:MM:SS or military time. For example: “Day 1, 9:00 AM”). Learn more about Route4Me’s Time Windows Route Optimization Constraint. | time_window_start, window_start, window_open, start_time, time_start, time window start, time window start 1, time_window_start_1, local_time_window_start, local_time_window_start_1 | |
| Time Window End | End time of the time window (working hours) when the route stop can be visited and serviced (Day 1, + HH:MM:SS or military time. For example: “Day 1, 5:00 PM”). | 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, local_time_window_end, local_time_window_end_1 | |
| Time Window Start 2 | Start time of the Second Time Window (working hours) when the route stop can be visited and serviced (Day 2, + HH:MM:SS or military time. For example: “Day 2, 9:00 AM”). | time_window_start_2, window_start_2, window_open_2, start_time_2, time_start_2, time window start 2, time_window_start_2, local_time_window_start_2 | |
| Time Window End 2 | End time of the Second Time Window (working hours) when the route stop can be visited and serviced (Day 2, + HH:MM:SS or military time. For example: “Day 2, 5:00 PM”). | 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, local_time_window_end_2 | |
| ↓ ADDITIONAL ATTRIBUTES | |||
| Stop Sequence Number | Sequence number of the stop (address) on the optimized route. For example, the route stop (address) with the sequence number 5 is placed as the 5th stop on the optimized route. Accordingly, the Stop Sequence Number of each stop affects the final stops sequence of the optimized route. | seq, sequence_no, sequence_number, sequenceno, sequence, stopnumber, stop_number | |
| Cost | Total cost of visiting and servicing the route stop (address). Accordingly, when the cost of each stop on the route is aggregated, you can check the total cost of the entire route in the Route Summary Table or Route Manifest. | cost, total_cost, order_cost, stop_cost | |
| Depot | Used for Multiple Depot Routing. Mark depot addresses as 1 and regular addresses as 0 in the Depot column in your spreadsheet. | is_depot, depot | |
| Original Route ID | When optimizing Multiple Depot Routes, you can include specific stops (addresses) in the same routes. To do so, add the same custom ID next to the stops (addresses) you want to include in the same route. | original route id, route, route_no, route_id, routeid, original_route_id | |
| Route Name | When optimizing Multiple Depot Routes, you can assign different names to routes that start from different depots. To do so, add the preferred route names next to their corresponding depots. | route name, route_name, depot_route_name | |
| Required Skills | Skills or attributes required to visit and service the route stop (address). Learn more about Driver Skills Route Planning and Optimization. | required_skills, skills, skill, tags, tag | |
| Pick-Up | Pick-up stop (address) in the Pick-Up and Drop-Off route stop pair. | pickup, pick_up, pick up, pick | |
| Drop-Off | Drop-off stop (address) in the Pick-Up and Drop-Off route stop pair. | dropoff, drop_off, drop off, drop | |
| Joint | Used for Joint Pick-Up and Drop-Off Routing, where each pick-up stop is always followed by its corresponding drop-off stop, without any stops in between. | joint | |
NOTE: To use specific columns, you need to have the respective Add-Ons activated on your account. Check your subscription to see if the preferred Add-Ons are activated on your account.
Address Book Spreadsheets Glossary
Your Route4Me Synced Address Book is a centralized database where you store and manage thousands of customer addresses. Additionally, you can use your Address Book to plan and optimize routes, create address territories, insert addresses into best routes, and more. Instead of adding each entry manually, you easily import thousands of addresses using spreadsheets.
In addition to the address, each entry in your Route4Me Address Book can have additional details, such as order information, customer details, visitation hours, and more. Below, you can find the list of what details you can attach to your addresses when importing spreadsheets into your Address Book.
To include addresses, order information, customer details, etc., in your spreadsheet, add the corresponding column names to the spreadsheet header. For example, to add customer emails to addresses in your spreadsheet, add the Email column using any of the following column names: email, e-mail, email_address, emailaddress, customer_email, order_email, address_email.
Below, you can find the list of available column names that can be added to your spreadsheets based on your unique business and routing needs. Address Attributes are the names of the Route4Me system entities (address fields) that can be found in the Address Book. Accordingly, Valid Column Names (Headers) are the available column names that can be added to the header of your spreadsheets. You can pick one preferred Valid Column Name to create the respective column in your spreadsheets.
| Address Attribute | Attribute Description | Valid Column Names (Headers) | |
|---|---|---|---|
| ↓ ADDRESS DETAILS | |||
| Alias | Сustom alias or name of the address. You can assign aliases to addresses for faster and easier search or other operating requirements. | alias, customeralias, customer, name, nickname, address_alias | |
| Address | Address of the Address Book entry (customer address, business location, visit address, etc.). The Address column is the only mandatory spreadsheet column for optimizing routes, creating Address Book addresses, etc. Accordingly, other columns can contain the data you want to assign to imported addresses. | address, address 1, address_1, address1, addr1, destination_name, customer_address, street address, street_address, street address 1, street_address_1, street address 1 (shipping), street address 1(shipping) | |
| City | The City part of the address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | city, city_name, address_city, cityname, town, city (shipping) | |
| State | The State part of the address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | state, state_name, address_state | |
| ZIP Code | The ZIP Code part of the address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | zip, zipcode, addresszip, addresszipcode, address_zip, postal code, postal, postal_code, postcode, postal code (shipping) | |
| Country | The Country part of the address. You can add full addresses or split them into multiple columns: City, State, ZIP Code, and Country. | country, country_name, countryname, nation | |
| Latitude | Rooftop latitude coordinates (decimal form) of the location. Instead of adding the address of a location, you can add its latitude and longitude coordinates. | dst_lat, lat, latitude, address_latitude, cached_lat, destination latitude | |
| Longitude | Rooftop longitude coordinates (decimal form) of the location. Instead of adding the address of a location, you can add its latitude and longitude coordinates. | dst_lng, lng, long, longitude, address_longitude, cached_lng, destination longitude | |
| Curbside Latitude | Curbside latitude coordinates (decimal form) of the location. Instead of adding the address of a location, you can add its curbside latitude and longitude coordinates for curbside routing. | curb_lat, curb_latitude, curbside_latitude, curbside_lat, destination’s curbside latitude | |
| Curbside Longitude | Curbside longitude coordinates (decimal form) of the location. Instead of adding the address of a location, you can add its curbside latitude and longitude coordinates for curbside routing. | curb_lng, curb_longitude, curbside_longitude, curbside_lng, destination’s curbside longitude | |
| Color | Color code of the address. You can color code addresses in your Address Book for Advanced Search Groups filters, indicating particular sales stages, client statuses, location types, visitation priorities, and more. To do so, add preferred colors in the Color column as words (i.e., green, red, white, black, etc.). | color, address_color | |
| Icon | Custom icon of the address. You can assign different icons (emojis) to your addresses for your specific business and operating needs. To assign icons to addresses, add icons (emojis) as HTML entities in the Icon column next to addresses in your spreadsheet. | address_icon, icon | |
| ↓ LOCATION DETAILS | |||
| First Name | First name of the contact or customer associated with the address. | first_name, firstname, first name, first, customer_first_name | |
| Last Name | Last name of the contact or customer associated with the address. | last_name, lastname, last name, last, customer_last_name | |
| Email address of the contact or customer associated with the address. | email, e-mail, email_address, emailaddress, customer_email, order_email, address_email | ||
| Phone Number | Phone number of the contact or customer associated with the address. | phone, phonenumber, telephone, phone_1, address_phone, phone 1, address_phone_number | |
| Customer PO | Purchase order (PO) of the contact or customer associated with the address. | customer po, customer_po, customerpo, customer_purchase_order, purchase_order, po_number, cust po | |
| Group | Assign a custom Address Book Group ID to the address. | group, address_group, destination_group | |
| ↓ BUSINESS DETAILS | |||
| Weight | Weight route optimization constraint. Total weight expected to be delivered to or picked up at the route stop (address). | weight, pounds, kilograms | |
| Pieces | Pieces route optimization constraint. Total number of items or pieces expected to be delivered to or picked up at the route stop (address). | pcs, pieces | |
| Cube | Cube route optimization constraint. Total cubic volume expected to be delivered to or picked up at the route stop (address). | dimensions, cube, cubic, volume | |
| Revenue | Revenue route optimization constraint. Total revenue expected to be generated from the route stop (address). | revenue, income, earnings | |
| Priority | Visitation priority of the route stop (address). The highest stop priority is 1 and the lowest stop priority is 999999. Accordingly, route stops (addresses) with higher priority are visited earlier on the route. | priority, importance | |
| Reference Number | Custom reference number of the order associated with the route stop (address). | reference no, reference_no, ref_no, reference_number, ref_number, ref num | |
| Stop Type | You can add the following stop types (location types) to your Address Book and then use them for optimizing routes: PICKUP, DELIVERY, BREAK, SERVICE, MEETUP, and VISIT. NOTE: When adding stop types to your spreadsheets, make sure that they are added in upper case only. | address stop type, address_type, address_stop_type, stop_type, delivery_type, location_type, type | |
| ↓ TIME WINDOWS | |||
| Time Window Start | Start time of the time window (working hours) when the address can be visited and serviced (Day 1, + HH:MM:SS or military time. For example: “Day 1, 9:00 AM”). Learn more about Route4Me’s Time Windows Route Optimization Constraint. | time_window_start, window_start, window_open, start_time, time_start, time window start, time window start 1, time_window_start_1, local_time_window_start, local_time_window_start_1 | |
| Time Window End | End time of the time window (working hours) when the address can be visited and serviced (Day 1, + HH:MM:SS or military time. For example: “Day 1, 5:00 PM”). | 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, local_time_window_end, local_time_window_end_1 | |
| Time Window Start 2 | Start time of the Second Time Window (working hours) when the address can be visited and serviced (Day 2, + HH:MM:SS or military time. For example: “Day 2, 9:00 AM”). | time_window_start_2, window_start_2, window_open_2, start_time_2, time_start_2, time window start 2, time_window_start_2, local_time_window_start_2 | |
| Time Window End 2 | End time of the Second Time Window (working hours) when the address can be visited and serviced (Day 2, + HH:MM:SS or military time. For example: “Day 2, 5:00 PM”). | 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, local_time_window_end_2 | |
| Timezone | Time zone in which the address operates. When an address has Time Windows, you can add the time zone in which these time windows operate. Accordingly, when using addresses with different Time Windows in different Time Zones, Route4Me automatically accounts for the difference in time and sequences addresses in the most optimal visitation order. | local_timezone_string, timezone, local_timezone | |
| Service Time | Service Time (integer minutes or HH:MM:SS) required to service the address. | time, service_time, minutes, servicetime, service time | |
| ↓ SCHEDULE | |||
| Schedule | Recurring visitation schedule of the address. To add address schedules to your spreadsheet, use the respective JSON code snippets in the Schedule column. | schedule | |
NOTE: To use specific columns, you need to have the respective Add-Ons activated on your account. Check your subscription to see if the preferred Add-Ons are activated on your account.
Visit Route4Me's Marketplace to Check out Various Modules and Add-Ons:
- Route Optimization
Time Windows
- Operations
Advanced Team Management Add-On
