Click here to Skip to main content
15,887,135 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Good day, I have been given an assignment that gets data off a CSV file. The CSV file contains the following entries:
LName,FName,Dept,Grade,Gross Pay, Tax Paid, Net Pay.

I am able to retrieve the data and display it in the console window which is shown in my code below. However, I need help with displaying the results according to dept and getting the total net pay in each dept. There are three depts M, R, And S.

class DisplayEmpData
{
    public void DisplayEmp()
    {
        List<string> columns;
        List<Dictionary<string, string>> myData = GetData(out columns);
        foreach (string column in columns)
        {
            Console.Write("{0,-9}", column);
        }
        Console.WriteLine();
        foreach (Dictionary<string, string> row in myData)
        {
            foreach (string column in columns)
            {
                Console.Write("{0,-9}", row[column]);
            }
            Console.WriteLine();
        }
        Console.ReadKey();
    }

    private static List<Dictionary<string, string>> GetData(out List<string> columns)
    {
        string line;
        string[] stringArray;
        char[] charArray = new char[] { ',' };
        List<Dictionary<string, string>> data = new List<Dictionary<string, string>>();
        columns = new List<string>();
        try
        {
            FileStream aFile = new FileStream(@"..\..\EmployeeDetails.txt", FileMode.Open);
            StreamReader sr = new StreamReader(aFile);
            // Obtain the columns from the first line.
            // Split row of data into string array
            line = sr.ReadLine();
            stringArray = line.Split(charArray);
            for (int x = 0; x <= stringArray.GetUpperBound(0); x++)
            {
                columns.Add(stringArray[x]);
            }
            line = sr.ReadLine();
            while (line != null)
            {
                // Split row of data into string array
                stringArray = line.Split(charArray);
                Dictionary<string, string> dataRow = new Dictionary<string, string>();
                for (int x = 0; x <= stringArray.GetUpperBound(0); x++)
                {
                    dataRow.Add(columns[x], stringArray[x]);
                }
                data.Add(dataRow);
                line = sr.ReadLine();
            }
            sr.Close();
            return data;
        }
        catch (IOException ex)
        {
            Console.WriteLine("An IO exception has been thrown!");
            Console.WriteLine(ex.ToString());
            Console.ReadLine();
            return data;
        }
    }
}


this is the data found in the CSV file.
Sample data:
LName,FName,Dept,Grade,HrsWorked,Gross,Tax,Net
kakvi,sakib,M,b,50,179.38,19.91,159.47
Ahmed,Shaan,R,A,50,140.50,14.08,126.43
Thomas,John,S,B,34,109.38,9.41,99.97
Link,Frank,M,E,24,78.75,5.88,72.88
Jones,Jack,R,D,34,124.00,11.60,112.40
Rich,Dan,M,C,37,145.00,14.75,130.25

However this is what I want.
Desired Output
ID Surname Forename Grade Hrs GrossPay Tax Net Pay
0001 Kakvi Sakib B 50 179.38 19.91 159.47
xxxx xxxx xxxxxxx x xxx xxx.xx xxx.xx xxx.xx
xxxx xxxx xxxxxxx x xxx xxx.xx xxx.xx xxx.xx
xxxx xxxx xxxxxxx x xxx xxx.xx xxx.xx xxx.xx
--------
Total for: Marketing xxxxx.xx

Department: Sales
ID Surname Forename Grade Hrs Gross Pay Tax Net Pay
xxxx xxxx xxxxxxx x xxx xxx.xx xxx.xx xxx.xx
xxxx xxxx xxxxxxx x xxx xxx.xx xxx.xx xxx.xx
--------
Total for: Sales xxxxx.xx

Please help me. Thank You.
Posted
Updated 26-Apr-11 15:21pm
v3
Comments
Tarakeshwar Reddy 26-Apr-11 21:05pm    
Unchecked ignore html
Wonde Tadesse 26-Apr-11 21:06pm    
The code is not clear. Can you modify it.
fazleh ahmed 26-Apr-11 21:17pm    
i am not sure what you mean by modify it?

1 solution

