none
Create CSV File For Each Value In DataTable RRS feed

  • Question

  • Hi!  I have a DataTable with structure like this.  How can I create a csv for each teacher listed in the DataTable?  (This sample only shows 2 teachers.  My production code has 14).  I need all columns from the DataTable, including the column headers exported to the csv.

    private void CreateDataTable()
            {
                table.Columns.Add("studentname", typeof(string));
                table.Columns.Add("teacher", typeof(string));
                table.Columns.Add("grade", typeof(string));
                table.Rows.Add("Jason", "Sarah", "A");
                table.Rows.Add("James", "Sarah", "A");
                table.Rows.Add("Jim", "Sarah", "A");
                table.Rows.Add("Jack", "Sarah", "A");
                table.Rows.Add("Bill", "Sarah", "A");
                table.Rows.Add("Bob", "Sarah", "A");
                table.Rows.Add("Beng", "Sarah", "A");
                table.Rows.Add("Brandy", "Sarah", "A");
                table.Rows.Add("Cal", "Sarah", "A");
                table.Rows.Add("Carl", "Sarah", "B");
                table.Rows.Add("Chuck", "Sarah", "B");
                table.Rows.Add("Nate", "Sarah", "B");
                table.Rows.Add("Nick", "Sarah", "B");
                table.Rows.Add("Nooch", "Sarah", "B");
                table.Rows.Add("Liam", "Sarah", "B");
                table.Rows.Add("Lan", "Sarah", "B");
                table.Rows.Add("Len", "Sarah", "B");
                table.Rows.Add("Noel", "Sarah", "B");
                table.Rows.Add("Nobe", "Sarah", "B");
                table.Rows.Add("Star", "Sarah", "C");
                table.Rows.Add("Stella", "Sarah", "C");
                table.Rows.Add("Jason", "Bill", "F");
                table.Rows.Add("James", "Bill", "F");
                table.Rows.Add("Jim", "Bill", "A");
                table.Rows.Add("Jack", "Bill", "A");
                table.Rows.Add("Bill", "Bill", "C");
                table.Rows.Add("Bob", "Bill", "C");
                table.Rows.Add("Beng", "Bill", "C");
                table.Rows.Add("Brandy", "Bill", "A");
                table.Rows.Add("Cal", "Bill", "A");
                table.Rows.Add("Carl", "Bill", "B");
                table.Rows.Add("Chuck", "Bill", "B");
                table.Rows.Add("Nate", "Bill", "B");
                table.Rows.Add("Nick", "Bill", "B");
                table.Rows.Add("Nooch", "Bill", "A");
                table.Rows.Add("Liam", "Bill", "B");
                table.Rows.Add("Lan", "Bill", "B");
                table.Rows.Add("Len", "Bill", "B");
                table.Rows.Add("Noel", "Bill", "B");
                table.Rows.Add("Nobe", "Bill", "B");
                table.Rows.Add("Star", "Bill", "C");
                table.Rows.Add("Stella", "Bill", "C");
            }

    Monday, October 7, 2019 8:51 PM

Answers

  • Hello,

    Use this and File.WriteAllText to write the text in the StringBuilder.

    using System;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                Shown += Form1_Shown;
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                var dt = CreateDataTable();
    
                var StudentData = dt.AsEnumerable().GroupBy(
                        (student) => student.Field<string>("teacher"))
                    .Select((group) => new
                    {
                        Teacher = group.Key,
                        Students = group .Select((x) => x)
                    });
    
                foreach (var item in StudentData)
                {
                    var sb = new StringBuilder();
    
                    Console.WriteLine($"File Name will be '{item.Teacher}.csv'");
                    sb.AppendLine("StudentName,Teacher,Grade");
                    foreach (var row in item.Students)
                    {
                        sb.AppendLine(string.Join(",", row.ItemArray));
                    }
    
                    Console.WriteLine("save file using sb.ToString()");
                }
    
            }
    
            private DataTable CreateDataTable()
            {
                var table = new DataTable();
                table.Columns.Add("studentname", typeof(string));
                table.Columns.Add("teacher", typeof(string));
                table.Columns.Add("grade", typeof(string));
                table.Rows.Add("Jason", "Sarah", "A");
                table.Rows.Add("James", "Sarah", "A");
                table.Rows.Add("Jim", "Sarah", "A");
                table.Rows.Add("Jack", "Sarah", "A");
                table.Rows.Add("Bill", "Sarah", "A");
                table.Rows.Add("Bob", "Sarah", "A");
                table.Rows.Add("Beng", "Sarah", "A");
                table.Rows.Add("Brandy", "Sarah", "A");
                table.Rows.Add("Cal", "Sarah", "A");
                table.Rows.Add("Carl", "Sarah", "B");
                table.Rows.Add("Chuck", "Sarah", "B");
                table.Rows.Add("Nate", "Sarah", "B");
                table.Rows.Add("Nick", "Sarah", "B");
                table.Rows.Add("Nooch", "Sarah", "B");
                table.Rows.Add("Liam", "Sarah", "B");
                table.Rows.Add("Lan", "Sarah", "B");
                table.Rows.Add("Len", "Sarah", "B");
                table.Rows.Add("Noel", "Sarah", "B");
                table.Rows.Add("Nobe", "Sarah", "B");
                table.Rows.Add("Star", "Sarah", "C");
                table.Rows.Add("Stella", "Sarah", "C");
                table.Rows.Add("Jason", "Bill", "F");
                table.Rows.Add("James", "Bill", "F");
                table.Rows.Add("Jim", "Bill", "A");
                table.Rows.Add("Jack", "Bill", "A");
                table.Rows.Add("Bill", "Bill", "C");
                table.Rows.Add("Bob", "Bill", "C");
                table.Rows.Add("Beng", "Bill", "C");
                table.Rows.Add("Brandy", "Bill", "A");
                table.Rows.Add("Cal", "Bill", "A");
                table.Rows.Add("Carl", "Bill", "B");
                table.Rows.Add("Chuck", "Bill", "B");
                table.Rows.Add("Nate", "Bill", "B");
                table.Rows.Add("Nick", "Bill", "B");
                table.Rows.Add("Nooch", "Bill", "A");
                table.Rows.Add("Liam", "Bill", "B");
                table.Rows.Add("Lan", "Bill", "B");
                table.Rows.Add("Len", "Bill", "B");
                table.Rows.Add("Noel", "Bill", "B");
                table.Rows.Add("Nobe", "Bill", "B");
                table.Rows.Add("Star", "Bill", "C");
                table.Rows.Add("Stella", "Bill", "C");
    
                return table;
            }
        }
    }


    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


    Monday, October 7, 2019 9:43 PM
    Moderator

