Split Column Values into multiple Rows

We were asked how you might use Data Sync to Split values stored in a column with a separator character into multiple rows repeating the data on each row.

This is not something Data Sync handles out of the box but with a little bit of Dynamic Column code it is quite possible.

Assuming you have some data like this

You can see that Item 1 and Item 2 have multiple Options where they are separated by a semi-colon (;). Item 3 has no options and is NULL.

To do this in Data Sync we need to override the row loading with Dynamic Columns and split the Value column. We also need to be careful since we can create a StackOverflow exception and crash Data Sync if we do not exit the loop correctly.

So in the code below we Split the Value Column and if we have more than 1 result we cancel the current row and create new row items for each element we found. We have to manually copy the row element by element and put the split single value into the value column.

public override bool BeginRow()
    {        
        if(!string.IsNullOrEmpty(Value))
        {
            var parts = Value.Split(';');
            
            if ( parts.Length > 1 )
            {
                foreach(var s in parts)
                {
                    var new_row = Table.NewRow();
                    foreach(var c in Table.Columns) 
                    {
                        if ( c.ColumnName == "Value" )
                        {
                            new_row[c.ColumnName] = s;
                        } 
                        else 
                        {
                            new_row[c.ColumnName] = Row[c.ColumnName];    
                        }
                    }
                    
                    Table.Rows.Add(new_row);
                }
                
                return false;
            }
        }
                            
        return true; 
    }    

Adding the new rows with this code causes the BeginRow() method to be called again for each of the split values. But this time the Value column only contains 1 value so we just drop out the bottom.