Michael E. Chancey Jr.

Exporting Datasets and Datatables to CSV Extension

// August 24th, 2010 // Useful Code

This is a simple VB.NET Extension for converting Datasets and Datatables into CSV formatted files. Simply call .ToCSV() and you will be returned a CSV formatted string or an array of CSV formatted strings. There is also an option to display null values as NULL or simply leave them as a blank space.

//Code

---

Imports System.Runtime.CompilerServices
Imports System.Text
Imports System.Data

''' <summary>
''' EXTENDS DATASETS AND DATATABLES TO GIVE THEM THE ABILITY TO EXPORT TO CSV FORMATTED FILES
''' </summary>
Module DataExtensions

    ''' <summary>
    ''' EXPORTS A DATATABLE INTO A CSV FORMATTED FILE
    ''' </summary>
    ''' <param name="tmpTable">INPUT DATATABLE TO CONVERT TO CSV</param>
    ''' <param name="showNull">DISPLAY NULL IN EVENT OF NULL OR BLANK LINE</param>
    ''' <returns>RETURNS A CSV FORMATTED STRING WHICH CAN BE WRITTEN TO A FILE</returns>
    <Extension()> _
    Public Function ToCSV(ByVal tmpTable As DataTable, ByVal showNull As Boolean) As String
        Dim tmpResult As New StringBuilder()

        'INSERT THE COLUMN HEADERS INTO THE RETURN
        For Each tmpCcol As DataColumn In tmpTable.Columns
            tmpResult.Append(String.Format("""{0}"",", tmpCcol.ColumnName))
        Next
        tmpResult.AppendLine()

        'INSERT EACH ROWS LINE INTO THE RETURN
        For Each tmpRow As DataRow In tmpTable.Rows
            Dim tmpLine As New StringBuilder()
            For Each tmpCcol As DataColumn In tmpTable.Columns
                If (Not IsDBNull(tmpRow(tmpCcol.ColumnName))) Then
                    tmpLine.Append(String.Format("""{0}"",", tmpRow(tmpCcol.ColumnName)))
                Else
                    'IF THE USER WANTS TO SEE NULL THEN DISPLAY IT, OTHERWISE JUST A BLANK SPOT
                    If showNull Then
                        tmpLine.Append("""NULL"",")
                    Else
                        tmpLine.Append(",")
                    End If
                End If
            Next

            'APPEND THE ROW TO THE OVERALL RESULT TABLE
            tmpResult.AppendLine(tmpLine.ToString().Substring(0, tmpLine.Length - 1))
        Next

        'RETURN THE TABLE STRING
        Return tmpResult.ToString()
    End Function

    ''' <summary>
    ''' EXPORTS A DATASET INTO AN ARRAY OF CSV FORMATTED FILES
    ''' </summary>
    ''' <param name="tmpData">INPUT DATASET TO BE CONVERTED INTO CSV FILES</param>
    ''' <param name="showNull">DISPLAY NULL IN EVENT OF NULL OR BLANK LINE</param>
    ''' <returns>RETURNS A CSV FORMATTED STRING ARRAY WHICH CAN BE WRITTEN TO A SERIES OF FILES</returns>
    <Extension()> _
    Public Function ToCSV(ByVal tmpData As DataSet, ByVal showNull As Boolean) As String()
        Dim result(tmpData.Tables.Count - 1) As String

        For I As Integer = 0 To tmpData.Tables.Count - 1
            'STORE THE RETURN OF EACH TABLE INTO THE ARRAY
            result(I) = tmpData.Tables(I).ToCSV(showNull)
        Next

        'RETURN THE FULL RESULT
        Return result
    End Function

End Module