C# - Datatable to CSV

watch_later Sunday, March 1, 2020

Introduction


This article gives an explanation about convert Datatable to CSV in c# and explains the efficient way to write CSV files from Datatable as well as show you how to read records from the Datatable using LINQ and write it into CSV file using c#.


Export Datatable to CSV

I have also written an article about export Dataset/Datatable to CSV file using c# and vb.net, recently in the few months, I got many requests from developers and beginners via email regarding post an article for export Datatable to CSV with a faster and efficient way using c# compare to the previously written article. So, today in this article I'll explain the same with different logic for export Datatable to CSV with the help of LINQ using c# 

Requirement 


1) Create a method that returns a Datatable
2) Export Datatable to.CSV File.

Implementation


So, Let's start with a demonstration and design a web form with grid view and one simple button, where we will display records of Datatable to the user with help of Gridview and while the user will click on the button, all the data/records of Datatable will export into CSV file. 

HTML

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="articles_2020_03_CS" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Datatable to CSV</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css" />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>
    <form id="form1" runat="server">
    <div class="container">
           <div class="form-row">
               <br />
                <div class="form-group col-md-12">
                   <center><h2>Product Sales Details</h2></center>
                </div>
            </div>
            <div class="form-row">
                
                <div class="form-group col-md-12">
                    <div class="panel panel-default ">
                        <div class="panel-heading">Sales Grid</div>
                        <div class="panel-body">
                            <asp:GridView ID="grdCustomer" runat="server" CssClass="table table-bordered active active" AutoGenerateColumns="false" EmptyDataText="No records has been found.">
                                 <Columns>
                                     <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" ItemStyle-Width="15" />
                                     <asp:BoundField DataField="CustomerName" HeaderText="CustomerName" ItemStyle-Width="300" />
                                     <asp:BoundField DataField="ProductName" HeaderText="ProductName" ItemStyle-Width="100" />
                                     <asp:BoundField DataField="Price" HeaderText="Price" ItemStyle-Width="50" />
                                 </Columns>
                            </asp:GridView>
                        </div>
                    </div>
                </div>
                <div class="form-group col-md-2">
                    <asp:Button ID="btnExportCSV" runat="server" CssClass="btn btn-success" Text="Export to CSV" OnClick="btnExportCSV_Click" />
                </div>
            </div>
        </div>
    </form>
</body>
</html>
Datatable to CSV

To, write a CSV file we need a Datatable and hear I will create a simple method that returns a Datatable using c# for demonstration.
public DataTable GetData()
    {
        DataTable dt = new DataTable();
        try
        {
            dt.Columns.Add("CustomerId"typeof(int));
            dt.Columns.Add("CustomerName"typeof(string));
            dt.Columns.Add("ProductName"typeof(string));
            dt.Columns.Add("Price"typeof(double));
            dt.Rows.Add(1, "Nikunj Satasiya""Laptop", 37000);
            dt.Rows.Add(2, "Hiren Dobariya""Mouse", 820);
            dt.Rows.Add(3, "Vivek Ghadiya""Pen", 250);
            dt.Rows.Add(4, "Pratik Pansuriya""Laptop", 42000);
            dt.Rows.Add(5, "Sneha Patel""Lip Bam", 130);
            dt.Rows.Add(6, "Jhon Smith""Sigar", 150);
            return dt;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return null;
        }
        finally
        {
            if (dt != null)
            {
                dt.Dispose();
                dt = null;
            }
        }
 
    }
Now, we will bind created GridView and assign the Datatable as a Datasource to the Gridview while page load.
protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            try
            {
                grdCustomer.DataSource = GetData();
                grdCustomer.DataBind();
            }
            catch (Exception)
            {
 
                throw;
            }
        }
        catch (Exception)
        {
 
            throw;
        }
    }
Now, we will write the following code in the click event of a button "Export To CSV", for export all the records of Datatable to CSV file.
protected void btnExportCSV_Click(object sender, EventArgs e)
   {
 
       try
       {
           DataTable dtCSV = new DataTable();
 
           // Cast datasource of gridview to datatable
           dtCSV = (DataTable)grdCustomer.DataSource;
 
           //checked for the datatable dtCSV not empty
           if (dtCSV != null && dtCSV.Rows.Count > 0)
           {
               // create object for the StringBuilder class
               StringBuilder sb = new StringBuilder();
 
               // Get name of columns from datatable and assigned to the string array
               string[] columnNames = dtCSV.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
 
               // Create comma sprated column name based on the items contains string array columnNames
               sb.AppendLine(string.Join(",", columnNames));
 
               // Fatch rows from datatable and append values as comma saprated to the object of StringBuilder class 
               foreach (DataRow row in dtCSV.Rows)
               {
                   IEnumerable<string> fields = row.ItemArray.Select(field => string.Concat("\"", field.ToString().Replace("\"""\"\""), "\""));
                   sb.AppendLine(string.Join(",", fields));
               }
 
               // save the file
               File.WriteAllText(@"D:\Codingvila.csv", sb.ToString());
           }
       }
       catch (Exception ex)
       {
           Console.WriteLine(ex.ToString());
           throw;
       }
 
   }

Explanation


As you can see in the code above here we have created an object of Datatable dtCSV and cast the Datasource of Gridview to Datatable and assign to the Datatable dtCSV, Then we have checked the condition for the Datatable dtCSV weather Datatable dtCSV is empty or not and if it is not empty and has records then we have created an object for the StringBuilder class "sb" and fetch name of columns from Datatable and assigned to the string array "columnNames" and finally join the items of array columnNames" with comma appended into object of StringBuilder using the AppendLine. Finally, with the help of loop fetch the records from the Datatable and using the LINQ selects records of the Datarow and stored result into local IEnumerable variable called fields and join it with a comma and appended into an object of StringBuilder using the AppendLine.

Finally, with the help of File.WriteAllText creates a new file and writes a specified string contained objects of the StringBuilder class "sb" and then closes the file. 

Note: If the target file already exists then File.WriteAllText will overwrite the file.

Output

Convert Datatable to CSV

Summary


In this article, we learned an efficient way to export Datatable to CSV files with the help of LINQ in C#.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon