# Making summary (summation table) from two data tables • ### Question

• Hi Everyone,

I need a help. I have to develop a console application which takes input of two csv files (or excel files). I am importing these files and assigning into Data Table. one csv file has employee details with project id and other csv file has payroll items and respective amount with employee id and name as below. Please advice to calculate data summary table. Below example shows the details.

I have a two data table and need to make data summary, table, it may be a data table or array. But it should be as below mentioned data summary.

Employee Details

 Employee ID Emplyee name Project / Department Code Allocation 101140 Name1 151 100 101141 Name2 152 80 101142 Name3 153 90 101144 Name4 153 10 101145 Name5 152 40 101143 Name6 151 30

Employee Payroll

 Employee Code Gross Pay Total Taxable Pay Total Non-taxable Pay Total ER NICable Pay Total Deductions Employee NI Amount Employer NI Amount Employee Tax Amount Total AEO Deducted Student Loans Deducted 101140 1000.00 2000.00 5000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 101142 2000.00 3000.00 3000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 101145 6000.00 1000.00 2000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 101141 9000.00 7000.00 6000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 101143 1000.00 6000.00 2000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 101144 2000.00 6,622.80 9000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00 00000.00

Data Summary

 Project / Department Code Gross Pay Total Taxable Pay Total Non-taxable Pay Total ER NICable Pay Total Deductions Employee NI Amount Employer NI Amount Employee Tax Amount Total AEO Deducted Student Loans Deducted 151 1300 152 9600 153 2000

Calculation: Gross Pay

151= (1000.00*1)+( 1000.00*.3)=1000.00+300=1300

152= (9000.00*.8)+6000.00*.4=7200+2400=9600

153=2000*.9+2000*.1=1800+200=2000

same way need to calculate for each payroll item

I have wrote below code, but stuck to apply logic for data summary table

DataTable emplyeetable= new DataTable();
emplyeetable= ConvertCSVtoDataTable("C:\\abcd\\input\\workday.csv");

DataTable payroll = new DataTable();
payroll = ConvertCSVtoDataTable("C:\\abcd\\input\\payroll.csv");
int payrollcount = payroll.Columns.Count;

DataView view = new DataView(emplyeetable);
DataTable distinctValues = view.ToTable(true, "Project");

//DataTable table = GetTable();

//Make a summary table

DataTable DataSummary = new DataTable();
for (int i = 0; i < payrollcount; i++)
{

}

for (int i = 0; i < distinctValues.Rows.Count; i++)
{
DataRow oRow = distinctValues.DefaultView[i].Row;

}

Sunday, February 9, 2020 5:54 PM

• Hi,

I modified the code and it should work now.

```    class Program
{
static void Main(string[] args)
{

string path = @"D:\Test\csv\Employee Details.csv";
DataTable details = ConvertCSVtoDataTable(path);

string path2 = @"D:\Test\csv\Employee Payroll.csv";
DataTable payroll = ConvertCSVtoDataTable(path2);
int columnNum = payroll.Columns.Count;
Dictionary<string, List<double>> dict = new Dictionary<string, List<double>>();
for (int i = 1; i < columnNum; i++)
{
var result = from de in details.AsEnumerable()
join pa in payroll.AsEnumerable() on de["Employee ID"] equals pa["EmployeeCode"] into g
from leftJoin in g.DefaultIfEmpty()
select new
{
Employee = de,
DepartmentCode = de["Project / Department Code"],
Value = leftJoin == null ? 0 : double.Parse(leftJoin[i].ToString()) * (double.Parse(de["Allocation"].ToString()) / 100),
};
var res = from code in result
group code by code.DepartmentCode into g
select new
{
DepartmentCode = g.Key,
Pay = g.Sum(a => a.Value),
};
if (dict.Count == 0)
{
foreach (var item in res)
{
List<double> list = new List<double>();
}
}
else
{
foreach (var item in res)
{
foreach (var row in dict)
{
if (item.DepartmentCode.ToString() == row.Key)
{
}
}
}
}

}
string csv = "";
foreach (DataColumn item in payroll.Columns)
{
csv += item.ColumnName + ",";
}
csv += Environment.NewLine;
foreach (var item in dict)
{
csv += item.Key;
for (int i = 0; i < item.Value.Count; i++)
{
csv += ","+item.Value[i];
}
csv += Environment.NewLine;
}
Console.WriteLine();
File.WriteAllText(@"d:\Test\aaa.csv", csv);

}
public static DataTable ConvertCSVtoDataTable(string strFilePath)
{
DataTable dt = new DataTable();
{
{
}
while (!sr.EndOfStream)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
}

}
return dt;
}
}```

