I have designed code to implement the second solution that i proposed.
Here it is-
I am going with the <sivagelli_collection-png> that you have attached above. The collection has one column with comma delimited text and you want to break the column in to multi column.
In the object studio, create a new object and add a code stage. The code uses C# and Linq.
So, first things to do is to add the below namespaces and reference dlls to the Initialise page and do not forget to change the language to C#.
External References:
System.Core.dll
System.Data.DataSetExtensions.dll
Namespaces:
System.Linq
System.Collections.Generic
Now, on the Action1 page-
Have Start stage accept the below arguments:
- CollectionIn of Collection Type and store in Coll_In
- "CollectionIn" would be the initial collection, you have with single column. Here, you have to pass the "ExtractedCollection"
- delimiter of Text Type and store in delimiter Data Item
- This is to hold the delimiter to split the text. Pass ","
- columnName of type text and store in columnName Data item
- The name of the column in the collection. Pass "Value"
- Headers of Collection Type and store in Headers
- Create a Single Column collection and add field names you want. Pass a collection with On Date, Off Date, so on listed in rows of a column
For the Code stage, have all the above as input arguments. For Outputs, add 'Collection_Out' of type collection and Store In Coll_Out.
Add the below code to the code stage-
C#:
var dtCompleteRecords1 = new DataTable();
foreach(DataRow row in Headers.Rows){
dtCompleteRecords1.Columns.Add(new DataColumn(row.Field<string>(0),typeof(string)));
}
dtRecords.AsEnumerable()
.Select(row => row[columnName].ToString().Split(delimiter.ToCharArray()))
.ToList()
.ForEach(array => dtCompleteRecords1.Rows.Add(array));
Collection_Out = dtCompleteRecords1.Copy();
On execution, Coll_Out will have the data split in to multiple columns with column names specified in "Headers" collection.
Post back how it goes!