View All Blog Posts
Transform Any Data with Data Sync
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.