I deleted the step of importing data into datatable, and now it is directly exporting the string to a csv file.

Best Regards,

Timon

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, February 13, 2020 3:10 AM
• Hi Timon,<o:p></o:p>

Thanks a ton. This is what I was looking for. Working perfectly fine. Really appreciate your effort.<o:p></o:p>

Thanks

Friday, February 14, 2020 10:06 AM

### All replies

• Hi,

Thank you for posting here.

I have some questions want to confirm.

1. 101142 appears twice in Employee Payroll, so which one should be used when calculating?

2. What is the correspondence between Project / Department Code and Employee? Does each Project / Department Code necessarily correspond to two employees? If there are more than two, do all have to be calculated? Because in the data you give, 151 corresponds to 101140 and 100543, but in Employee Payroll, 100543 has not appeared.

Honestly, I don't particularly understand your calculation rules. If you can describe it in detail, it will help us solve this problem faster.

Best Regards,

Timon

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, February 10, 2020 9:12 AM
• Hi Timon,

I am very sorry for not putting proper details. Please find below details. I hope that will make things more clear.

Actually I made a sample data, 101142 came twice by mistake. Employee id is unique in payroll data. I have updated the above table and calculation accordingly. Let me put some more details.

For a project (project/Department Code), a number of employee may work. In above example for project code 151, employee involved are 101140 and 101143 and their respective allocation in the project is 100% and 30%. For one project/Department code there may be number of employees, it is not limited for two. In sample example, I have mentioned two. It may be any number, and need to be calculated for all. In nutshell need to calculate summary for each (project/ Department code) available in employee details table. Please note that allocation is in %. 100 means 100% and 10 means 10%. So for each project, what are employee worked and based on allocation need to calculate payroll summary. Individual payroll data is available in payroll table. Please note that I just made sample data to get the logic. In actual employee table will have details for all employee and payroll table will have all payroll items (more than 80 columns). In example I have just calculated for Gross pay, in actual same way need to calculate for all payroll items. Payroll table has data for individual employee and my requirement is to get summary table based on project/Department code same like payroll table, using employee and payroll table. Summary table will be based on project/department code, whereas payroll table is based on individual employee.  Will be getting project code and all involved employee allocation in employee details table where as employee respective expenditure in payroll table.

Actually this is require to make Journal for finance system based on input employee file and payroll item file using configurable mappings. To generate journal file, I need intermediate summary table. I am facing difficulty to apply logic to get summary table.

I hope, I am able to explain you the requirement. Please let me in case of any further query or clarification.

Monday, February 10, 2020 5:53 PM
• Hi,

It should be pointed out that there is still a bit of error in the data. The Gross Pay of 144 in the Employee Payroll table is 2000, but in the following formula, you used 1000 to participate in the calculation.

I thought my logic was wrong, fortunately, I find it quickly.

In the future, when you post a question, please check the data carefully.

