Get your orders into OptimoRoute in bulk by importing a spreadsheet. In this article, we’ll explain the requirements, settings, and how to map data to get your orders into OptimoRoute using spreadsheet import.
Who can use this feature | |
Available for all plans | |
All users | |
All regions |
Video tutorial
Preparing your spreadsheet
The OptimoRoute importer will accept Excel, CSV (comma-separated value), and tab-delimited files. The only other requirement is that there is a column that contains location information so that we can locate your orders on a map.
Examples of location information include:
- Address
- Location ID
- Address Line, Town/City, State/Province, ZIP Code, Country/Region
- Latitude, Longitude
During the import process, you can map the columns from your own spreadsheet to the corresponding columns in OptimoRoute, without needing to adjust to our template.
However as a starting point, or to see the types of columns available, check out our example import template here.
The number of orders you can import at one time is determined by your subscription plan. Contact support if you need your limit raised. |
Importing your spreadsheet
The import process has a few steps to ensure the information is accurately reflected in OptimoRoute.
Step 1: Upload Your File
Once you have your spreadsheet ready to go, it's time to upload.
- Go to the “Plan and Optimize” tab
- Choose the date where you want to import orders
- Click the blue "Import Orders" button below the map
- Either click to upload your file or drag it into the space provided
- Click “Import” if it doesn’t automatically process the file and take you to the next step
In the next window, you'll will be able to map your data columns to fields available in OptimoRoute.
Step 2: Map your columns
OptimoRoute will automatically try to match the columns in your spreadsheet to fields in OptimoRoute. You'll have a chance to check and correct the pre-selected column mapping headers. There is also a preview section where you can review the data in your spreadsheet to ensure we have detected the correct information.
- The mapped columns will show a column header selected.
- Unmapped columns will show as "Not mapped" in light orange in the column header and the data will be greyed out.
- If your spreadsheet has more than one sheet, you can choose the one to import at the bottom of the window.
To change the mapping:
- Click on the dropdown arrow above the column
- Choose an OptimoRoute field to map your information to
- Choose "Not mapped" to unmatch that you don't want imported
- Check the box for “Save this mapping as my default mapping" if you want OptimoRoute to save these import settings.
- Once you’ve finished column mapping, click “Continue” to check your locations.
Advanced Import Settings
In the event your spreadsheet does not have a header in the first row of the sheet, you can configure your import using the advanced import settings.
In the top right corner, you’ll find “Advanced options” where you can:
- Create a header if you don't have one
- Choose which row your data starts on
Choose the correct sheet to import if there’s more than one in your file. You will be able to see up to 5 sheets in a single spreadsheet in OptimoRoute.
If the sheet you want to upload is not one of the first 5 in the file, just make an adjustment to your import file in order to import the correct sheet.
Tip: If you need to import linked pick up and delivery orders, this option is available in the Custom plan. |
Step 3: Location accuracy check
Before completing the import, you'll have a chance to review all the orders that will be imported. During this step, it's important to make sure that your locations are being identified correctly - focus on the ones written in red and yellow. Therefore, the Location Accuracy column should be checked before saving.
Possible statuses are:
Status | Meaning |
---|---|
GPS Coordinates | A new location will be created using supplied latitude and longitude coordinates. |
Geocoded | A new location will be created in the system using the supplied address. |
Matching Location ID | An existing location with the same location ID was found in the system. |
Matching Location | An existing location with the same address and name was found in the system |
Geocoded, Multiple Results Found | A new location will be created using the supplied address, but multiple results have been found for the supplied address – this should be checked before saving to make sure the right location is geocoded. |
Geocoded, Partial Match | A new location will be created using the supplied address, but the found result is only a partial match to the supplied address – this should be checked before saving to make sure the right location is geocoded. |
Not Found | The address could not be found and will not be imported. The location needs to be manually corrected in order to be included in the import. |
Checking and correcting location errors
To check and correct any location warnings :
- Look at the top of the "Review Imported Orders" screen and find the "Import orders summary" screen to see if there are any errors or warnings.
- If there are any, you can sort the "Location Accuracy" column to group them together and make it easier to check.
- For any "Partial Match" or "Multiple Results Found", you can check the "Geocoded Address" column (what Google found for the location) against the "Address" column (what you imported) to see if the difference is anything you need to fix. If not, you can ignore the warning and move on to the next "Location Accuracy" warning
- If you are not sure whether the order is geocoded correctly, you can click the "Edit" link next to the error message, to see where on the map the location is geocoded. Here you can make the necessary changes by editing the address or moving the pin on the map and click "Save".
- Repeat these steps for any errors or warnings
If you do not want to proceed with the import, you can click "Discard" and no locations or orders will be added.
Otherwise, when you are satisfied with all orders, click “Save” to complete the import and save all locations. You will then see your imported orders on the map and in the orders grid on the Plan and Optimize tab.