How To

Transforming Data into a Usable Format

We have a database with order information where we just have 2 columns ID and Data where the Data column holds a Blob of strings like this below. I wanted to extract these strings into separate columns so that they could be stored in the database.

FIRST_NAME:Sean
LAST_NAME:Cleaver 
COMPANY:Simego Ltd 
EMAIL:sean@simego.com 
PHONE: 
FAX: 
COUNTRY:United Kingdom 
STATE:East Susses 
CITY:St Leonards-on-Sea 
ZIP:TN38 9NP 
ADDRESS:34 Stirling Road 
LICENSE_CODE:5E366AAFBB 
EXPIRATION_DATE:2012-06-23 
DATE_UPDATED: 
CHANGED_BY:VENDOR 
LICENSE_TYPE:REGULAR 
DISABLED:0 
RECURRING:0a 
LICENSE_PRODUCT:4559088 
START_DATE:2012-05-23 
LICENSE_LIFETIME:0 
PARTNER_CODE: 
PSKU: 
ACTIVATION_CODE:DCDA2M-MEX6DR-Q 
EXPIRED:0 
HASH:7b9dfb715ae1a703e12603aa

With Data Sync it took about 5 minutes to write a few Dynamic Columns to transform this into the right shape to import virtually anywhere. (CRM, Sharepoint, SQL etc)

For each row we basically read each line creating a Dictionary of the Values and then update the Dynamic Properties with the values from the Dictionary. The main task here was Cut & Paste

    public string FIRST_NAME { get; set; }
    public string LAST_NAME { get; set; }
    public string COMPANY { get; set; }
    public string EMAIL { get; set; }
    public string PHONE { get; set; }
    public string FAX { get; set; }
    public string COUNTRY { get; set; }
    public string STATE { get; set; }
    public string CITY { get; set; }
    public string ZIP { get; set; }
    public string ADDRESS { get; set; }
    public string LICENSE_CODE { get; set; }
    public string EXPIRATION_DATE { get; set; }
    public string DATE_UPDATED { get; set; }
    public string CHANGED_BY { get; set; }
    public string LICENSE_TYPE { get; set; }
    public string DISABLED { get; set; }
    public string RECURRING { get; set; }
    public string LICENSE_PRODUCT { get; set; }
    public string START_DATE { get; set; }
    public string LICENSE_LIFETIME { get; set; }
    public string PARTNER_CODE { get; set; }
    public string PSKU { get; set; }
    public string ACTIVATION_CODE { get; set; }
    public string EXPIRED { get; set; }
    public string HASH { get; set; }
    
    public override bool BeginRow()
    {        
        Dictionary<string, string> items = new Dictionary<string, string>();
        
        using(var sr = new StringReader(Data)) 
        {
            string line = string.Empty;
            while((line = sr.ReadLine()) != null)
            {
                string [] split = line.Split(':');
                if ( split.Length == 2 )
                {
                    items.Add(split[0], split[1]);    
                }                
            }
        }
        
        FIRST_NAME = items["FIRST_NAME"];
        LAST_NAME = items["LAST_NAME"];
        COMPANY = items["COMPANY"];
        EMAIL = items["EMAIL"];
        PHONE = items["PHONE"];
        FAX = items["FAX"];
        COUNTRY = items["COUNTRY"];
        STATE = items["STATE"];
        CITY = items["CITY"];
        ZIP = items["ZIP"];
        ADDRESS = items["ADDRESS"];
        LICENSE_CODE = items["LICENSE_CODE"];
        EXPIRATION_DATE = items["EXPIRATION_DATE"];
        DATE_UPDATED = items["DATE_UPDATED"];
        CHANGED_BY = items["CHANGED_BY"];
        LICENSE_TYPE = items["LICENSE_TYPE"];
        DISABLED = items["DISABLED"];
        RECURRING = items["RECURRING"];
        LICENSE_PRODUCT = items["LICENSE_PRODUCT"];
        START_DATE = items["START_DATE"];
        LICENSE_LIFETIME = items["LICENSE_LIFETIME"];
        PARTNER_CODE = items["PARTNER_CODE"];
        PSKU = items["PSKU"];
        ACTIVATION_CODE = items["ACTIVATION_CODE"];
        EXPIRED = items["EXPIRED"];
        HASH = items["HASH"];
        
        return true; // return false to remove row from results.
    }

And the results in Data Sync Data Preview.

Preview