``` static void Main(string[] args)
{

string path = @"D:\Test\csv\Employee Details.csv";
DataTable details = ConvertCSVtoDataTable(path);

string path2 = @"D:\Test\csv\Employee Payroll.csv";
DataTable payroll = ConvertCSVtoDataTable(path2);
var result = from de in details.AsEnumerable()
join pa in payroll.AsEnumerable() on de["Employee ID"] equals pa["EmployeeCode"] into g
from leftJoin in g.DefaultIfEmpty()
select new
{
Employee = de["Employee ID"],
DepartmentCode= de["Project / Department Code"],
Value = leftJoin == null ? 0 : double.Parse(leftJoin["GrossPay"].ToString()) * (double.Parse(de["Allocation"].ToString()) / 100)
};

var res = from code in result
group code by code.DepartmentCode into g
select new
{
DepartmentCode = g.Key,
Pay = g.Sum(a => a.Value)
};

foreach (var item in res)
{
Console.WriteLine(item.DepartmentCode +"  "+item.Pay);
}
}
public static DataTable ConvertCSVtoDataTable(string strFilePath)
{
DataTable dt = new DataTable();
{
{
}
while (!sr.EndOfStream)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
}

}
return dt;
}
}```

Result: Best Regards,

Timon

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, February 11, 2020 8:21 AM
• Hi Timon,

Thanks a lot for your reply. I am sorry for putting incorrect data in formula. This is excellent logic. I have one doubt, I need to make a summary table for all payroll items. you have calculated for Gross pay, where as in payroll table there are so many other columns. I need to get a summarized table respective of project code for all columns.In example I have calculated for Gross pay, where as I need for all other columns as mentioned all columns below. In payroll table I have put  zero in lot of cells where as in actual all cells will have value some value

 Total Taxable Pay Total Non-taxable Pay Total ER NICable Pay Total Deductions Employee NI Amount Employer NI Amount Employee Tax Amount Total AEO Deducted Student Loans Deducted

Can we do the loop based on number of payroll items. but if do loop, then how get dynamically payroll column. I mean first loop will calculate for Gross pay, second loop will calculate for Total Taxable Pay, third loop will calculate for Total Non-taxable Pay etc. In simple when I run your code I should get first column as project code and rest column as payroll item. As of now it is giving output for one payroll item. I need out put with all payroll item. I am sorry, if I am asking something very silly. It will great help , if you can provide me the code, which should give result for all payroll items,as of now it is giving only for Gross pay. Thanks a lot in advance.

Final output for summary table should be like below.

 Project / Department Code Gross Pay Total Taxable Pay Total Non-taxable Pay Total ER NICable Pay Total Deductions Employee NI Amount Employer NI Amount Employee Tax Amount Total AEO Deducted Student Loans Deducted 151 1300 Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value 152 9600 Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value 153 2000 Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value Calculated value

Tuesday, February 11, 2020 7:24 PM
• Hi,

This is not complicated, we just need to add the corresponding content in the linq statement.

In the code below, I added Total Taxable Pay and Total Non-taxable Pay.

```  static void Main(string[] args)
{

string path = @"D:\Test\csv\Employee Details.csv";
DataTable details = ConvertCSVtoDataTable(path);

string path2 = @"D:\Test\csv\Employee Payroll.csv";
DataTable payroll = ConvertCSVtoDataTable(path2);
var result = from de in details.AsEnumerable()
join pa in payroll.AsEnumerable() on de["Employee ID"] equals pa["EmployeeCode"] into g
from leftJoin in g.DefaultIfEmpty()
select new
{
Employee = de["Employee ID"],
DepartmentCode = de["Project / Department Code"],
Value = leftJoin == null ? 0 : double.Parse(leftJoin["GrossPay"].ToString()) * (double.Parse(de["Allocation"].ToString()) / 100),
Value1 = leftJoin == null ? 0 : double.Parse(leftJoin["Total Taxable Pay"].ToString()) * (double.Parse(de["Allocation"].ToString()) / 100),
Value2 = leftJoin == null ? 0 : double.Parse(leftJoin["Total Non-Ttaxable Pay"].ToString()) * (double.Parse(de["Allocation"].ToString()) / 100)
};

var res = from code in result
group code by code.DepartmentCode into g
select new
{
DepartmentCode = g.Key,
Pay = g.Sum(a => a.Value),
Pay1 = g.Sum(a => a.Value1),
Pay2 = g.Sum(a => a.Value2)
};

foreach (var item in res)
{
Console.WriteLine(item.DepartmentCode +"  "+item.Pay+"  "+item.Pay1+"  "+item.Pay2);
}
```

