Export CSV from Dataset


This article is written by Pon Saravanan  on 04-Jan-10 Last modified on :14-Jun-10





Export CSV from a DataTable VB.Net Tutorial

Before XML is widely used for data communications between the systems/applications, we have used CSV. Even now for when the new systems are being developed which needs to be communicated with these legacy applications, we have no choice if we cant choose anything other than those systems are using. I am planning to develop a class which can be useful for exporting CSV.

Delimiter

CSV is a character separated values format so we have to choose a character as a delimiter. As delimiters are chosen based on various factors we need our class to be delimiters configurable.

Text Qualifiers

As there will be a risk if the delimiter is already present in the data, the text qualifiers are used to identify the text element’s boundary. Within this boundary if a delimiter character is present then the class will not consider it as a delimiter.

Configurable Column Headers

Some of the interfaces will not like headers, so make a switch to turn off the column header generations.

Generate the CSV string

For generating the file with text quantifiers, surround the item with quantifier character both the side. Once done add a delimiter character next to that. If this is done for all rows then the string is ready to be written into the file system.

Source Code


Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Csv As New Exporter()
        Using CsvWriter As New StreamWriter("C:\TestFile.csv")
            CsvWriter.Write(Csv.CsvFromDatatable(GetSampleData()))
        End Using
        System.Diagnostics.Process.Start("C:\TestFile.csv")
        MessageBox.Show("done")
    End Sub
   
    Private Function GetSampleData() As DataTable
        Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;Integrated Security = True")
        Dim CategoryAdapter As New SqlDataAdapter("SELECT CategoryID,CategoryName,Description FROM Categories", con)
        Dim ProductData As New DataSet()
        CategoryAdapter.Fill(ProductData, "Categories")

        Return ProductData.Tables(0)
    End Function

End Class

Public Class Exporter
    Public Sub New()
        TextDelimiter = ","c
        TextQualifiers = """"c
        HasColumnHeaders = True
    End Sub
    Private _TextDelimiter As Char
    Public Property TextDelimiter() As Char
        Get
            Return _TextDelimiter
        End Get
        Set(ByVal value As Char)
            _TextDelimiter = value
        End Set
    End Property
    Private _TextQualifiers As Char
    Public Property TextQualifiers() As Char
        Get
            Return _TextQualifiers
        End Get
        Set(ByVal value As Char)
            _TextQualifiers = value
        End Set
    End Property
    Private _HasColumnHeaders As Boolean
    Public Property HasColumnHeaders() As Boolean
        Get
            Return _HasColumnHeaders
        End Get
        Set(ByVal value As Boolean)
            _HasColumnHeaders = value
        End Set
    End Property
    Public Function CsvFromDatatable(ByVal InputTable As DataTable) As String
        Dim CsvBuilder As New StringBuilder()
        If HasColumnHeaders Then
            CreateHeader(InputTable, CsvBuilder)
        End If
        CreateRows(InputTable, CsvBuilder)
        Return CsvBuilder.ToString()
    End Function
    Private Sub CreateRows(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
        For Each ExportRow As DataRow In InputTable.Rows
            For Each ExportColumn As DataColumn In InputTable.Columns
                Dim ColumnText As String = ExportRow(ExportColumn.ColumnName).ToString()
                ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
                CsvBuilder.Append(TextQualifiers + ColumnText + TextQualifiers)
                CsvBuilder.Append(TextDelimiter)
            Next
            CsvBuilder.AppendLine()
        Next
    End Sub
    Private Sub CreateHeader(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
        For Each ExportColumn As DataColumn In InputTable.Columns
            Dim ColumnText As String = ExportColumn.ColumnName.ToString()
            ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
            CsvBuilder.Append(TextQualifiers + ExportColumn.ColumnName + TextQualifiers)
            CsvBuilder.Append(TextDelimiter)
        Next
        CsvBuilder.AppendLine()
    End Sub
End Class






« Previous -
VB Send Email - Next »







Comments
  • GUEST
    really nice code..thanks for putting the code on internet.... 2/21/2011 4:59:42 AM

  • GUEST
    Hi,
    Exporter is throwing error..

    is any reference needed extra

    thanks
    3/21/2011 3:12:24 AM

  • pons
    It should work as it is. But what kind of exception you are getting? 3/21/2011 3:39:45 AM

  • GUEST
    Nice code 4/11/2011 4:10:06 AM

  • GUEST
    It's Working, thank bro.. 6/9/2011 12:20:40 AM

  • GUEST
    It throws me an error at CategoryAdapter.Fill(ProductData, "Categories") in the GetSampleData function. The error says SqlException was unhandled. Any ideas as to what is going on? thanks. 6/13/2011 3:34:45 PM

  • GUEST
    nice code................ tnx alot 7/24/2011 11:01:56 PM

  • GUEST
    Thank you very mouch. 8/11/2011 4:32:42 AM

  • GUEST
    Nice.. Thank you...... 9/7/2011 10:07:20 PM

  • GUEST
    Your a star. Thank you 9/8/2011 4:54:44 AM

  • GUEST
    Fantastic, this performed exactly as needed! Hats off to great code - this is much faster than trying to convert a dataset to Excel. 11/2/2011 7:34:05 PM

  • GUEST
    Thank,this usefully 11/7/2011 9:33:30 PM

  • GUEST
    Awesome... Thank you for the code. This saved me atleast 2 hours of development and testing time.

    Syed.
    11/16/2011 11:32:01 AM

  • GUEST
    Excelente trabajo, funcion de maravilla, gracias 11/16/2011 3:19:36 PM

  • GUEST
    Nice, but fixing the text qualifier as 1 char means if you don't want one, it puts a space either side of the delimiter. Also there is no test for the last column and you get an extra one with blank header and data.

    I added this:

    Dim x As Integer = ExportColumn.Ordinal
    If x (lessthan) InputTable.Columns.Count Then CsvBuilder.Append(TextDelimiter)

    Cheers, Deadeye
    12/12/2011 6:21:21 AM

  • GUEST
    Sorry, should be InputTable.Columns.Count - 1 12/12/2011 6:27:30 AM

  • GUEST
    I get an exception error...

    System.UnauthorizedAccessException was unhandled by user code
    Message=Access to the path 'C:\TestFile.csv' is denied.


    How do I get it to just pop open a window for download within the browser?
    1/10/2012 9:51:58 AM

  • GUEST
    Great work done
    Thanks a lot.
    2/21/2012 10:47:41 PM

  • GUEST
    Really good peace of work 3/20/2012 10:22:55 PM

  • GUEST
    Exception of type 'System.OutOfMemoryException' was thrown. 4/18/2013 4:57:35 AM

  • GUEST
    Awesome!!! 5/29/2013 2:50:13 PM

  • GUEST
    I have an error Type 'StringBuilder' is not defined.. 3/8/2014 3:05:46 AM


Comments
   
Captcha Image
For you specially:  
Captcha Text Enter the text in the image.(Not Case sensitive)    



Spam Bot Trap



   



Select Theme
White
Gray
Blue
Brown
GraySimple