Convert DataTable to CSV

Features

This code snippet is two functions that converts a DataTable to a CSV file. The One difference between the two functions is that the one with the Boolean parameter named headers allows you to signify that the first row in the CSV file contains header text.

References

The following namespaces/class will need to be added in order for the code to compile:

• System
• System.Data
• System.Data.DataSetExtensions
• System.Linq
• System.Xml

Code

Friend Sub ConvertDataTableToCSV(ByVal data As DataTable, ByVal path As String)
    Dim csv As String = String.Join(Environment.NewLine, (From row As DataRow In data.Rows.Cast(Of DataRow) Select String.Join(",", (From column As DataColumn In data.Columns.Cast(Of DataColumn) Select row.Item(column.ColumnName)))))
    IO.File.WriteAllText(path, csv)
End Sub

Friend Sub ConvertDataTableToCSV(ByVal data As DataTable, ByVal path As String, ByVal headers As Boolean)
    Dim csv As String = String.Empty
    If headers Then
        csv = String.Join((",", (From column As DataColumn In data.Columns.Cast(Of DataColumn) Select column.Caption).ToArray) & Environment.NewLine()
    End If

    csv &= String.Join(Environment.NewLine, (From row As DataRow In data.Rows.Cast(Of DataRow) Select String.Join(",", (From column As DataColumn In data.Columns.Cast(Of DataColumn) Select row.Item(column.ColumnName)))))
    IO.File.WriteAllText(path, csv)
End Sub

Fiddle: https://dotnetfiddle.net/TOjUK3

Convert CSV File to DataTable

Features

This code snippet is two functions that returns a DataTable from a CSV file. One difference between the two functions is that the one with the Boolean parameter named header allows you to signify that the first row in the CSV file contains header text.

References

The following namespaces will need to be added in order for the code to compile:

• System
• System.Data
• System.Xml

Code

Friend Function ConvertCSVToDataTable(ByVal path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR=YES;FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", "C:\")
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & path, con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    Return dt
End Function

Friend Function ConvertCSVToDataTable(ByVal path As String, ByVal header As Boolean) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR={2};FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", "C:\", If(header, "YES", "NO"))
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & path, con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    Return dt
End Function

Fiddle: https://dotnetfiddle.net/TOjUK3