GENWiki

Premier IT Outsourcing and Support Services within the UK

User Tools

Site Tools


ccm:data_import

This is an old revision of the document!


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 NameFormatMandatoryAgentVisibleDescription
TitleVARCHAR(10)NYThe Title such as Mr, Mrs, Miss etc.
ForenameVARCHAR(30)NYFirst or given Name.
SurnameVARCHAR(30)NYLast or Family Name.
Address1VARCHAR(40)NYAddress Line 1, usually house number
Address2VARCHAR(40)NYSecond Line
Address3VARCHAR(40)NYThird Line
CityVARCHAR(40)NYCity Name
CountyVARCHAR(40)NYCounty Name
PostCodeVARCHAR(12)NYPostCode properly formatted
CountryVARCHAR(3)NYANSI Country Code
Phone1VARCHAR(20)YYPrimary Phone Number
Phone2VARCHAR(20)NYFirst Alternative Number
Phone3VARCHAR(20)NYSecond Alternative Number
EmailVARCHAR(64)NYEmail Address
CallDataLine1VARCHAR(64)NYInformation to be displayed to the Agent Line 1
CallDataLine2VARCHAR(64)NYInformation to be displayed to the Agent Line 2
CallDataLine3VARCHAR(64)NYInformation to be displayed to the Agent Line 3
SrcRef1VARCHAR(64)NNSource Data Reference 1
SrcRef2VARCHAR(64)NNSource Data Reference 2
SrcRef3VARCHAR(64)NNSource Data Reference 3
CallBackDateDATETIMENNThe Date to begin calling in the format DD/MM/YYYY
CallBackSlotINTNNThe 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

XML File Format

<?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>

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.

/data/webs/external/dokuwiki/data/attic/ccm/data_import.1599813572.txt.gz · Last modified: 2020/09/11 08:39 by genadmin

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki