Sending planning data as CSV file
To make sending planning data a lot easier, we make use of our universal planning inbox. The inbox is a single API which is able to receive planning data in different formats. One of the formats we are able to consume is CSV, the comma-separated value format. Underneath you will find more information on how to arrange your CSV file, either for Home Delivery or Supply Chain. Let's get started.
A look at the basic format
First examine the following example, which is a dummy dataset, that gives us some basic guidance:
trip ID,STOP ID,location ID,start time stop,END time stop
1,Stop1,1,2021-01-25T10:00:00+01:00,2021-01-25T10:00:00+01:00
1,Stop2,2,2021-01-25T10:45:00+01:00,2021-01-25T11:00:00+01:00
1,Stop3,1,2021-01-25T11:30:00+01:00,2021-01-25T11:45:00+01:00What we can learn from the above example is:
- The accepted delimiter is a comma.
- Fields should not be encapsulated by single or double quotes.
- The first line of a file should always be a header data row.
-
All headers are treated as case-insensitive, so
trip IDandTRIP IDare both valid for the same field. - Each line within your CSV data should represent a single stop with a single shipment within a trip.
- Columns can be in any order, as long as the column integrity stays intact.
Home Delivery
Next to some required fields in the previous example, you usually want to provide more information for Home Delivery, such as the address data for locations, as those addresses are unmanaged on the Simacan Platform. An example CSV planning for home delivery could look like the following:
trip ID,stop ID,location ID,order ID,start time stop,end time stop,street,house number,postcode,city
1,Stop1,1,1234,2021-01-25T10:00:00+01:00,2021-01-25T10:00:00+01:00,Street1,1,1234AB,Amersfoort
1,Stop2,2,1235,2021-01-25T10:45:00+01:00,2021-01-25T10:45:00+01:00,Street2,2,1234AB,Amersfoort
1,Stop3,1,1237,2021-01-25T13:00:00+01:00,2021-01-25T13:00:00+01:00,Street4,4,1234AB,AmersfoortIn a tabular way the contents of the above file contains the following (scroll right if needed):
| trip ID | stop ID | location ID | order ID | start time stop | end time stop | street | house number | postcode | city |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Stop1 | 1 | 1234 | 2021-01-25T10:00:00+01:00 | 2021-01-25T10:00:00+01:00 | Street1 | 1 | 1234AB | Amersfoort |
| 1 | Stop2 | 2 | 1235 | 2021-01-25T10:45:00+01:00 | 2021-01-25T11:00:00+01:00 | Street2 | 2 | 1234AB | Amersfoort |
| 1 | Stop3 | 1 | 1237 | 2021-01-25T13:00:00+01:00 | 2021-01-25T13:00:00+01:00 | Street3 | 4 | 1234AB | Amersfoort |
Field definitions
Next to the example fields above there are few fields optional not used as well. In the following table, we show you all required and optional field options for Home Delivery:
| Header name | Field value | Description | |
|---|---|---|---|
| Trip ID | trip ID | Unique identifier for this trip, on this date | required |
| Stop ID | stop ID | Unique identifier for this stop, for this trip | required |
| Stop Name | stop name | Friendly name that is shown on the location for the appropriate stop | optional |
| Stop Number | stop number | The number of the stop within the trip | optional |
| Stop Comment | stop comment | Text remark to be displayed with the stop in the Control Tower | optional |
| Order ID | order ID | The order number, this is used passed in the shipment | optional |
| Trace Code | trace code | The code that is used for track & trace | optional |
| Location ID | location ID | Unique identifier for this location | required |
| Start time stop | start time stop | Start time of the stop; ISO8601 Date and time in UTC | required |
| End time stop | end time stop | End time of the stop; ISO8601 Date and time in UTC | required |
| Start time trip | start time trip | Start time of the trip; ISO8601 Date and time in UTC. When not given, first start time stop is used | optional |
| End time trip | end time trip | End time of the trip; ISO8601 Date and time in UTC. When not given, last end time stop is used | optional |
| Street | street | Street, part of location | required |
| House number | house number | House number, part of location | optional |
| House number addition | house number addition | House number addition, part of location | optional |
| Postcode | postcode | Postcode, part of location | required |
| City | city | City, part of location | required |
| Lat | lat | Latitude of the location | optional |
| Lon | lon | Longitude of the location | optional |
| Carrier ID | carrier ID | Unique identifier for the carrier | optional |
| Cancel Trip | cancel trip | Cancel the trip, assuming it already exists | optional |
Supply Chain
For Supply Chain planning you generally just provide location IDs, as there are managed from the Simacan Platform, but you might want to provide more information, such as variable carriers. An example CSV planning for Supply Chain operations could look like the following:
trip ID,stop ID,location ID,start time stop,end time stop,stop remark,carrier ID
1,Stop1,1,2021-01-25T10:00:00+01:00,2021-01-25T10:00:00+01:00,,carrier 1
1,Stop2,2,2021-01-25T10:45:00+01:00,2021-01-25T10:45:00+01:00,Use the back door,carrier 1
1,Stop3,1,2021-01-25T12:00:00+01:00,2021-01-25T12:00:00+01:00,,carrier 1
2,Stop1,1,2021-01-25T15:00:00+01:00,2021-01-25T15:00:00+01:00,,carrier 2
2,Stop2,3,2021-01-25T15:45:00+01:00,2021-01-25T16:45:00+01:00,Use the back door,carrier 2
2,Stop3,1,2021-01-25T17:00:00+01:00,2021-01-25T17:00:00+01:00,,carrier 2In a tabular way the contents of the above file contains the following (scroll right if needed):
| trip ID | stop ID | location ID | start time stop | end time stop | stop remark | carrier ID |
|---|---|---|---|---|---|---|
| 1 | Stop1 | 1 | 2021-01-25T10:00:00+01:00 | 2021-01-25T10:00:00+01:00 | carrier 1 | |
| 1 | Stop2 | 2 | 2021-01-25T10:45:00+01:00 | 2021-01-25T10:45:00+01:00 | Use the back door | carrier 1 |
| 1 | Stop3 | 1 | 2021-01-25T12:00:00+01:00 | 2021-01-25T12:00:00+01:00 | carrier 1 | |
| 2 | Stop1 | 1 | 2021-01-25T15:00:00+01:00 | 2021-01-25T15:00:00+01:00 | carrier 2 | |
| 2 | Stop2 | 3 | 2021-01-25T15:45:00+01:00 | 2021-01-25T16:45:00+01:00 | Use the back door | carrier 2 |
| 2 | Stop3 | 1 | 2021-01-25T17:00:00+01:00 | 2021-01-25T17:00:00+01:00 | carrier 2 |
Field definition
Here are the field definitions for all optional and required fields for Supply Chain planning:
| Header name | Field value | Description | |
|---|---|---|---|
| Trip ID | trip ID | Unique identifier for this trip, on this date | required |
| Stop ID | stop ID | Unique identifier for this stop, for this trip | required |
| Stop Name | stop name | Friendly name that is shown on the location for the appropriate stop | optional |
| Stop Number | stop number | The number of the stop within the trip | optional |
| Stop Remark | stop remark | Text remark to be displayed with the stop in the Control Tower | optional |
| Order ID | order ID | The order number, this is used passed in the shipment | optional |
| Location ID | location ID | Unique identifier for this location | required |
| Start time stop | start time stop | Start time of the stop; ISO8601 Date and time in UTC | required |
| End time stop | end time stop | End time of the stop; ISO8601 Date and time in UTC | required |
| Start time trip | start time trip | Start time of the trip; ISO8601 Date and time in UTC. When not given, first start time stop is used | optional |
| End time trip | end time trip | End time of the trip; ISO8601 Date and time in UTC. When not given, last end time stop is used | optional |
| Carrier ID | carrier ID | Unique identifier for the carrier | optional |
| Cancel Trip | cancel trip | Cancel the trip, assuming it already exists | optional |
| Stop monitoring | stop monitoring | Whether or not the stop is administrative. The values are either empty (not administrative), or "administrative" (administrative stop) |
Updating your planning
Now that you have seen the appropriate format, you should also be aware that it is possible to update a planning that you sent to us before. It is important that sent messages always contains a full state, so a full planning. Any IDs we receive, will always be matched to any previous data, wherever possible. A trip ID used, that remained the same matched to a previous trip ID, will update the known trip. A new trip ID will be considered as a new trip. Stop IDs that remain the same, will be considered as the previous sent stop or changes to the previous sent stop. New stop IDs are considered as new stops. Removed stop IDs will be marked as cancelled stops.