none
Filtering DataTable RRS feed

  • Question

  • I have a datatable that contains student information.  For example, let's say that it it set-up like this - how could I create a csv file for EACH teacher listed that contains all columns of the DataTable.  Meaning, I'd have a csv file for Teacher 1, Teacher 11, and Teacher 12?

            DatastudentsInfo studentsInfo = new DatastudentsInfo();
            studentsInfo.Columns.Add("studentID", typeof(int));
            studentsInfo.Columns.Add("studentName", typeof(string));
            studentsInfo.Columns.Add("Teacher", typeof(string));
            studentsInfo.Rows.Add(25, "Student 1", "Teacher 1");
            studentsInfo.Rows.Add(50, "Student 2", "Teacher 1");
            studentsInfo.Rows.Add(10, "Student 3", "Teacher 1");
            studentsInfo.Rows.Add(21, "Student 4", "Teacher 1");
            studentsInfo.Rows.Add(251, "Student 11", "Teacher 11");
            studentsInfo.Rows.Add(501, "Student 21", "Teacher 11");
            studentsInfo.Rows.Add(101, "Student 31", "Teacher 11");
            studentsInfo.Rows.Add(211, "Student 41", "Teacher 11");
    		studentsInfo.Rows.Add(215, "Student 12", "Teacher 12");
            studentsInfo.Rows.Add(510, "Student 22", "Teacher 12");
            studentsInfo.Rows.Add(110, "Student 32", "Teacher 12");
            studentsInfo.Rows.Add(211, "Student 42", "Teacher 12");

    Wednesday, August 28, 2019 5:36 PM

Answers

  • Using .NET DataTable here is everything to extract data via group-by. Using a StringBuilder, append each data row to it then save via File.WriteAllText.

    using System;
    using System.Data;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var dt = new DataTable { TableName = "Students" };
    
                dt.Columns.Add(new DataColumn { ColumnName = "studentID", DataType = typeof(int) });
                dt.Columns.Add(new DataColumn { ColumnName = "studentName", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn { ColumnName = "Teacher", DataType = typeof(string) });
    
                dt.Rows.Add(new object[] { 25, "Student 1", "Teacher 1"});
                dt.Rows.Add(new object[] { 50, "Student 2", "Teacher 1"});
                dt.Rows.Add(new object[] { 10, "Student 3", "Teacher 1"});
                dt.Rows.Add(new object[] { 21, "Student 4", "Teacher 1"});
                dt.Rows.Add(new object[] { 251, "Student 11", "Teacher 11"});
                dt.Rows.Add(new object[] { 501, "Student 21", "Teacher 11"});
                dt.Rows.Add(new object[] { 101, "Student 31", "Teacher 11"});
                dt.Rows.Add(new object[] { 211, "Student 41", "Teacher 11"});
                dt.Rows.Add(new object[] { 215, "Student 12", "Teacher 13"});
                dt.Rows.Add(new object[] { 510, "Student 22", "Teacher 12"});
                dt.Rows.Add(new object[] { 110, "Student 32", "Teacher 12"});
                dt.Rows.Add(new object[] { 211, "Student 42", "Teacher 12"});
    
                var studentData = dt.AsEnumerable()
                    .GroupBy((student) => student.Field<string>("Teacher"))
                    .Select((group) => new
                    {
                        Teacher = group.Key,
                        DataRowList = group.OrderBy(
                            (dataRow) => dataRow.Field<string>("studentName"))
                            .ToList()
                    }).OrderBy(x => x.Teacher).ToList();
    
    
                foreach (var row in studentData)
                {
                    Console.WriteLine($"Teacher's name {row.Teacher}");
                    for (var index = 0; index < row.DataRowList.Count; index++)
                    {
                        Console.WriteLine(" " + string.Join(",", row.DataRowList[index].ItemArray.ToList().Take(2)));
                    }
    
                    Console.WriteLine();
                }
    
            }
        }
    }
    

    Results in the IDE Output window

    Teacher's name Teacher 1
     25,Student 1
     50,Student 2
     10,Student 3
     21,Student 4
    
    Teacher's name Teacher 11
     251,Student 11
     501,Student 21
     101,Student 31
     211,Student 41
    
    Teacher's name Teacher 12
     510,Student 22
     110,Student 32
     211,Student 42
    
    Teacher's name Teacher 13
     215,Student 12
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by IndigoMontoya Wednesday, August 28, 2019 7:05 PM
    Wednesday, August 28, 2019 6:17 PM
    Moderator

