Working with data tables in PowerShell

I’m creating this post for my own reference for the most part, however hopefully it will assist someone else as well.

The following can be ran in PowerShell to create a basic data table:

$Table = New-Object System.Data.DataTable

For each column you want in the table, you’ll want to run something like the following:

$column = New-Object System.Data.DataColumn ‘NameofColumn’,([COLUMNTYPE])
$Table.Columns.Add($column)

Or to simplify things:

$Table.Columns.Add((New-Object System.Data.DataColumn ‘NameofColumn’, ([COLUMNTYPE])))

In the above, ‘NameofColumn’ is whatever you want the column title to be. It can contain spaces and symbols. COLUMNTYPE should be replaced with the type of column you want it to be. I most commonly use ‘string’ and ‘datetime’. You can reference http://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype%28v=vs.110%29.aspx for additional column types.

After you’ve created your table with the columns you want, you’ll need to start populating the table. The code below will show you how to add a row to the table, and assumes there are 3 columns, Name, Department, and Email.

$row = $Table.NewRow()
$row.’Name’ = “John”
$row.’Department’ = “IT”
$row.’Email’ = “John@intrntpirate.com”
$Table.Rows.Add($row)

If you find yourself needing to edit  a row, you have two ways IMO that you can do this. The first way is to explicitly call a particular row. You would do this by calling:

$Table.Rows[0]

In the above, the [0] references the first row. Using [1] would reference the second row, and so on. If you were to run:

$Table.Rows[0].Name

You would get the value for the Name column of row 1 ([0]) returned. You can do the following to change the value.

$Table.Rows[0].Name = “NewName”

To delete the row you would run:

$Table.Rows[0].Delete()

The second way you can change a value is a bit more useful. You can essentially search the table for the row you want, and then change the value of a column.

To do this you use Where-Object. The following example will change the department column for the row where the name is equal to “Joe”.

($Table.Rows | Where-Object {($_.Name -eq “Joe”)}.Department = “NewDepartment”

Finally, if you find yourself needing to delete a row you can do so using the same two methods we used for modifying a row/column. You can delete a row by running:

$Table.Rows[0].Delete()

and also by doing:

($Table.Rows | Where-Object {($_.Name -eq “Joe”)}).Delete()

One thought on “Working with data tables in PowerShell”

  1. Just wanted to say that this is awesome and I am using something very similar to create a table within PowerShell and then bulk insert that table into a SQL Server table. Thank you for sharing.

Leave a Reply

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