DataTable to CSV extraction

In this post we examine a set of DataTable Extensions using Linq to provide a CSV (comma separated value) extraction of a DataTable.

Imports System.Runtime.CompilerServices

Public Module StringExtensions

    ''' <summary>
    ''' Quotes a string using the following rules:
    ''' <list>
    ''' <listheader>Rules</listheader>
    ''' <item>if the string is not quoted and the string contains the separator string</item>
    ''' <item>if the string is not quoted and the string begins or ends with a space</item>
    ''' <item>if the string is not quoted and the string contains CrLf</item>
    ''' </list>
    ''' </summary>
    ''' <param name="s">String to be quoted</param>
    ''' <param name="quote">
    ''' <list>
    ''' <listheader>quote characters</listheader>
    ''' <item>if len = 0 then double quotes assumed</item>
    ''' <item>if len = 1 then quote string is doubled for left and right quote characters</item>
    ''' <item>else first character is left quote, second character is right quote</item>
    ''' </list>
    ''' </param>
    ''' <param name="sep">separator string to check against</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <Extension()> _
    Public Function QuoteName(ByVal s As String, Optional ByVal quote As String = Nothing, _
                              Optional ByVal sep As String = ","c) As String
        Select Case Len(quote)
            Case 0 : quote = """"""
            Case 1 : quote += quote
        End Select
        ' Fields with embedded sep are quoted
        If (Not s.StartsWith(quote.Substring(0, 1))) _
            AndAlso (Not s.EndsWith(quote.Substring(1, 1))) Then _
            If s.Contains(sep) Then s = quote.Substring(0, 1) + s + quote.Substring(1, 1)
        ' Fields with leading or trailing blanks are quoted
        If (Not s.StartsWith(quote.Substring(0, 1))) _
            AndAlso (Not s.EndsWith(quote.Substring(1, 1))) Then _
            If s.StartsWith(" ") OrElse s.EndsWith(" ") Then s = quote.Substring(0, 1) + s + quote.Substring(1, 1)
        ' Fields with embedded CrLF are quoted
        If (Not s.StartsWith(quote.Substring(0, 1))) _
            AndAlso (Not s.EndsWith(quote.Substring(1, 1))) Then _
            If s.Contains(vbCrLf) Then s = quote.Substring(0, 1) + s + quote.Substring(1, 1)
        Return s
    End Function

End Module

Imports System.Runtime.CompilerServices

Public Module DataSetExtensions

    ''' <summary>
    ''' Returns the CSV of a DataTable as a string
    ''' </summary>
    ''' <param name="table"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <Extension()> _
    Public Function GetCSV(ByVal table As System.Data.DataTable) As String
        Dim s As String = String.Empty
        Dim iCols As Integer = table.Columns.Count
        Try
            s += String.Join("", (From ii As Integer In Enumerable.Range(0, iCols) _
                                  Select table.Columns(ii).ColumnName.QuoteName("[]") + _
                                  If(ii + 1 < iCols, ",", vbCrLf)).ToArray) + _
                  String.Join("", (From dr As DataRow In table.Rows _
                                   From ii As Integer In Enumerable.Range(0, iCols) _
                                   Select dr(ii).ToString().QuoteName() + _
                                   If(ii + 1 &lt; iCols, ",", vbCrLf)).ToArray)
            s = s.TrimEnd(New Char() {vbCr, vbLf})
        Catch ex As Exception
            WriteErrorLog(ex, msErrorLog)
        End Try
        Return s
    End Function

    ''' <summary>
    ''' Write the CSV of a DataTable to a file
    ''' </summary>
    ''' <param name="table"></param>
    ''' <param name="fileName"></param>
    ''' <remarks></remarks>
    <Extension()> _
    Public Sub WriteCSV(ByVal table As System.Data.DataTable, ByVal fileName As String)
        IO.File.WriteAllText(fileName, table.GetCSV)
    End Sub

    ''' <summary>
    ''' Write the CSV of a DataTable to a Stream
    ''' </summary>
    ''' <param name="table"></param>
    ''' <param name="stream"></param>
    ''' <remarks></remarks>
    <Extension()> _
    Public Sub WriteCSV(ByVal table As System.Data.DataTable, ByVal stream As System.IO.StreamWriter)
        stream.Write(table.GetCSV)
        stream.Flush()
    End Sub

End Module