How to group multiple columns and count records with LINQ from a CSV file in C #

Question

How to group multiple columns and count records with LINQ from a CSV file in C #

Navigation

#1 by (3 votes)

0

I have a problem grouping several columns and counting your records in each column by reading it from a CSV file, Example CSV records:

Nombre,Apellido,FechaDeRegistro,Aceptado
David,tod,09/09/2014,SI
David,lopez,09/09/2011,NO
David,cortez,09/09/2011,SI
Maurice,perez,09/09/2012,SI
Maurice,ruiz,09/09/2013,NO

The user in a form will select a period of dates eg. 01/01/2010 and 12/31/2015 the program should show in a DataGridView, how many people with the name of David and Mauricio were accepted to the university and how many were not within that period. And He must show it like this:

Nombre,TotaldePersonas,Aceptadas,NoAceptadas
David,3,2,1
Maurice,2,1,1

I have the code to count the total of people who were in that period, but I do not know how to do it for multiple columns and count it.

 DataTable dtDataSource = new DataTable();

csvfile = localitation;
            fileContent = File.ReadAllLines(csvfile);

                dtDataSource.Columns.Add(Nombre);
                dtDataSource.Columns.Add("Total");

                var people = fileContent
                    .Skip(1)
                    .Select(line =>
                    {
                        var parts = line.Split(',');

                        return new Person
                        {

                            Nombre = parts[0],
                            Apellido = parts[1],
                            FechaRegistro = DateTime.ParseExact(parts[2], "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture),
                        };
                    });



                var groups = people
                .Where(p => p.FechaRegistro >= DateTime.ParseExact(DateIn.Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) && p.FechaRegistro <= DateTime.ParseExact(DateFin.Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture))
               .GroupBy(p => p.Nombre)
               .Select(firstNameGroup => new
               {
                   Name = firstNameGroup.Key,
                   Count = firstNameGroup.Count(),

               });


                foreach (var group in groups)
                {

                    dtDataSource.Rows.Add(group.Name, group.Count);

                }

if (dtDataSource != null)
                {

                    dataGridViewReporte.DataSource = dtDataSource;

                }

With a class called Person

class Person
{
    public string Nombre { get; set; }
    public string Apellido { get; set; }
    public DateTime FechaRegistro { get; set; }
 // public string Aceptado { get; set; }
}

I do not know how to do it with this logic or if there is another easier to implement for my serious help, thank you! EYE This code was adapted in such a way that it could be understood .. and I found it in the community in English, if there is a better and simpler solution, I would appreciate it very much.

c# linq csv

asked by David Tod 07.12.2016 в 16:31

source

1 answer

Export class with express Focus on textbox in C #

score 3 · Accepted Answer

That's how I would do it. First, in the Person class the accepted field would turn it into a bool, thus:

class Person
{
    public string Nombre { get; set; }
    public string Apellido { get; set; }
    public DateTime FechaRegistro { get; set; }
    public bool Aceptado { get; set; }
}

Then, at the time of passing the csv to a List<Person> , it would be like this:

var people = fileContent
            .Skip(1)
            .Select(line =>
            {
                var parts = line.Split(',');

                return new Person
                {

                    Nombre = parts[0],
                    Apellido = parts[1],
                    FechaRegistro = DateTime.ParseExact(parts[2], "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture),
                    Aceptado=parts[3]=="SI"?true : false
                };
            });

Finally, here the list you needed is generated:

DateTime dtIni = DateTime.Parse("01/01/2010");
DateTime dtFin = DateTime.Parse("31/12/2015");

var lista = people.Where(x => (x.FechaRegistro > dtIni && x.FechaRegistro < dtFin))
                            .GroupBy(x=>x.Nombre)
                            .Select(firstNameGroup=>new
                            {
                                Name = firstNameGroup.Key,
                                Count = firstNameGroup.Count(),
                                Aceptados = firstNameGroup.Count(x=>x.Aceptado),
                                NoAceptados = firstNameGroup.Count(x => !x.Aceptado)

                            });