Putting orders into RouteLogic using an Excel file (.csv)
In addition to entering orders manually, it is possible to put multiple addresses into RouteLogic at once. To do this, you use an Excel file.
There are 2 possibilities:
- You use your own Excel file.
- You use our example file and enter all your own assignments there.
Useful to know
- In this document you will read further what data and in what way must be entered for each assignment.
- Besides importing orders via an Excel file, it is also possible to daily and automatically retrieve all orders from your own system. This can be done through our API. More information can be found when you are logged into your account. Click on your name and then click on "synchronization". This page contains all the information and documentation about our API.
Need help importing your orders or have any other questions?
→ Contact us at https://www.routelogic.nl
Option 1: Import with your own Excel file (.csv)
You want to import orders with your own Excel file. Many systems (crm, web shops, etc) have the possibility to export orders.
In many cases, you can therefore easily get your orders into RouteLogic.
All we need to know from you is which field in your Excel file corresponds to a field in RouteLogic. In other words in your Excel file there is data, where do you want it to go in RouteLogic.
Step 1: Go to "commands" and click the["import](https://app.routelogic.io/tasks/import)" button.
Step 2: You now see a field and button where you can upload your Excel file
Step 3: You will now see the screen below:
Step 4: In this step, you can tell us where to import the data from your Excel file into RouteLogic. Many data we already recognize automatically (e.g. date and an ID), in some cases we do not recognize the data directly and ask e you to indicate after each field which data you want to import.
Explanation of the notifications:
- a green check mark: everything is good and the data will be imported.
- a red cross: data has not been entered (which is a required field) or a field does not contain the correct data (see above which data we expect)
Additional options:
- We only look at the mandatory fields when importing. These are fields we need as a minimum to create routes in RouteLogic for you. If you also want to import optional fields, click "show optional fields" and make sure all fields are set properly there as well.
- In some cases, while importing all your assignments, you may want to immediately give these assignments a fixed value. E.g. the service time on location. Select the option "enter the same value for all jobs" after the field in question and enter a number or value in the open input field after it. For example, 5 (for 5 minutes). While importing, we will now give all your jobs a service time of 5 minutes.
- Another option is "generate an ID for me". Not every Excel file contains a unique ID for every job. Since RouteLogic does need this, you can use this feature to have us generate a unique ID for each job.
Option 2: Import using the sample Excel file (.csv).
You use our sample Excel file to import your assignments. You can download the example file here: download the example file here
For each column we give an explanation below. Which data you put in which column and in which format.
Important
- Do not remove line 1 (the column headings including "external id", "note" "date" etc)
- Delete line 2 and 3, these commands are only for example
- From line 2 on, fill in your own assignments, one line is one assignment.
- Make sure your commands are entered directly below each other and do not leave lines between commands blank.
- Save your Excel file as .csv file (not as .xls or .xlsx)
Explanation meaning of columns
Required fields
The fields listed below are mandatory, without completing these fields we cannot import the job(s).
ID (External id) (mandatory, maximum 255 characters)
- The number used in your system to recognize the order, for example an order number. Based on this field we also make sure no duplicate jobs are loaded, so make sure it is unique to this job. Do you want to update an order? Then you can do so by importing the Excel file again and keeping the same ID. The job will then not be re-imported but the data of the job will be updated.
Date (required, maximum 255 characters)
- The date on which the order must be delivered or picked up.
- It is important to use this date format: Y-m-d
- So a date should be entered as follows (2021-11-02). For November 2, 2021.
- Please note: when saving a CSV file, the date format still sometimes wants to change (more information on the last page)
Street (required, maximum 255 characters)
- The street name of the person who ordered the order without a house number.
House number (required, 255 characters max)
- The house number of the person who ordered the order.
Postcal code (required, 255 characters maximum)
- The postal code of the person who ordered the order.
City (required, maximum 255 characters)
- The city of the person who ordered the job.
If you are exporting a file from your own system know that the order of the columns in the file does not matter for the import. However, it is important that the column names are copied exactly from the sample file including capital letters. The header of the column should really be Street and not for example street.
Optional fields
The fields below are not mandatory.
External reference (required, maximum 255 characters)
- An order number, reference number or other description that you can define yourself. This can be useful for recognizing similar orders in RouteLogic or for searching on them. Think of it as an additional "order number".
Type
- Enter whether it is an order for delivery or pickup.
- For delivery, enter delivery.
- For pickup, enter "pickup".
- For shipment, enter ''shipment'' where you will need to enter pick address separately:
Start time
- The start time of the time period of the order (e.g. 08:00h)
- Always use a colon :
End time
- The end time of the job period (e.g. 19:00h)
- Always use a colon :
Delivery from
- In the mandatory field "date" (see above) you indicate on which date the order must be delivered/picked up. If you enter a date here, this is the latest delivery date. This way you can easily create a date range within which the order must be delivered/picked up. E.g. at "date" you fill in 01-03-2023 and at "delivery from" 10-03-2023. Your order then has a date range from 01-03 to 10-03-2023 so that you can schedule the order in RouteLogic on these dates or in between.
Name (up to 255 characters)
- The name of the person who ordered the order (this can be a first name only or a first name with a middle and last name).
Email (255 characters maximum)
- The e-mail address of the person who ordered the order. If present, used to send an e-mail with a link for track & trace.
Phone number (255 characters maximum)
- The phone number of the person who ordered the order. Used to send an SMS with a track & trace link, if available.
- It is also possible to enter a fixed phone number. This number cannot be used to send a track & trace SMS. However, the driver can use this number to contact the contact person.
Note (maximum 10000 characters)
- An optional note visible only to the driver.
- Make sure this field is only filled with text and not with special characters
External link
- In this field you can place a url to a packing slip, work instruction or other file. The driver will see the link in his driver app and by clicking on it he can open the relevant file directly. It is important that the file is freely accessible (without logging in).
Duration
- The time needed to deliver the job in minutes.
Capacity 1 (2 and 3)
- You can enter up to 3 capacity units in RouteLogic. We take 1 capacity unit into account if you enter only 1 for a vehicle and assignment. If you want us to take more capacity units into account, for example m³ **and** pieces **and** kilograms, we can do that too. In this case, fill in all 3 capacity units for the vehicle and for the job.
- For each vehicle fill in at capacity what is the maximum quantity per unit that fits in this vehicle (e.g. m³, pieces, pallets, kilograms etc). For the vehicle, enter what the maximum m³, pieces and kilograms is.
- Specify per job what the m³, pieces and kilograms are for the job and we will automatically take it into account so it always fits in your vehicles.
- For each vehicle in RouteLogic, a number can be entered for the capacity. For example, in terms of capacity, a vehicle can hold 100 units. If "10" is entered here for one job, it is therefore possible to plan a maximum of 10 such jobs in one vehicle.
- If you want to change the description of the units (standard units for capacity are: pieces, kg, m3) you can do this in your account settings under the tab["vehicle settings](https://app.routelogic.io/settings/account)".
- The units you enter under capacity will then be shown when editing/creating a job and in the expanded statistics you see once you plan a route.
- You can read all about how to work with capacity in this article.
Return require
- Enter a 1 here if this job needs to be retrieved. Enter a 0 here if the job only needs to be taken away (and not picked up again).
Signature require
- Enter a 1 here if this job requires signing. Enter nothing here or a 0, if this job does not require a signature.
House number extension
- Any addition to the house number can be entered here. For example, if the house number is "100a", enter the "a" here. It is also possible to enter the entire house number including extension in the mandatory field House number (see above).
Country
- The country of the person ordering the order.
- Please enter here the 2-letter ISO code of the country (for the Netherlands this is: nl)
Products
- Here it is possible to show the driver the products to be picked up or delivered. For example: 1: table | 2: bar stools
- The separator for different products is: `|`
- The separator for numbers is: `:`, where the number is entered before the separator and the product name after it.
- If no number is entered, the default will be 1.
Codes (only available with the Scanner Add-on).
- In this field, barcodes or QR codes can be added to an order. This makes it possible to give assignments codes and scan them with the scanner in the driver app.
- A code is mandatory if you want to scan, the number and description are not mandatory.
- If you fill in a description then quantity is also mandatory.
- Spaces are not allowed
- The notation of the codes in this field is important for it to work properly. As an example: 3STASW2872017,1,Box1
- 3STASW2872017, the first part is the barcode followed by a comma
- 1, the second part is the number of products (boxes/pallets/products) associated with this code
- Box1 the last part is the description of the product to be scanned. For example "box", "pallet" etc. This can be filled in at your own discretion.
- It is possible to add multiple codes under one job. For this you use the separator: `|`
- Multiple codes will then look like this for example: 3STASW2872017,1,Doos1|3STGPB075061749,1,Doos2
If you want to start using scanning then purchase the Add-on "Scan" for this purpose. Once this Add-on is purchased the example file for importing orders via Excel will also include the "codes" column.
This also applies to the use of the API. Once the Add-on "Scan" is purchased it will also be possible to add codes to orders via the API.
Of course, it is possible to combine assignments with and without codes in a route. As soon as an order has no code(s), the driver can complete the order immediately. If code(s) are added then the driver will automatically get the (bar)(QR) code scanner in the driver app.
Notification identifier
- Enter here which notification template we should use with this job. The number you can enter here can be found on this page: https://app.routelogic.io/notification-templates after the template name. If you do not enter anything here, we will use the template that is set as the default template. If you want to address customers in their own language, you can easily create multiple templates here and indicate for each job which template you want to send (e.g. for English-speaking customers send the English template).
Tags
- This column allows you to link jobs to (fixed) vehicles. For example, if you want all jobs to be sent by refrigerated truck, you can tag each job with "refrigerated truck". Add the tag "refrigerated truck" to one (or all) vehicles and we will make sure that orders are always assigned to the correct vehicles. You can read more about tags in this article.
How do I update jobs that I have already imported?
When you imported your jobs, each job was assigned a unique id. If you import the same file again and the IDs of each job are the same then the jobs whose IDs are the same are updated with the information from your last import file. So if you keep the IDs the same of the jobs then you can update the data of the jobs in a file and do the import again. All commands whose IDs are the same will then be updated.