ConvertTo-DataTable

ConvertTo-DataTable.md

ConvertTo-DataTable.psm1

FUNCTION  ConvertTo-DataTable {
    param(

        [string]
        $TableName,

        [hashtable]
        $Columns,
    
        # array of rows
        # each row is a hashtables
        [array] 
        $Rows
    )

    # Make A Table
    $DATATABLE = [System.Data.Datatable]::new()
    $DATATABLE.TableName = $TableName

    # Add Columns & Types
    FOREACH ( $ColumnName in $Columns.keys ) {
        [string]$DataType = $Columns.$ColumnName
        [void]$DATATABLE.Columns.Add($ColumnName, $DataType)
    }

    # Add Rows
    FOREACH ($Row in $Rows) {
        $NewRow = $DATATABLE.NewRow()
        
        # add each value the row column
        FOREACH ($key in $Row.keys) {
            $RowValue = $Row.$key

            # attemp to add the value to the row
            try {$NewRow.$key = $RowValue}
            catch {
                write-host "could not add value to row"
                write-host $PSItem -f red
            }
        }
        
        # Add row to table
        [void]$DATATABLE.Rows.Add($NewRow)
    }

    RETURN $DATATABLE
}

EXAMPLE:

$DataTable_Parameters = @{

    TableName = 'employees'

    # define column names thier datatypes
    Columns   =  @{id = 'int'; fn = 'string' ; hd = 'datetime'}
    
    # A simple array of hashtables
    Rows      = @(
        @{ id = 123 ; fn = 'rick'  ; hd = $(get-date)  }
        @{ id = 456 ; fn = 'steve' ; hd = '2014-07-08' }
        @{ id = 789 ; fn = 'beth'  ; hd = '2018-12-09' }
    )
}

ConvertTo-DataTable @DataTable_Parameters