All replies

  • Using .NET DataTable here is everything to extract data via group-by. Using a StringBuilder, append each data row to it then save via File.WriteAllText.

    using System;
    using System.Data;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var dt = new DataTable { TableName = "Students" };
    
                dt.Columns.Add(new DataColumn { ColumnName = "studentID", DataType = typeof(int) });
                dt.Columns.Add(new DataColumn { ColumnName = "studentName", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn { ColumnName = "Teacher", DataType = typeof(string) });
    
                dt.Rows.Add(new object[] { 25, "Student 1", "Teacher 1"});
                dt.Rows.Add(new object[] { 50, "Student 2", "Teacher 1"});
                dt.Rows.Add(new object[] { 10, "Student 3", "Teacher 1"});
                dt.Rows.Add(new object[] { 21, "Student 4", "Teacher 1"});
                dt.Rows.Add(new object[] { 251, "Student 11", "Teacher 11"});
                dt.Rows.Add(new object[] { 501, "Student 21", "Teacher 11"});
                dt.Rows.Add(new object[] { 101, "Student 31", "Teacher 11"});
                dt.Rows.Add(new object[] { 211, "Student 41", "Teacher 11"});
                dt.Rows.Add(new object[] { 215, "Student 12", "Teacher 13"});
                dt.Rows.Add(new object[] { 510, "Student 22", "Teacher 12"});
                dt.Rows.Add(new object[] { 110, "Student 32", "Teacher 12"});
                dt.Rows.Add(new object[] { 211, "Student 42", "Teacher 12"});
    
                var studentData = dt.AsEnumerable()
                    .GroupBy((student) => student.Field<string>("Teacher"))
                    .Select((group) => new
                    {
                        Teacher = group.Key,
                        DataRowList = group.OrderBy(
                            (dataRow) => dataRow.Field<string>("studentName"))
                            .ToList()
                    }).OrderBy(x => x.Teacher).ToList();
    
    
                foreach (var row in studentData)
                {
                    Console.WriteLine($"Teacher's name {row.Teacher}");
                    for (var index = 0; index < row.DataRowList.Count; index++)
                    {
                        Console.WriteLine(" " + string.Join(",", row.DataRowList[index].ItemArray.ToList().Take(2)));
                    }
    
                    Console.WriteLine();
                }
    
            }
        }
    }
    

    Results in the IDE Output window

    Teacher's name Teacher 1
     25,Student 1
     50,Student 2
     10,Student 3
     21,Student 4
    
    Teacher's name Teacher 11
     251,Student 11
     501,Student 21
     101,Student 31
     211,Student 41
    
    Teacher's name Teacher 12
     510,Student 22
     110,Student 32
     211,Student 42
    
    Teacher's name Teacher 13
     215,Student 12
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by IndigoMontoya Wednesday, August 28, 2019 7:05 PM
    Wednesday, August 28, 2019 6:17 PM
    Moderator
  • Using .NET DataTable here is everything to extract data via group-by. Using a StringBuilder, append each data row to it then save via File.WriteAllText.

    using System;
    using System.Data;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var dt = new DataTable { TableName = "Students" };
    
                dt.Columns.Add(new DataColumn { ColumnName = "studentID", DataType = typeof(int) });
                dt.Columns.Add(new DataColumn { ColumnName = "studentName", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn { ColumnName = "Teacher", DataType = typeof(string) });
    
                dt.Rows.Add(new object[] { 25, "Student 1", "Teacher 1"});
                dt.Rows.Add(new object[] { 50, "Student 2", "Teacher 1"});
                dt.Rows.Add(new object[] { 10, "Student 3", "Teacher 1"});
                dt.Rows.Add(new object[] { 21, "Student 4", "Teacher 1"});
                dt.Rows.Add(new object[] { 251, "Student 11", "Teacher 11"});
                dt.Rows.Add(new object[] { 501, "Student 21", "Teacher 11"});
                dt.Rows.Add(new object[] { 101, "Student 31", "Teacher 11"});
                dt.Rows.Add(new object[] { 211, "Student 41", "Teacher 11"});
                dt.Rows.Add(new object[] { 215, "Student 12", "Teacher 13"});
                dt.Rows.Add(new object[] { 510, "Student 22", "Teacher 12"});
                dt.Rows.Add(new object[] { 110, "Student 32", "Teacher 12"});
                dt.Rows.Add(new object[] { 211, "Student 42", "Teacher 12"});
    
                var studentData = dt.AsEnumerable()
                    .GroupBy((student) => student.Field<string>("Teacher"))
                    .Select((group) => new
                    {
                        Teacher = group.Key,
                        DataRowList = group.OrderBy(
                            (dataRow) => dataRow.Field<string>("studentName"))
                            .ToList()
                    }).OrderBy(x => x.Teacher).ToList();
    
    
                foreach (var row in studentData)
                {
                    Console.WriteLine($"Teacher's name {row.Teacher}");
                    for (var index = 0; index < row.DataRowList.Count; index++)
                    {
                        Console.WriteLine(" " + string.Join(",", row.DataRowList[index].ItemArray.ToList().Take(2)));
                    }
    
                    Console.WriteLine();
                }
    
            }
        }
    }

    Results in the IDE Output window

    Teacher's name Teacher 1
     25,Student 1
     50,Student 2
     10,Student 3
     21,Student 4
    
    Teacher's name Teacher 11
     251,Student 11
     501,Student 21
     101,Student 31
     211,Student 41
    
    Teacher's name Teacher 12
     510,Student 22
     110,Student 32
     211,Student 42
    
    Teacher's name Teacher 13
     215,Student 12


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Is there anyway to convert it to an IENumerable so I can do the below to export to csv?

    IEnumerable<string> studentData = dt.AsEnumerable()
                    .GroupBy((student) => student.Field<string>("Teacher"))
                    .Select((group) => new
                    {
                        Teacher = group.Key,
                        DataRowList = group.OrderBy(
                            (dataRow) => dataRow.Field<string>("studentName"))
                            .ToList()
                    }).OrderBy(x => x.Teacher).ToList();
    foreach (DataRow row in dt.Rows)
    {
    	IEnumerable<string> fields = row.ItemArray.Select(field => string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
    	sb.AppendLine(string.Join(",", fields));
    }


    Wednesday, August 28, 2019 8:03 PM
  • Looks like you want to place quotes around each value.

    var dt = new DataTable {TableName = "Students"};
    
    dt.Columns.Add(new DataColumn {ColumnName = "studentID", DataType = typeof(int)});
    dt.Columns.Add(new DataColumn {ColumnName = "studentName", DataType = typeof(string)});
    dt.Columns.Add(new DataColumn {ColumnName = "Teacher", DataType = typeof(string)});
    
    dt.Rows.Add(25, "Student 1", "Teacher 1");
    dt.Rows.Add(50, "Student 2", "Teacher 1");
    dt.Rows.Add(10, "Student 3", "Teacher 1");
    dt.Rows.Add(21, "Student 4", "Teacher 1");
    dt.Rows.Add(251, "Student 11", "Teacher 11");
    dt.Rows.Add(501, "Student 21", "Teacher 11");
    dt.Rows.Add(101, "Student 31", "Teacher 11");
    dt.Rows.Add(211, "Student 41", "Teacher 11");
    dt.Rows.Add(215, "Student 12", "Teacher 13");
    dt.Rows.Add(510, "Student 22", "Teacher 12");
    dt.Rows.Add(110, "Student 32", "Teacher 12");
    dt.Rows.Add(211, "Student 42", "Teacher 12");
    
    var studentData = dt.AsEnumerable()
        .GroupBy((student) => student.Field<string>("Teacher"))
        .Select((group) => new
        {
            Teacher = group.Key,
            DataRowList = group.OrderBy(
                    (dataRow) => dataRow.Field<string>("studentName"))
                .ToList()
        }).OrderBy(x => x.Teacher).ToList();
    
    
    foreach (var row in studentData)
    {
        Console.WriteLine($"Teacher's name {row.Teacher}");
    
        for (var index = 0; index < row.DataRowList.Count; index++)
        {
            var items = string.Join(",", Array.ConvertAll(
                row.DataRowList[index].ItemArray, value => value?.ToString() ?? string.Empty).ToList()
                .Select(item => $"\"{item}\""));
    
            Console.WriteLine(items);
        }
    
        Console.WriteLine();
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, August 29, 2019 2:45 AM
    Moderator