none
Making summary (summation table) from two data tables RRS feed

  • 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

    //Load data

                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;
                    
                } 

    Mohammad Nadeem Alam SME in Emirates NBD






    Sunday, February 9, 2020 5:54 PM

Answers

  • 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[1],
                                     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>();
                            list.Add(item.Pay);
                            dict.Add(item.DepartmentCode.ToString(), list);
                        }
                    }
                    else
                    {
                        foreach (var item in res)
                        {
                            foreach (var row in dict)
                            {
                                if (item.DepartmentCode.ToString() == row.Key)
                                {
                                    row.Value.Add(item.Pay);
                                }
                            }
                        }
                    }
    
                }
                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();
                using (StreamReader sr = new StreamReader(strFilePath))
                {
                    string[] headers = sr.ReadLine().Split(',');
                    foreach (string header in headers)
                    {
                        dt.Columns.Add(header);
                    }
                    while (!sr.EndOfStream)
                    {
                        string[] rows = sr.ReadLine().Split(',');
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            dr[i] = rows[i];
                        }
                        dt.Rows.Add(dr);
                    }
    
                }
                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


    Mohammad Nadeem Alam SME in Emirates NBD

    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.

    Looking forward to your reply.

    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,

    Thank you so much for your reply. Really appreciate.

    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.

    I will be eagerly waiting for your reply.


    Mohammad Nadeem Alam SME in Emirates NBD


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

    Please review the code below, it should meet your needs.

    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();
                using (StreamReader sr = new StreamReader(strFilePath))
                {
                    string[] headers = sr.ReadLine().Split(',');
                    foreach (string header in headers)
                    {
                        dt.Columns.Add(header);
                    }
                    while (!sr.EndOfStream)
                    {
                        string[] rows = sr.ReadLine().Split(',');
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            dr[i] = rows[i];
                        }
                        dt.Rows.Add(dr);
                    }
    
                }
                return dt;
            }
        }

    Result:

    Hope this could be helpful.

    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


    Mohammad Nadeem Alam SME in Emirates NBD


    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),
                                 //New added.
                                 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),
                              //New added
                              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);
                }
                Console.ReadLine();
    

    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();
                dataTable.Columns.Add("Project / Department Code", typeof(string));
                dataTable.Columns.Add("Gross Pay", typeof(double));
                dataTable.Columns.Add("Total Taxable Pay", typeof(double));
                dataTable.Columns.Add("Total Non-taxable Pay", typeof(double));
                foreach (var item in res)
                {
                    dataTable.Rows.Add(item.DepartmentCode, item.Pay, item.Pay1, item.Pay2);
                   
                }

             public static void ToCSV( DataTable dtDataTable, string strFilePath)
            {
                StreamWriter sw = new StreamWriter(strFilePath, false);
                //headers  
                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.


    Mohammad Nadeem Alam SME in Emirates NBD

    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[1],
                                     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>();
                            list.Add(item.Pay);
                            dict.Add(item.DepartmentCode.ToString(), list);
                        }
                    }
                    else
                    {
                        foreach (var item in res)
                        {
                            foreach (var row in dict)
                            {
                                if (item.DepartmentCode.ToString() == row.Key)
                                {
                                    row.Value.Add(item.Pay);
                                }
                            }
                        }
                    }
    
                }
                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();
                using (StreamReader sr = new StreamReader(strFilePath))
                {
                    string[] headers = sr.ReadLine().Split(',');
                    foreach (string header in headers)
                    {
                        dt.Columns.Add(header);
                    }
                    while (!sr.EndOfStream)
                    {
                        string[] rows = sr.ReadLine().Split(',');
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            dr[i] = rows[i];
                        }
                        dt.Rows.Add(dr);
                    }
    
                }
                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


    Mohammad Nadeem Alam SME in Emirates NBD

    Friday, February 14, 2020 10:06 AM