Ok. Here is what you looking for. Add the following method in addition to the methods that already have it.
Call GroupbyDeptAndSumNetPay() in the main entry.


///
/// Group by Department and Sum the Net Pay for each department
///

public void GroupbyDeptAndSumNetPay()
{
DataTable dt = getData();
StringBuilder sb = new StringBuilder("");
DataView dv = dt.DefaultView;

dv.RowFilter = "Dept = 'M'";
sb.Append(dumpColumn(dt.Columns));
sb.AppendLine();
foreach (DataRowView item in dv)
{
sb.Append(dumpRow(dt.Columns, item));
sb.AppendLine();
}
sb.AppendLine();
var totalNetPay_M = dt.Compute("Sum(Net)", "Dept = 'M'");
sb.AppendLine();
sb.Append("Total Net Pay : " + totalNetPay_M.ToString());
sb.AppendLine();
sb.AppendLine();

dv = dt.DefaultView;
dv.RowFilter = "Dept = 'R'";
sb.Append(dumpColumn(dt.Columns));
sb.AppendLine();
foreach (DataRowView item in dv)
{
sb.Append(dumpRow(dt.Columns, item));
sb.AppendLine();
}
sb.AppendLine();
var totalNetPay_R = dt.Compute("Sum(Net)", "Dept = 'R'");
sb.AppendLine();
sb.Append("Total Net Pay : " + totalNetPay_R.ToString());
sb.AppendLine();
sb.AppendLine();

dv = dt.DefaultView;
dv.RowFilter = "Dept = 'S'";
sb.Append(dumpColumn(dt.Columns));
sb.AppendLine();
foreach (DataRowView item in dv)
{
sb.Append(dumpRow(dt.Columns, item));
sb.AppendLine();
}
sb.AppendLine();
var totalNetPay_S = dt.Compute("Sum(Net)", "Dept = 'S'");
sb.AppendLine();
sb.Append("Total Net Pay : " + totalNetPay_S.ToString());

Console.WriteLine(sb.ToString());
}

///
/// Dump Column names
/// ;
/// <param name="dcc" />DataColumnCollection value;
/// <returns>Header value;
private string dumpColumn(DataColumnCollection dcc)
{
StringBuilder sb = new StringBuilder("");
foreach (DataColumn dc in dcc)
{
sb.AppendFormat("{0,-9}", dc.ColumnName);
}
return sb.ToString();
}

///
/// Dump Row value
///

/// <param name="dcc" />Column Collection value
/// <param name="drv" />DataViewRow value
/// <returns>Value of each column
private string dumpRow(DataColumnCollection dcc, DataRowView drv)
{
StringBuilder sb = new StringBuilder("");
foreach (DataColumn dc in dcc)
{
sb.AppendFormat("{0,-9}", drv[dc.ColumnName].ToString());
}
return sb.ToString();
}

///
/// Convert the CSV data to DataTable
///

/// <returns>DataTable object
public DataTable getData()
{
List<string> columns;
List<dictionary><string,>> myData = GetData(out columns);
DataTable dt = new DataTable();

foreach (string column in columns)
{
if (column.Contains("Net"))
dt.Columns.Add(new DataColumn(column.Replace("\\", "").Replace("\"","").ToString(), typeof(double)));
else
dt.Columns.Add(new DataColumn(column.Replace("\\", "").Replace("\"","").ToString()));
}

foreach (Dictionary<string,> row in myData)
{
DataRow dr = dt.NewRow();
foreach (string column in columns)
{
string value = row[column].Replace("\\", "").Replace("\"","").ToString());
if (column.Contains("Net"))
dr[column.Replace("\\", "").Replace("\"","").ToString())] = double.Parse(value);
else
dr[column.Replace("\\", "").Replace("\"","").ToString())] = value;
}
dt.Rows.Add(dr);
}
return dt;
}
 
Share this answer
 
v2
Comments
fazleh ahmed 27-Apr-11 9:35am    
You are gem. If u are ever in Birmingham UK, I will buy you a pint or more depending on how many I have had. Cheers mate.
Wonde Tadesse 6-Dec-11 10:38am    
Thanks mate. Don't forget the pint. I'll come UK anytime soon.:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900