You can add more columns according to your needs, just modify the parameters.

By the way, you can use the following code to put the data into a datatable and then export it to a new csv file.

```            DataTable dataTable = new DataTable();
foreach (var item in res)
{

}```

```         public static void ToCSV( DataTable dtDataTable, string strFilePath)
{
StreamWriter sw = new StreamWriter(strFilePath, false);
for (int i = 0; i < dtDataTable.Columns.Count; i++)
{
sw.Write(dtDataTable.Columns[i]);
if (i < dtDataTable.Columns.Count - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
foreach (DataRow dr in dtDataTable.Rows)
{
for (int i = 0; i < dtDataTable.Columns.Count; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
string value = dr[i].ToString();
if (value.Contains(','))
{
value = String.Format("\"{0}\"", value);
sw.Write(value);
}
else
{
sw.Write(dr[i].ToString());
}
}
if (i < dtDataTable.Columns.Count - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}```

Result: Best Regards,

Timon

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Wednesday, February 12, 2020 10:03 AM
• Hi Timon,

Thanks a lot for your reply and the effort you have put. This came into my mind. But there are two things, number of payroll items are more than 85 and some payroll item is dynamic, i mean it may come or may not. if you can advice dynamically by looping will be great.

Wednesday, February 12, 2020 11:12 AM
• Hi,

I modified the code and it should work now.

```    class Program
{
static void Main(string[] args)
{

string path = @"D:\Test\csv\Employee Details.csv";
DataTable details = ConvertCSVtoDataTable(path);

string path2 = @"D:\Test\csv\Employee Payroll.csv";
DataTable payroll = ConvertCSVtoDataTable(path2);
int columnNum = payroll.Columns.Count;
Dictionary<string, List<double>> dict = new Dictionary<string, List<double>>();
for (int i = 1; i < columnNum; i++)
{
var result = from de in details.AsEnumerable()
join pa in payroll.AsEnumerable() on de["Employee ID"] equals pa["EmployeeCode"] into g
from leftJoin in g.DefaultIfEmpty()
select new
{
Employee = de,
DepartmentCode = de["Project / Department Code"],
Value = leftJoin == null ? 0 : double.Parse(leftJoin[i].ToString()) * (double.Parse(de["Allocation"].ToString()) / 100),
};
var res = from code in result
group code by code.DepartmentCode into g
select new
{
DepartmentCode = g.Key,
Pay = g.Sum(a => a.Value),
};
if (dict.Count == 0)
{
foreach (var item in res)
{
List<double> list = new List<double>();
}
}
else
{
foreach (var item in res)
{
foreach (var row in dict)
{
if (item.DepartmentCode.ToString() == row.Key)
{
}
}
}
}

}
string csv = "";
foreach (DataColumn item in payroll.Columns)
{
csv += item.ColumnName + ",";
}
csv += Environment.NewLine;
foreach (var item in dict)
{
csv += item.Key;
for (int i = 0; i < item.Value.Count; i++)
{
csv += ","+item.Value[i];
}
csv += Environment.NewLine;
}
Console.WriteLine();
File.WriteAllText(@"d:\Test\aaa.csv", csv);

}
public static DataTable ConvertCSVtoDataTable(string strFilePath)
{
DataTable dt = new DataTable();
{
{
}
while (!sr.EndOfStream)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
}

}
return dt;
}
}```

I deleted the step of importing data into datatable, and now it is directly exporting the string to a csv file.

Best Regards,

Timon

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, February 13, 2020 3:10 AM
• Hi Timon,<o:p></o:p>

Thanks a ton. This is what I was looking for. Working perfectly fine. Really appreciate your effort.<o:p></o:p>

Thanks