Advent Challenge #7 Beginner
Santa had to think again about how best to solve this challenge. At first, he tried generic approaches in order to be able to react as flexibly as possible to structural changes. But he decided to use a specific approach, because it is easier to understand and read.
First, a data table is created with the different columns and their types. Important is here the column Effort and StartDate. The following steps reads the data and copies them into the table. Here is very important the conversion of the Effort to single and StartDate to DateTime type. One line of LINQ code contains a query that sorts, groups, selects and sums. Last but not least the result table is written to the output window.
//-Begin----------------------------------------------------------------
//-CSV to DataTable-----------------------------------------------------
DataTable CSVTable = new DataTable();
CSVTable.Columns.Add("RegionCode", typeof(string));
CSVTable.Columns.Add("AdressID", typeof(string));
CSVTable.Columns.Add("Task", typeof(string));
CSVTable.Columns.Add("Effort", typeof(Single));
CSVTable.Columns.Add("Status", typeof(string));
CSVTable.Columns.Add("StartDate", typeof(DateTime));
//-Read CSV file--------------------------------------------------------
StreamReader CSV = new StreamReader("Data.csv");
//-Write the data from the CSV file into the DataTable------------------
string Line = string.Empty;
int i = 0;
//-Read the lines and put them into the DataTable-----------------------
while((Line = CSV.ReadLine()) != null) {
//-Jump over header line----------------------------------------------
i += 1; if(i == 1) continue;
string[] Row = Line.Split(';');
DataRow CSVRow = CSVTable.NewRow();
CSVRow["RegionCode"] = Row[0];
CSVRow["AdressID"] = Row[1];
CSVRow["Task"] = Row[2];
//-Convert column Effort to data type Single--------------------------
Single Effort = Single.Parse(Row[3], CultureInfo.InvariantCulture);
CSVRow["Effort"] = Effort;
CSVRow["Status"] = Row[4];
//-Convert column StartDate to data type DateTime---------------------
DateTime StartDate;
DateTime.TryParseExact(Row[5], new string[]{ "MM/d/yyyy", "MM/dd/yyyy", "d.MM.yyyy", "dd.MM.yyyy"}, CultureInfo.InvariantCulture, DateTimeStyles.None, out StartDate);
CSVRow["StartDate"] = StartDate;
CSVTable.Rows.Add(CSVRow);
}
//-Query data from DataTable--------------------------------------------
CSVTable = CSVTable.AsEnumerable()
.OrderBy(o => o.Field<string>("RegionCode"))
.GroupBy(g => g.Field<string>("RegionCode"))
.Select(s => {
var row = CSVTable.NewRow();
row["RegionCode"] = s.Key;
row["Task"] = String.Join(",",s.Select(z => z.Field<string>("Task")));
row["Effort"] = s.Sum(r => r.Field<Single>("Effort"));
return row;
}).CopyToDataTable();
//-Output Result--------------------------------------------------------
string Output = string.Empty;
foreach(DataRow Row in CSVTable.Rows) {
Output += Row[0] + ";" + Row[2] + ";" + Row[3] + Environment.NewLine;
}
Console.WriteLine(Output);
//-End------------------------------------------------------------------
Here the result.
Santa tried it in C# again and implements it in UiPath as Invoke Code activity.
Main.xaml (7.3 KB)
In a further step Santa will try this approach also with more activities. Santa looks at his notes and continues to think.
Hint: It is not necessary to convert the StartDate field into the right data type for this beginner challenge, but Santa thinks it is better to convert any column in the right format. So you can calculate with them correctly and sorting is also very simple, also for the advanced challenge. 
Santa tried LINQPad to check other possibilities. Santa can use his code to define and read the data table. Only the query and the output are different, just to try it out.
This approach is also easy to read and Santa means that the clear view of the result is an advantage.
Also Santa tried an additional approach. He create the data table outside the Invoke Code activity and uses the Build Data Table activity.
A very comfortable way to define a data table he thinks. 
Especially because the correct data types can be defined here as well. He tried also to use the Read CSV activity, but without success. Therefore, Santa has left the reading of the data in the Invoke Code activity.
The Invoke Code activity has now one parameter…
… and the code is a little bit reduced.
//-Begin----------------------------------------------------------------
//-Read CSV file--------------------------------------------------------
StreamReader CSV = new StreamReader("Data.csv");
//-Write the data from the CSV file into the DataTable------------------
string Line = string.Empty;
int i = 0;
//-Read the lines and put them into the DataTable-----------------------
while((Line = CSV.ReadLine()) != null) {
//-Jump over header line----------------------------------------------
i += 1; if(i == 1) continue;
string[] Row = Line.Split(';');
DataRow CSVRow = CSVTable.NewRow();
CSVRow["RegionCode"] = Row[0];
CSVRow["AdressID"] = Row[1];
CSVRow["Task"] = Row[2];
//-Convert column Effort to data type Single--------------------------
Single Effort = Single.Parse(Row[3], System.Globalization.CultureInfo.InvariantCulture);
CSVRow["Effort"] = Effort;
CSVRow["Status"] = Row[4];
//-Convert column StartDate to data type DateTime---------------------
DateTime StartDate;
DateTime.TryParseExact(Row[5], new string[]{ "MM/d/yyyy", "MM/dd/yyyy", "d.MM.yyyy", "dd.MM.yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out StartDate);
CSVRow["StartDate"] = StartDate;
CSVTable.Rows.Add(CSVRow);
}
DataTable tmp = CSVTable;
//-Query data from DataTable--------------------------------------------
CSVTable = tmp.AsEnumerable()
.OrderBy(o => o.Field<string>("RegionCode"))
.GroupBy(g => g.Field<string>("RegionCode"))
.Select(s => {
var row = tmp.NewRow();
row["RegionCode"] = s.Key;
row["Task"] = String.Join(",",s.Select(z => z.Field<string>("Task")));
row["Effort"] = s.Sum(r => r.Field<Single>("Effort"));
return row;
}).CopyToDataTable();
//-End------------------------------------------------------------------
Hint: To use a reference or output parameter inside a query expression, it is necessary to assign an internal variable to it, in this example tmp.
Main.xaml (12.7 KB)
This example shows us very nicely how Invoke Code activities can be integrated into a workflow, Santa thinks. He learned a lot about LINQ.