Text File to DataTable

Features

This code snippet is a function that returns a DataTable by converting a delimited text file. The function also allows for the user to specify if the file’s first row is the column header text.

References

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

Code

Private Function FileToDataTable(ByVal path As String, ByVal delimiter As Char, ByVal headers As Boolean) As DataTable
    Dim dt As New DataTable
    Dim lines() As String = IO.File.ReadAllLines(path)

    Dim x As Integer = 0
    If headers Then
        dt.Columns.AddRange((From column As String In lines(x).Split(delimiter) Select New DataColumn(column)).ToArray)
        x += 1
    Else
        dt.Columns.AddRange((From column As String In lines(x).Split(delimiter) Select New DataColumn()).ToArray)
    End If

    For index As Integer = x To lines.Length - 1
        dt.Rows.Add(lines(index).Split(delimiter))
    Next

    Return dt
End Function

Fiddle: https://dotnetfiddle.net/K5hRbg

Exceptions

If the first row in the text file has less columns than any of the following rows then a System.ArgumentException will be thrown.

 

If the incorrect delimiter is used then a System.IndexOutOfRangeException will be thrown.

If the file does not exist then a System.IO.FileNotFoundException will be thrown

Calculate Mean(Average)

Features

This code snippet is a function that returns a Double based on the sum of all the items in a collection divided by the amount of items in a collection.

References

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

Code

Friend Function CalculateAverage(ByVal collection() As Object) As Double
    Return collection.Sum(Function(item) Convert.ToDouble(item)) / collection.Length
End Function

Fiddle: https://dotnetfiddle.net/oBejPC

Exceptions

If the collection cannot successfully be converted to a Double array then a System.FormatException

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:

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:

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

Get Files by Accessed Date

Features

This code snippet is two functions that returns an array of IO.FileInfo from a directory based on the day that the file was last accessed. The difference between the two is that the one with the String parameter named filter allows you to specify the search pattern of the files.

References

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

Code

Friend Function GetFilesByAccessedDate(ByVal directory As String, ByVal creation As DateTime) As IO.FileInfo()
    Return (From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles() Where file.LastAccessTime.Date = creation.Date).ToArray()
End Function

Friend Function GetFilesByAccessedDate(ByVal directory As String, ByVal creation As DateTime, ByVal filter As String) As IO.FileInfo()
    Return (From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles(filter) Where file.LastAccessTime.Date = creation.Date).ToArray()
End Function

Fiddle: https://dotnetfiddle.net/dPS9nb