GENWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
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 can be either CSV (where the separator need not be a comma) or XML with UTF-8 / ASCII encoding.
All lines within a CSV must have the same number of delimiters, and likewise all rows in an XML File must have the same number of attribute/value pairs. For CSV there should be a header row at row 1 (First Row) defining the column names for the rest of the file.

The actual names of the columns are for readability only but the column order must remain consistent. If you 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 either the separator for CSV or a reserved character for XML then it should be enclosed in double quotes (") or escaped respectively. 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|
|Email|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 ======

<code>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</code>

====== XML File Format ======

<code><?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Default>
    <Title>Mr</Title>
    <Forename>Fred</Forename>
    <Surname>Bloggs</Surname>
    <Address1>24</Address1>
    <Address2>Stoney Street</Address2>
    <Address3 />
    <City>Nottingham</City>
    <County>Nottinghamshire</County>
    <PostCode>NG1 7AW</PostCode>
    <Phone1>01159857040</Phone1>
    <CallDataLine1>Catalog: SimplyFun</CallDataLine1>
    <SrcRef1>013463465N</SrcRef1>
  </Default>
  <Default>
    <Title>Mr</Title>
    <Forename>Joe</Forename>
    <Surname>Soap</Surname>
    <Address1>16</Address1>
    <Address2>Arkwright Street</Address2>
    <Address3>The Park</Address3>
    <City>Nottigham</City>
    <County>Nottinghamshire</County>
    <PostCode>NG1 8FP</PostCode>
    <Phone1>01152358845</Phone1>
    <CallDataLine1>Catalog: EnjoyingData</CallDataLine1>
    <SrcRef1>028573568X</SrcRef1>
  </Default>
</NewDataSet></code>

===== 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 allow 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.

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 SFTP and we will setup a specific SFTP service for each data vendor. 

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 SFTP 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. 

We will produce a data return based on this test file (if required) and deliver this to the SFTP 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 SFTP 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.