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.

Route4Me spreadsheet formatting rules, supported file extensions, CSV, XLS, and XLSX route optimization spreadsheets.

 

 

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 Route Planner spreadsheet requirements and formatting for XLS, XLSX, and CSV route optimization files.

 

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.

Route4Me Route Planner spreadsheets formatting with custom data for XLS, XLSX, and CSV route planning files.

 

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.

Click to Download Route4Me’s CSV 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.

Route4Me Route Planner automatically verifies and validates addresses, route, and customer data in uploaded spreadsheet.

 

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.

Route4Me Route Planner shows the address and route data imported in the XLS or CSV spreadsheet.

 

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 EntityEntity / Attribute DescriptionValid 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
AddressPrimary 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)
CityThe 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)
StateThe 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 CodeThe 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)
CountryThe 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
LatitudeRooftop 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
LongitudeRooftop 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 LatitudeCurbside 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 LongitudeCurbside 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 TypeYou 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
PriorityVisitation 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 NumberCustom order number of the order associated with the route stop (address).order, order_no, order_number, orderno, order no
Reference NumberCustom reference number of the order associated with the route stop (address).reference no, reference_no, ref_no, reference_number, ref_number, ref num
Invoice NumberCustom invoice number of the order associated with the route stop (address).invoice no, invoice_no, invoice, invoice_number
WeightWeight route optimization constraint. Total weight expected to be delivered to or picked up at the route stop (address).weight, pounds, kilograms
PiecesPieces route optimization constraint. Total number of items or pieces expected to be delivered to or picked up at the route stop (address).pcs, pieces
CubeCube route optimization constraint. Total cubic volume expected to be delivered to or picked up at the route stop (address).dimensions, cube, cubic, volume
RevenueRevenue route optimization constraint. Total revenue expected to be generated from the route stop (address).revenue, income, earnings
CUSTOMER DETAILS
First NameFirst name of the contact or customer associated with the route stop (address).first_name, firstname, first name, first, customer_first_name
Last NameLast name of the contact or customer associated with the route stop (address).last_name, lastname, last name, last, customer_last_name
EmailEmail 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 NumberPhone 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 POPurchase 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
GroupCustom ID of the Address Book Group associated with the route stop (address).group, address_group, destination_group
TIME DETAILS
Service TimeService Time (integer minutes or HH:MM:SS) required to service the route stop (address).time, service_time, minutes, servicetime, service time
Time Window StartStart 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 EndEnd 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 2Start 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 2End 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 NumberSequence 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
CostTotal 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
DepotUsed 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 IDWhen 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 NameWhen 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 SkillsSkills 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-UpPick-up stop (address) in the Pick-Up and Drop-Off route stop pair.pickup, pick_up, pick up, pick
Drop-OffDrop-off stop (address) in the Pick-Up and Drop-Off route stop pair.dropoff, drop_off, drop off, drop
JointUsed 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.

Route4Me Route Planner Address Book Map shows the address, customer, and order data imported in the XLS or CSV spreadsheet.

 

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 AttributeAttribute DescriptionValid 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
AddressAddress 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)
CityThe 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)
StateThe 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 CodeThe 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)
CountryThe 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
LatitudeRooftop 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
LongitudeRooftop 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 LatitudeCurbside 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 LongitudeCurbside 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
ColorColor 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
IconCustom 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 NameFirst name of the contact or customer associated with the address.first_name, firstname, first name, first, customer_first_name
Last NameLast name of the contact or customer associated with the address.last_name, lastname, last name, last, customer_last_name
EmailEmail address of the contact or customer associated with the address.email, e-mail, email_address, emailaddress, customer_email, order_email, address_email
Phone NumberPhone number of the contact or customer associated with the address.phone, phonenumber, telephone, phone_1, address_phone, phone 1, address_phone_number
Customer POPurchase 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
GroupAssign a custom Address Book Group ID to the address.group, address_group, destination_group
↓ BUSINESS DETAILS
WeightWeight route optimization constraint. Total weight expected to be delivered to or picked up at the route stop (address).weight, pounds, kilograms
PiecesPieces route optimization constraint. Total number of items or pieces expected to be delivered to or picked up at the route stop (address).pcs, pieces
CubeCube route optimization constraint. Total cubic volume expected to be delivered to or picked up at the route stop (address).dimensions, cube, cubic, volume
RevenueRevenue route optimization constraint. Total revenue expected to be generated from the route stop (address).revenue, income, earnings
PriorityVisitation 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 NumberCustom reference number of the order associated with the route stop (address).reference no, reference_no, ref_no, reference_number, ref_number, ref num
Stop TypeYou 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 StartStart 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 EndEnd 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 2Start 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 2End 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
TimezoneTime 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 TimeService Time (integer minutes or HH:MM:SS) required to service the address.time, service_time, minutes, servicetime, service time
↓ SCHEDULE
ScheduleRecurring 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:

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

About Route4Me

Route4Me has over 35,000 customers globally. Route4Me's Android and iPhone mobile apps have been downloaded over 2 million times since 2009. Extremely easy-to-use, Route4Me's apps create optimized routes, synchronize routes to mobile devices, enable communication with drivers and customers, offer turn-by-turn directions, delivery confirmation, and more. Behind the scenes, Route4Me's operational optimization platform combines high-performance algorithms with data science, machine learning, and big data to plan, optimize, and analyze routes of almost any size in real-time.

Leave Your Comment or Question

Your email address and phone number will not be published.