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

3 Comments


  1. Thanks for the post – really helpful in getting started with data tables.

    Reply

  2. Really helpful, thanks. This post helped me realize that the comma before the variable return in createDT() is necessary to preserve the table datatype (powershell implicitly passes it back as an object array without). Wasn’t aware of that before.

    Reply

  3. Thank you, this helped me a lot and its a very clear easy to understand format

    Reply

Leave a Reply

Your email address will not be published.