PowerShell – Using Datatables

I’ve authored several PowerShell scripts along my technical journey. One of the most common request I receive is a request to write PowerShell to go fetch information based on a specific set of criteria and exporting that data to CSV or some other file format. Datatables are a great use case for this. Datatables is an object type that you can create columns/rows and update each accordingly. I found that I often reuse existing PowerShell code working with Datatables and feel this would be valuable to share.

Sample Code – Creating a Datatable

##The following code sample creates a data table and adds custom columns to it.##

######################################
##Creating and Returning a DataTable##
######################################
function createDT()
{
    ###Creating a new DataTable###
    $tempTable = New-Object System.Data.DataTable
   
    ##Creating Columns for DataTable##
    $col1 = New-Object System.Data.DataColumn(“ColumnName1”)
    $col2 = New-Object System.Data.DataColumn(“ColumnName2”)
    $col3 = New-Object System.Data.DataColumn(“ColumnName3”)
           
    ###Adding Columns for DataTable###
    $tempTable.columns.Add($col1)
    $tempTable.columns.Add($col2)
    $tempTable.columns.Add($col3)
       
    return ,$tempTable
}

Note 1: I wrapped this into a function. In addition, you would want to update the ColumnName# parameter to something that identifies the column.

Note 2: Calling the above function would look something like the following:

###Getting a new DataTable###
[System.Data.DataTable]$dTable = createDT

Sample Code – Adding Information to a Datatable

The next step is populating the Datatable with information collected. The following code sample adds information to the datatable created with the sample code above.

#Add a row to DataTable

$row = $dTable.NewRow()
$row[“ColumnName1”] = “RandomStringData1”
$row[“ColumnName2”] = “RandomStringData2”
$row[“ColumnName3”] = "RandomStringData3"
$dTable.rows.Add($row)

Note: You can simply iterate via a foreach loop to add more than one row if you’re working with large data sets.

Finally, you can export this data table to csv (Export-CSV) command-let or some other file format.

I hope you find this helpful.

Thank You!

Russ Maxwell

Leave a Reply

Your email address will not be published. Required fields are marked *