CSV Files
Documentation on sending load location or status updates in a .csv file over our SFTP server. Two types of shipment statuses can be sent: a location, or a status.
- Location: The latitude/longitude location of the load.
- Status: Stop status updates such as arrived, departed, or enroute, or EDI 214 status codes
In a single .csv file, you can send different client_IDs if you are using Chaine to send data to multiple clients on the same cron job. You can send status, location, or both all in the same file.
The only limitation right now is a maximum of 1,000 rows which will get increased in the future.
SFTP Server Info
For server host and port, please ping your Chaine technical contact
- Username: This will be provided to you upon setup.
- Password: Will be provided to you upon setup. For SFTP, a password will be provided.
For SFTP, username and password will also work. For additional security, a public-private SSH keypair can be used.
Directory
All files go directly inside the home directory:
/
Our server is event-based, so as soon as a file is placed in the root, it will get processed. Older files will be purged by default after 30 days.
CSV File format
Click here for a sample file.
Required Columns for all CSV Types
All the types of .csv files must include the following columns:
| Columns | Required | Description | Example |
|---|---|---|---|
| client_id | Yes | Case-sensitive receiver's unique ID in the format that the receiver uses. | AChez9pcepd4e4 |
| load_id | Yes | The load number in the format that the receiver uses. | T976167 |
| sendr_dot | Yes* | Sending party's DOT number. | 2502791 |
| sendr_mc | Yes* | Sending party's MC number. | 868594 |
| sendr_scac | Yes* | Sending party's SCAC number. | WFER |
| equip_unit | No | This is your internal unit equipment number that is carrying the load for client with client_id. | T219341 |
| equip_trlr | No | This is your internal trailer equipment number that is carrying the load for client with client_id. | T219341 |
* One of DOT, MC or SCAC is required or else .csv will not be processed.
File Name
Any filename is accepted as long as the .csv suffix.
{file_name}.csv
Sending Location or Status
Location
Additional columns when sending location
| Columns | Required | Description | Example |
|---|---|---|---|
| lat | Yes | The latitude of the load or shipment's location value. | 37.790024 |
| lng | Yes | The longitude of the load or shipment's location value | -122.400833 |
| date_time | Yes | The date-time of when the location was received in ISO 8601 format | 2012-04-23T18:25:43.511Z |
Location example
| client_id | load_id | sendr_dot | sendr_mc | sendr_scac | lat | lng | equip_unit | equip_trlr | date_time |
|---|---|---|---|---|---|---|---|---|---|
| BRL4839 | AChez9pcepd4e4 | 2502791 | 868594 | WFER | 37.790024 | -122.400833 | T219341 | AB002 | 2012-04-23T18:25:43.511Z |
Status
Additional columns when sending status
| Columns | Required | Description | Example |
|---|---|---|---|
| status | Yes* | The status value. See below for possible options. | arrived |
| date_time | Yes | The status date-time in ISO 8601 format | 2012-04-23T18:25:43.511Z |
| stop_seq | Yes | This is the stop sequence number for the status being send. For example, if the load is 2 picks and 2 drops, then "stop_number": 3 refers to the first drop. A load is considered delivered status when the a departed status is sent for the last stop. | 2 |
| edi_code | Yes* | The EDI 214 shipment status code. See below for possible options. | X3 |
| edi_desc | No | The description for the EDI 214 shipment status code. | Arrived at Pick-up Location |
* One of status or edi_code is required, or else .csv will not be processed.
Status example
| client_id | load_id | sendr_dot | sendr_mc | sendr_scac | status | stop_seq | date_time | equip_unit | equip_trlr | edi_code | edi_desc |
|---|---|---|---|---|---|---|---|---|---|---|---|
| BRL4839 | AChez9pcepd4e4 | 2502791 | 868594 | WFER | arrived | 2 | 2012-04-23T18:25:43.511Z | T219341 | AB002 | X3 | Arrived at Pick-up Location |
Possible "status" Values
The status column accepts 1 of the following values. If you send a value not listed below, the status will not be processed.
"enroute": The estimated time of arrival (ETA) status, and estimated time of arrival is sent in date_time."arrived": When a carrier arrived at a stop (pick or drop)"departed": This is a stop's loaded / unloaded time
Sending EDI Status
The SFTP will also accept EDI 214 status codes sent in a .csv. file.
EDI Status .csv example
| client_id | load_id | sendr_dot | sendr_mc | sendr_scac | edi_code | edi_desc | date_time | equip_unit | equip_trlr |
|---|---|---|---|---|---|---|---|---|---|
| BRL4839 | AChez9pcepd4e4 | 2502791 | 868594 | WFER | X3 | Arrived at Pick-up Location | 2012-04-23T18:25:43.511Z | T219341 | AB002 |
Possible "edi_code" Values
Accepted EDI codes
Below are the list of EDI214 Shipment Status codes that are accepted in the API. If there are codes required to extend this list, please bring it up with your integration engineer as the API is flexible to extend EDI Code acceptance.
| EDI214 Code | Description |
|---|---|
| AF | Carrier Departed Pick-up Location with Shipment |
| AG | Estimated Delivery |
| X1 | Arrived at Delivery Location |
| X2 | Estimated Date and/or Time of Arrival at Consignee's Location |
| X3 | Arrived at Pick-up Location |
| X6 | En Route to Delivery Location |
| D1 | Completed Unloading at Delivery Location |
| CP | Completed Loading at Pick-up Location |
If you need to send additional EDI codes, please notify your Chaine contact during implementation. A more exhaustive list of EDI codes below can also be configured but are "OFF" by default.
ISO 8601 Date Format
Send all date-times in ISO 8601 format. Here is a quick example:
2016-03-23T23:20:00+13:00
{YYYY-MM-DD}T{HH:MM:SS}+{UTC-Offset}
edi_code = {
A3: 'Shipment Returned to Shipper',
A9: 'Shipment Damaged',
AF: 'Carrier Departed Pick-up Location with Shipment',
AG: 'Estimated Delivery',
AH: 'Attempted Delivery',
AI: 'Shipment has been Reconsigned',
AJ: 'Tendered for Delivery',
AM: 'Loaded On Truck',
AP: 'Delivery Not Completed',
AR: 'Rail Arrival At Destination Ramp',
B6: 'Estimated to Arrive at Carrier Terminal',
BA: 'Connecting Line or Cartage Pick-up',
BC: 'Storage in Transit',
D1: 'Completed Unloading at Delivery Location',
I1: 'In-Gate',
J1: 'Delivered To Connecting Line',
OA: 'Out-Gate',
K1: 'Arrived at Customs',
OO: 'Paperwork Received - Did not Receive Shipment or Equipment',
P1: 'Departed Terminal Location',
R1: 'Received from Prior Carrier',
RL: 'Rail Departure from Origin Ramp',
S1: `Trailer Spotted at Consignee's Location`,
X1: 'Arrived at Delivery Location',
X2: `Estimated Date and/or Time of Arrival at Consignee's Location`,
X3: 'Arrived at Pick-up Location',
X4: 'Arrived At Terminal Location',
AV: 'Available For Delivery',
A7: 'Refused By Consignee',
SD: 'Shipment Delayed',
X5: 'Arrived At Delivery Location Loading Dock',
X6: 'En Route to Delivery Location '
}