All replies

  • Hello,

    Please post the structure of the .csv file as it's unclear how you expect this to turn out e.g.



    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

    Monday, October 7, 2019 9:00 PM
    Moderator
  • Hi Karen -

    This is sample csv that shows a sampling of the data from datatable to illustrate how I need the data to be exported.  Please let me know if further is needed. 

    Thanks again!

    http://www.filedropper.com/book1_1

    Monday, October 7, 2019 9:08 PM
  • Hello,

    Use this and File.WriteAllText to write the text in the StringBuilder.

    using System;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                Shown += Form1_Shown;
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                var dt = CreateDataTable();
    
                var StudentData = dt.AsEnumerable().GroupBy(
                        (student) => student.Field<string>("teacher"))
                    .Select((group) => new
                    {
                        Teacher = group.Key,
                        Students = group .Select((x) => x)
                    });
    
                foreach (var item in StudentData)
                {
                    var sb = new StringBuilder();
    
                    Console.WriteLine($"File Name will be '{item.Teacher}.csv'");
                    sb.AppendLine("StudentName,Teacher,Grade");
                    foreach (var row in item.Students)
                    {
                        sb.AppendLine(string.Join(",", row.ItemArray));
                    }
    
                    Console.WriteLine("save file using sb.ToString()");
                }
    
            }
    
            private DataTable CreateDataTable()
            {
                var table = new DataTable();
                table.Columns.Add("studentname", typeof(string));
                table.Columns.Add("teacher", typeof(string));
                table.Columns.Add("grade", typeof(string));
                table.Rows.Add("Jason", "Sarah", "A");
                table.Rows.Add("James", "Sarah", "A");
                table.Rows.Add("Jim", "Sarah", "A");
                table.Rows.Add("Jack", "Sarah", "A");
                table.Rows.Add("Bill", "Sarah", "A");
                table.Rows.Add("Bob", "Sarah", "A");
                table.Rows.Add("Beng", "Sarah", "A");
                table.Rows.Add("Brandy", "Sarah", "A");
                table.Rows.Add("Cal", "Sarah", "A");
                table.Rows.Add("Carl", "Sarah", "B");
                table.Rows.Add("Chuck", "Sarah", "B");
                table.Rows.Add("Nate", "Sarah", "B");
                table.Rows.Add("Nick", "Sarah", "B");
                table.Rows.Add("Nooch", "Sarah", "B");
                table.Rows.Add("Liam", "Sarah", "B");
                table.Rows.Add("Lan", "Sarah", "B");
                table.Rows.Add("Len", "Sarah", "B");
                table.Rows.Add("Noel", "Sarah", "B");
                table.Rows.Add("Nobe", "Sarah", "B");
                table.Rows.Add("Star", "Sarah", "C");
                table.Rows.Add("Stella", "Sarah", "C");
                table.Rows.Add("Jason", "Bill", "F");
                table.Rows.Add("James", "Bill", "F");
                table.Rows.Add("Jim", "Bill", "A");
                table.Rows.Add("Jack", "Bill", "A");
                table.Rows.Add("Bill", "Bill", "C");
                table.Rows.Add("Bob", "Bill", "C");
                table.Rows.Add("Beng", "Bill", "C");
                table.Rows.Add("Brandy", "Bill", "A");
                table.Rows.Add("Cal", "Bill", "A");
                table.Rows.Add("Carl", "Bill", "B");
                table.Rows.Add("Chuck", "Bill", "B");
                table.Rows.Add("Nate", "Bill", "B");
                table.Rows.Add("Nick", "Bill", "B");
                table.Rows.Add("Nooch", "Bill", "A");
                table.Rows.Add("Liam", "Bill", "B");
                table.Rows.Add("Lan", "Bill", "B");
                table.Rows.Add("Len", "Bill", "B");
                table.Rows.Add("Noel", "Bill", "B");
                table.Rows.Add("Nobe", "Bill", "B");
                table.Rows.Add("Star", "Bill", "C");
                table.Rows.Add("Stella", "Bill", "C");
    
                return table;
            }
        }
    }


    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


    Monday, October 7, 2019 9:43 PM
    Moderator