CSV Upload
Inbound CSV Upload
The inbound CSV Upload file format is specified below and can be created with the following fields:
| Col No. | Sheet Col. | Field | Type | Max Length | Example | Required |
|---|---|---|---|---|---|---|
| 1 | A | Load Delivery Date | Date | 8 digits (DDMMYYYY) | 10072014 | N |
| 2 | B | Load Delivery Time | Time | 4 digits (HHMM) | 1001 | N |
| 3 | C | End Delivery Date | Date | 8 digits (DDMMYYYY) | 10072014 | N |
| 4 | D | End Delivery Time | Time | 4 digits (HHMM) | 1759 | N |
| 5 | E | User Id | Character | 10 | JSmith | N |
| 6 | F | Job Code | Character | 20 | JOBCODE1 | Y |
| 7 | G | Cust Ref | Character | 30 | CR1 | Y |
| 8 | H | Job Type | Character | 2 | D | Y |
| 9 | I | Job Instruction | Character | 255 | Leave Next Door | N |
| 10 | J | Job Delivery Date | Date | 8 digits (DDMMYYYY) | 10072014 | Y |
| 11 | K | Job Delivery Time | Time | 4 digits (HHMM) | 1001 | Y |
| 12 | L | Job End Delivery Date | Date | 8 digits (DDMMYYYY) | 10072014 | Y |
| 13 | M | Job End Delivery Time | Time | 4 digits (HHMM) | 1031 | Y |
| 14 | N | Customer Name | Character | 50 | J Bloggs | Y |
| 15 | O | Address 1 | Character | 40 | 1 High Street | Y |
| 16 | P | Address 2 | Character | 40 | Trumpton | N |
| 17 | Q | Address 3 | Character | 40 | Lincoln | N |
| 18 | R | Address 4 | Character | 40 | Lincolnshire | N |
| 19 | S | Address 5 | Character | 40 | N | |
| 20 | T | Postcode | Character | 8 | L23 4hg | Y |
| 21 | U | Contact | Character | 40 | J Bloggs | N |
| 22 | V | Telephone | Character | 50 | 01234 34567 | N |
| 23 | W | Character | 255 | [email protected] | N | |
| 24 | X | Container Id | Character | 30 | C1 | Y |
| 25 | Y | Product Id | Character | 40 | P1 | N |
| 26 | Z | Product Description | Character | 40 | Prod 1 | N |
| 27 | AA | Long Prod Description | Character | 255 | Long Prod 1 | N |
| 28 | AB | Planned Qty | Character | Max value: 2147483647 | 1 | N |
| 29 | AC | Site | Character | 10 | Site1 | Y |
| 30 | AD | Job Group | Character | 10 | Group1 | Y |
| 31 | AE | Load Id | Character | 20 | LOAD1 | N |
| 32 | AF | Cust Code | Character | 20 | CUST CODE1 | N |
| 33 | AG | Vehicle Id | Character | 10 | VEH1 | N |
Files can be created using Excel or another spreadsheet and saved as CSV format.
For example a simple 1 line upload could be created as follows:
,,,,,JOBCODE1,CR1,D,,10072014,901,10072014,901,J Bloggs,,,,,,,,,,C1,,,,,TEST,TEST,,
Example 1: Single Job, Single Container.
These examples can be pasted into Notepad and then 'saved as' a .csv file by specifying a .csv extension (e.g. example1.csv).
Uploading this would create a Job with 'JOBCODE1' for customer 'J Bloggs' with a single container record. Note: this assumes that the customer 'J Bloggs' already exists on the C-ePOD database.
An example of an upload for a customer that does not exist would be as follows:
,,,,,JOBCODE2,CR2,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C1,,,,,TEST,TEST,,
Example 2: Single Container, Customer Information.
This could be saved as example2.csv.
Upload via Import screen
The import screen lists the following options:
Figure 1: Data Upload - Import screen.
- Job Group: Sets the Job Group the jobs are to be created for. Job Group is a method of separating Jobs for the same site.
- Upload Type: Should be set to 'OBS [CSV]'.
- Header Row: Should be set to 'Yes' if the first row of the CSV file contains a header row. The above examples do not so for these this value should be set to 'No'.
Once these values are set, click the 'Choose File' button and browse to the relevant folder for the file (e.g. example2.csv). Once it has been found, click 'Upload' to upload the data onto C-ePOD.
Validating Upload
The upload screen will list the result of the CSV upload, showing the upload information in the additional 'Validation' and 'Reason for Failure' columns:
Figure 2: Data Upload - Validation.
In this case, there was only one record to upload and the Validation field indicates the record was successfully uploaded.
The details of the Job created can be checked by entering the job details screen and searching for the Job Code.
Figure 3: Job Details - Uploaded job, load number defaulted.
Note: A 'Load Id' was not specified in the upload file. In this case, the system has generated a load number 'YTE-0010844O'.
Automatic Upload
It is possible to setup an inbound polling process that will automatically upload files from a certain folder. In the "Export Config" screen within C-ePOD, a record should be defined as follows:
Figure 4: Export Configuration - Configuration Details.
- Config ID: Inbound CSV
- Description: Inbound CSV Upload
- Type: FILE
- ID: JOB
- Destination: Upload Folder (C:\Inbound in this example)
- Direction: I (Inbound)
This indicates that files copied to the 'C:\Inbound' folder on the server which C-ePOD is running will be uploaded automatically into C-ePOD. Note: Some additional configuration is required to schedule an inbound polling job which picks up this configuration.
Validating Upload
For example, if we copy the example1.csv file mentioned above into the C:\Inbound folder.
Enter the 'Export Audit' screen and search for records created on the current date:
Figure 5: Export Audit - Verify upload.
In this example, the description field indicates that the record could not be uploaded. This is due to the fact that for the data listed above for example1 does not contain customer address details.
In order to fix this error, the Customer should be manually created within the C-ePOD system. Enter the Customer Maintenance screen and create a new record for 'J Bloggs':
Figure 6: Customer Maintenance - New Customer.
The previous example1.csv file can now be re-uploaded. In order to do this, the file should be moved from the error folder (e.g. C:\Inbound\errors\) back into the upload folder. Once the file has re-processed, check the 'Export Audit' screen again:
Figure 7: Export Audit - Verify upload.
The Description field of 'Uploaded' on the last line indicates that the job was successfully created.
Import File Examples
Including a Header Record
An extra line can be included at the front of the file to include Header Text for each column. This makes it much easier to create records within Excel or another spreadsheet tool. For this example (example3.csv) we will create a new upload file with a header row and two new job codes:
EPL_LOAD_START_PLANNED_DATE, EPL_LOAD_START_PLANNED_TIME, EPL_LOAD_END_PLANNED_DATE, EPL_LOAD_END_PLANNED_TIME, EPL_USER_ID, EPL_JOB_CODE, EPL_CUST_REF, EPL_JOB_TYPE, EPL_JOB_INSTRUCTION, EPL_START_PLANNED_DATE, EPL_START_PLANNED_TIME, EPL_END_PLANNED_DATE, EPL_END_PLANNED_TIME, EPL_CUSTOMER_NAME, EPL_ADDRESS_1, EPL_ADDRESS_2, EPL_ADDRESS_3, EPL_ADDRESS_4, EPL_ADDRESS_5, EPL_POSTCODE, EPL_CONTACT, EPL_TELEPHONE, EPL_EMAIL, EPL_CONTAINER_ID, EPL_PRODUCT, EPL_DESCRIPTION, EPL_DESCRIPTION_LONG, EPL_PLANNED_QTY, EPL_SITE_ID, EPL_JOB_GROUP, EPL_LOAD_ID, EPL_CUST_CODE
,,,,,JOBCODE3,CR3,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C3,,,,,TEST,TEST,,
,,,,,JOBCODE4,CR4,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C4,,,,,TEST,TEST,,
Example 3: Two Jobs for Single Containers with Customer Information and Header Row.
Browse to this file from the upload screen, this time setting the 'Header Row' as 'Yes':
Figure 8: Data Upload - Import screen.
Uploading this file using the manual upload screen gives the following result:
Figure 9: Data Upload - Validation.
This shows both records were successfully created.
Creating New Products
For the jobs created so far, product details have not been included. If we examine the Job with Job Code 'JOBCODE1', we can see that a job was created with a container with Id 'C1' but the container does not have any corresponding products:
Figure 10: Data Upload - Import screen.
If more details are required to be stored against each job, then it is possible to create product records for each container. This example will create a job with one container and 2 product codes:
,,,,,JOBCODE5,CR5,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C5_1,PROD1,PRODDESC1,LONGDESC1,1,TEST,TEST,,
,,,,,JOBCODE5,CR5,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C5_1,PROD2,PRODDESC2,LONGDESC2,2,TEST,TEST,,
Example 4: Single Job, Single Container, Multiple Products.
Figure 11: Data Upload - Validation.
Check the Job created in the 'Job Details' screen:
Figure 12: Job Details - Uploaded Job.
Figure 13: Job Details - Container and Product Details.
This shows the two product records that were created.
Assigning to Users
For the examples we have created so far, the User Id has not been specified. This means the Loads created have been set at status 'Pending'. In reality, the load may already have been assigned a driver for delivery. This example shows a Job that has a user 'User1' already assigned to it:
,,,,User1,JOBCODE6,CR6,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C6,,,,,TEST,TEST,,
Example 5: Single Job, Single Container, assigned to user.
Uploading the file and check the load created:
Figure 14: Data Upload - Validation.
This time the load has been created with status 'Assigned' with the user set as 'User1':
Figure 15: Loads Screen - User assigned.
Note: The site parameter 'Import Create Standing Data' has to be checked in order for new users to be created:
Figure 16: Site Maintenance - Import Create Standing Data.
Multiple Loads
For the example so far, a single load has been created for each upload. If two different usernames are included, then two separate Load Ids will be generated:
,,,,User1,JOBCODE7,CR7,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C7,,,,,TEST,TEST,,
,,,,User2,JOBCODE8,CR8,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C8,,,,,TEST,TEST,,
Example 6: Two Jobs, Two Users, two separate Loads.
Uploading this file results in two separate loads being created:
Figure 17: Data Upload - Validation.
Figure 18: Job Details - Load One.
Figure 19: Job Details - Load Two.
Modifying and Re-uploading loads
If a job is at status 'Pending' or 'Assigned' then it is possible to modify the details of that job by uploading another file.
For example the Customer Reference field could be modified for 'JOBCODE8' from the original 'CR8' to 'CR8v2' using the data below:
,,,,User2,JOBCODE8,CR8v2,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C8,,,,,TEST,TEST,,
Example 7: Single Job, Updating Customer Reference information.
Figure 20: Data Upload - Validation.
Figure 21: Job Details - Updated Information.
Moving Jobs to a new load
It is possible to swap a Job from one Load to another Load by changing the user and load data. In order to do this, we have to specify the load id the jobs will belong to.
Firstly, we create a new load with three jobs by assigning them all to the same load 'LOAD1':
,,,,User1,JOBCODE9,CR9,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C9,,,,,TEST,TEST,Load1,
,,,,User1,JOBCODE10,CR10,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C10,,,,,TEST,TEST,Load1,
,,,,User1,JOBCODE11,CR11,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C11,,,,,TEST,TEST,Load1,
Example 8: Three Jobs, Single Load.
Figure 22: Data Upload - Validation.
Figure 23: Job Details - Load One.
Then if we change the load of one of the records and re-upload it, the record will be moved to a different load:
,,,,User2,JOBCODE10,CR10,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C10,,,,,TEST,TEST,Load2,
Example 9: Existing Job, New User, New Load.
Figure 24: Data Upload - Validation.
Job code 'JOBCODE10' is now assigned to 'Load2':
Figure 25: Job Details - Load2.
'Load 1' now consists of only two jobs:
Figure 26: Job Details - Load1.
Note that a job that is added to an existing load will be added with the highest sequence number, so that job will be the last job to be processed on the device.
Sequencing Jobs on a new load
If multiple jobs are added to a load, the sequence the jobs appear on the device can be determined by setting a Site level parameter.
Within the Site Maintenance screen, in the Admin tab there is a 'CSV Upload Sequence' list:
Figure 27: Site Maintenance - CSV Upload Sequence setting.
The value 'Default' will cause the jobs to be added in the order of the Load Planned Start Date/Time passed in, e.g. columns 1 and 2.
The value 'Order Provided' will cause the jobs to be added in the sequence they are included in the file.
Example Files
The following list links to the example files used in this user guide.
