Export Dataset/Datatable to CSV File Using C# and VB.NET

Codingvila
Codingvila
January 17, 2023
3
In this article, I will show you how to write CSV files using dataset/datatable in C# and VB.NET and also explain how you can export dataset/datable in CSV file in c# and vb.net.

while you working with CSV and trying to write CSV files suppose you have data with a comma(,) and double quote("") then may you can't write CSV as per your requirement, and may your CSV will not be generated properly as per your requirement.


Export Dataset/Datatable to CSV File Using C# and VB.NET
Export Dataset/Datatable to CSV File Using C# and VB.NET

There is a possibility if you working with big data applications and you have lots of data and clients give you a requirement like they want to export that all the data in excel but due to too larger data and you may get an exception like out of memory exception error, So there is the best option is you can write CSV file and fix this issue because CSV file can accept this kind of requirement.
Today I faced the same issue in my office actually our client said they need all the data for the whole year 2017-2018 with every transaction and its history also,  and I have created a stored procedure and returned datatable now I have datatable and I directly export that in .xls file and I got error like "Out of Memory Exception Error", and to fix this issue I have search too many websites on the internet but didn't get solution as per my expectation, then my TL said "Nikunj you can try and export that all the data In . CSV file that may help you" and now I have created one custom method and implemented in my c# code and really that helps me I got a solution as per my requirement.

Now, to overcome this kind of issue here I will show you a custom method that I have created "WriteCSV" in the below article with an example.

Requirement

1) Create DataSet/Datatable
2) Write a Custom Method/Function for Write. CSV file
3) Export Dataset/Datatable to. CSV File.

Implementation

First, to write a CSV file we need a dataset/datatable and hear I creating a datatable using c# for demonstration, you can get a datatable using stored procedure also as per your need.

C#

DataTable GetData()
       {
           DataTable dt = new DataTable();
           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;
       }

VB

Private Function GetData() As DataTable
        Dim dt As DataTable = New DataTable
        dt.Columns.Add("CustomerId", GetType(System.Int32))
        dt.Columns.Add("CustomerName", GetType(System.String))
        dt.Columns.Add("ProductName", GetType(System.String))
        dt.Columns.Add("Price", GetType(System.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
    End Function
Datatable
Datatable

Now We will write a custom function/method for writing CSV files. 

C#

public static string WriteCSV(string input)
        {
            try
            {
                if (input == null)
                    return string.Empty;
 
                bool containsQuote = false;
                bool containsComma = false;
                int len = input.Length;
                for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++)
                {
                    char ch = input[i];
                    if (ch == '"')
                        containsQuote = true;
                    else if (ch == ',')
                        containsComma = true;
                }
 
                if (containsQuote && containsComma)
                    input = input.Replace("\"", "\"\"");
 
                if (containsComma)
                    return "\"" + input + "\"";
                else
                    return input;
            }
            catch
            {
                throw;
            }
        }

VB

Public Shared Function WriteCSV(ByVal input As String) As String
        Try
            If (input Is Nothing) Then
                Return String.Empty
            End If
 
            Dim containsQuote As Boolean = False
            Dim containsComma As Boolean = False
            Dim len As Integer = input.Length
            Dim i As Integer = 0
            Do While ((i < len) _
                        AndAlso ((containsComma = False) _
                        OrElse (containsQuote = False)))
                Dim ch As Char = input(i)
                If (ch = Microsoft.VisualBasic.ChrW(34)) Then
                    containsQuote = True
                ElseIf (ch = Microsoft.VisualBasic.ChrW(44)) Then
                    containsComma = True
                End If
 
                i = (i + 1)
            Loop
 
            If (containsQuote AndAlso containsComma) Then
                input = input.Replace(""""", ", """"")", if, (containsCommaUnknown, """"" + input + ", "")
            Else
                Return input
            End If
 
        Catch  As System.Exception
            Throw
        End Try
 
    End Function
Now With the click of a button write the following code

C#

private void btnCSV_Click(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            DataTable dt = GetData();
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                    sb.Append(WriteCSV(dr[dc.ColumnName].ToString()) + ",");
                sb.Remove(sb.Length - 1, 1);
                sb.AppendLine();
            }
            File.WriteAllText(@"C:\Users\Nikunj-PC\Desktop\Demo\CodingvilaCSV.csv", sb.ToString());
            lblMsg.Text = "Successful";
        }

VB

Private Sub btnCSV_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim sb As StringBuilder = New StringBuilder
        Dim dt As DataTable = GetData
        For Each dr As DataRow In dt.Rows
            For Each dc As DataColumn In dt.Columns
                sb.Append((WriteCSV(dr(dc.ColumnName).ToString) + ","))
            Next
            sb.Remove((sb.Length - 1), 1)
            sb.AppendLine()
        Next
        File.WriteAllText("C:\Users\Nikunj-PC\Desktop\Demo\CodingvilaCSV.csv", sb.ToString)
        lblMsg.Text = "Successful"
    End Sub
Visualizer
Visualizer

If you analyzed the above code then here I have one CSV file in my desktop location and I want to write that CSV file so I give a fixed file path. you can use the file open dialog also as per your need as well as also can change the path if you have filed file location.

Output

CSV File
CSV File

Summary

This article explains how you can export dataset/datatable to a CSV file and how to write a CSV file using c# and vb.net.
Tags
Share to other apps
Codingvila

Codingvila

Codingvila is dedicated to helping you learn and grow as a programmer. We offer a wide range of resources, including tutorials, articles, and code snippets. This website covers a variety of programming languages, including Angular, .NET, Java, and more. Our goal is to provide you with the tools and knowledge you need to succeed in your programming journey.

You may like these posts

Show more

Post a Comment

3 Comments

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

  1. David HamsteadApril 16, 2019 at 1:11 AM

    Good example. Thank you! It helped me with a project I am working on. There were some errors with the VB code for the input function. I fixed them in the code below.

    Public Shared Function WriteCSV(ByVal input As String) As String
    Try
    If (input Is Nothing) Then
    Return String.Empty
    End If
    Dim containsQuote As Boolean = False
    Dim containsComma As Boolean = False
    Dim len As Integer = input.Length
    Dim i As Integer = 0
    Do While ((i < len) _
    AndAlso ((containsComma = False) _
    OrElse (containsQuote = False)))
    Dim ch As Char = input(i)
    If (ch = Microsoft.VisualBasic.ChrW(34)) Then
    containsQuote = True
    ElseIf (ch = Microsoft.VisualBasic.ChrW(44)) Then
    containsComma = True
    End If

    i = (i + 1)
    Loop
    If (containsQuote AndAlso containsComma) Then
    input = input.Replace("""", """""")
    End If
    If (containsComma) Then
    Return """" & input & """"
    Else
    Return input
    End If
    Catch ex As Exception
    Throw
    End Try
    End Function

    ReplyDelete
  2. Nikunj SatasiyaApril 16, 2019 at 5:46 PM

    Thank you for your valuable feedback. I will review your code and update the code in the article that you have fixed.

    ReplyDelete
  3. morrisMarch 12, 2021 at 4:49 AM

    Please continue this great work and I look forward to more of your awesome blog posts. view

    ReplyDelete
Join the conversation (3)
To Top