Table of Contents
Our Import tool is fairly flexible but does have some specific requirements when supplying data to us. Please read the information below in its entirety.
File format should be CSV with UTF-8 / ASCII encoding. All lines within a CSV must have the same number of delimiters, There should NOT be a header row at the top of the file and if one is included it will be stripped.
The actual names of the columns are for readability only but the column order must remain consistent. If you have not followed the standard format and subsequently wish to change the order and/or add/remove columns then this must first be communicated so we can make changes to the import process.
Where a data element would contain the separator for CSV, a comma, it should be removed and not supplied.
Some characters are prohibited regardless of quoting or escaping and those are defined below under Prohibited Characters
The column data is defined below. Where data is not mandatory it can be optionally supplied, but if supplied must be valid.
Incoming File Format Specification
| Column Name | Format | Mandatory | AgentVisible | Description |
|---|---|---|---|---|
| Title | VARCHAR(10) | N | Y | The Title such as Mr, Mrs, Miss etc. |
| Forename | VARCHAR(30) | N | Y | First or given Name. |
| Surname | VARCHAR(30) | N | Y | Last or Family Name. |
| Address1 | VARCHAR(40) | N | Y | Address Line 1, usually house number |
| Address2 | VARCHAR(40) | N | Y | Second Line |
| Address3 | VARCHAR(40) | N | Y | Third Line |
| City | VARCHAR(40) | N | Y | City Name |
| County | VARCHAR(40) | N | Y | County Name |
| PostCode | VARCHAR(12) | N | Y | PostCode properly formatted |
| Country | VARCHAR(3) | N | Y | ANSI Country Code |
| Phone1 | VARCHAR(20) | Y | Y | Primary Phone Number |
| Phone2 | VARCHAR(20) | N | Y | First Alternative Number |
| Phone3 | VARCHAR(20) | N | Y | Second Alternative Number |
| VARCHAR(64) | N | Y | Email Address | |
| CallDataLine1 | VARCHAR(64) | N | Y | Information to be displayed to the Agent Line 1 |
| CallDataLine2 | VARCHAR(64) | N | Y | Information to be displayed to the Agent Line 2 |
| CallDataLine3 | VARCHAR(64) | N | Y | Information to be displayed to the Agent Line 3 |
| SrcRef1 | VARCHAR(64) | N | N | Source Data Reference 1 |
| SrcRef2 | VARCHAR(64) | N | N | Source Data Reference 2 |
| SrcRef3 | VARCHAR(64) | N | N | Source Data Reference 3 |
| CallBackDate | DATETIME | N | N | The Date to begin calling in the format DD/MM/YYYY |
| CallBackSlot | INT | N | N | The Time to attempt the call as a single hour number. e.g. 10 = 10:00-11:00 |
CallDataLineN : Information to be displayed to the Agent. This data is shown on the agents screen when the call pop's and should contain any information the agent would require during their activity. An example would be an account number.
SRCREFN : Source Data References are used to tag each record with one or more identifiers that would be required in any subsequent data return or reconciliation process. This is traditionally used to hold a Unique Reference Number from the data supplier.
Prohibited Characters
Any character less than 0x20 or greater than 0x7e with the exception of 0x0a, 0x0d are prohibited in source files and 0x25, 0x26, 0x27, 0x60 should be avoided if at all possible.
Example Files
The files below are valid examples, not definitions.
CSV FileFormat
Title,Forename,Surname,Address1,Address2,Address3,City,County,PostCode,Phone1,CallDataLine1,SrcRef1 Mr,Fred,Bloggs,24,Stoney Street,,Nottingham,Nottinghamshire,NG1 7AW,01159857040,Catalog: SimplyFun,013463465N Mr,Joe,Soap,16,Arkwright Street,The Park,Nottigham,Nottinghamshire,NG1 8FP,01152358845,Catalog: EnjoyingData,028573568X
Filenames
Filenames MUST BE Alphanumeric and underscore only with no spaces or other symbols at all, with an extension of .csv. It is STRONGLY RECOMMENDED that filenames are all lower case but this is not a requirement, the extension should be lower case as indicated.
The Transfer Filename format should be identified and defined clearly so our import process knows what its looking to import. A good example would be vendorname_YYYYMMDD.csv.
The FOLDER into which you upload the files is critical in determining the destination of the data on our systems so you must ensure data is loaded to the correct folder.
If you will be supplying multiple varying files then an example would be vendorname_filetype_YYYYMMDD.csv
An example for a supplier named PPP would be ppp_20190504.csv, or ppp_1_20190504.csv & ppp_2_20190504.csv
Import File Acceptance and Configuration Process
When you have constructed a file in the format above and wish to begin the process of supply then the following should be observed.
The Transfer Method should be identified and defined. We can accept files provided to Secure FTP and we will setup a specific Secure FTP service for each data vendor. Our servers support SFTP, FTP, FTPS and WebDAV.
The Transfer Schedule should be identified so we know how often to check for new data.
The Data Return process should be clearly defined (if any) so that we can write the required code to produce it.
Testing Phase
A sample file should be supplied to the agreed Secure FTP using the agreed filename.
We will then code our import process to read this file and setup the mapping between columns and our database. We will validate each column as supplied and feedback.
Import Notifications and Fallout can be advised by FTP or email and this will be setup as required.
We will produce a data return based on this test file (if required) and deliver this to the Secure FTP where it can be collected and validated by the data vendor.
The above process should be repeated as many times as necessary to ensure that both parties are confident that the process is correct.
Production Phase
Once testing is complete then we can begin live operations on a date mutually agreed upon. All test files/data should be purged from the Secure FTP and system before this date.
During the first week of live operation, log files should be examined to ensure no post testing issues have arisen.