Working with Basic .NET DataSets and DataTables in PowerShell

Posted on February 1, 2015 by

This is mostly for my reference, but you can use it, too :)

Create Basic Datatable

$dt = New-Object System.Data.Datatable
[void]$dt.Columns.Add("First")
[void]$dt.Columns.Add("Second")
[void]$dt.Columns.Add("Third")

# Add a row manually
[void]$dt.Rows.Add("1","2","3")

# Or add an array
$me = "computername","userdomain","username"
$array = (Get-Childitem env: | Where-Object { $me -contains $_.Name }).Value
[void]$dt.Rows.Add($array)

Create Basic DataSet (Collection of Tables)

# Continuing from above
$dt.TableName = "Me"
$ds = New-Object System.Data.DataSet
$ds.Tables.Add($dt)

$dt2 = New-Object System.Data.Datatable "AnotherTable"
[void]$dt2.Columns.Add("MyColumn")
[void]$dt2.Rows.Add("MyRow")
$ds.Tables.Add($dt2)

$ds.tables["Me"]
$ds.tables["AnotherTable"]

Selecting

$dt = New-Object System.Data.Datatable
[void]$dt.Columns.Add("Artist")
[void]$dt.Columns.Add("Genre")
[void]$dt.Columns.Add("Album")
[void]$dt.Columns.Add("ReleaseYear")

# Change ReleaseYear to an int, if you want.
$dt.Columns["ReleaseYear"].DataType = [int]

# Add an array
$array = "Tim Smooth","Southern Rap","I Gotsta' Have It",1991
[void]$dt.Rows.Add($array)

# Or add rows manually
[void]$dt.Rows.Add("Poison","Glam Metal","Open Up and Say... Ahh!",1988)
[void]$dt.Rows.Add("Cinderella","Night Songs","Flesh & Blood",1986)
[void]$dt.Rows.Add("Eazy-E","Gangsta Rap","Eazy-Duz-It",1988)

# Uses SQL syntax
$dt.Select("Genre like '%Rap'")
$dt.Select("ReleaseYear > 1988")
$dt.Select("Artist = 'Poison'")

# Change Eazy-E's genre
$eazy = ($dt.Select("Artist = 'Eazy-E'"))
$eazy[0]["Genre"] = "West Coast Rap"

# Find this new genre
$dt.Select("Genre like '%Coast%'")

Create Datatable from CSV

Using this method, you can add 140k rows a second.

# CSV variables
$csvfile = "C:\perf\100krow.tsv"
$csvdelimiter = "`t"
$firstRowColumns = $false

# Do it
$dt = New-Object System.Data.Datatable
$reader = New-Object System.IO.StreamReader $csvfile
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)

foreach ($column in $columns) {
	if ($firstRowColumns -eq $true) { 
		[void]$dt.Columns.Add($column)
		$reader.ReadLine()
	} else { [void]$dt.Columns.Add() }
}

# Read in the data, line by line
while (($line = $reader.ReadLine()) -ne $null)  {
	[void]$dt.Rows.Add($line.Split($csvdelimiter))
} 

$dt.rows.count

Managing Duplicates

$columns = "Second","Third"

$distinct = New-Object "System.Data.DataTable" 
$onlydupes = New-Object "System.Data.DataTable" 
$hash = new-object "System.Collections.Generic.HashSet[string]"

foreach ($column in $dt.Columns) {
	[void]$distinct.Columns.Add($column.ToString())
	[void]$onlydupes.Columns.Add($column.ToString())
}

foreach ($row in $dt.rows) {
	$columnsToCheck = [string]($row | Select-Object $columns)
	$result = $hash.Add($columnsToCheck)

	if ($result -eq $false) { 
		[void]$distinct.Rows.Add($row.ItemArray) 
	} else {
		# This works about as fast as $all.DefaultView.ToTable($true,"Column2","Column3")
		[void]$onlydupes.Rows.Add($row.ItemArray) }
}

$dt.rows.count
$distinct.rows.count
$onlydupes.rows.count

Create new table using another datatable’s schema

$dupetable = $dt.Clone()
foreach ($row in $select) { $dupetable.ImportRow($row) }
#or
$dupetable = $dt.Copy()

Filling DataTables and DataSets with data from SQL Server

# Set variables
$sqlserver = "sqlserver"
$database = "master"
$connstring = "Server=$sqlserver;Database=$database;Integrated Security=True"
$query = "select SERVERPROPERTY('InstanceDefaultDataPath') as data, SERVERPROPERTY('InstanceDefaultLogPath') as log"
 
 
# You can use SQL Connection/ SQL Command
$sqlconn = New-Object System.Data.SqlClient.SqlConnection
$sqlconn.ConnectionString = $connstring
$sqlconn.Open()

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlconn
$sqlcmd.CommandText = $query

# load it right into a DataTable
$dt = New-Object System.Data.DataTable
$dt.Load($sqlcmd.ExecuteReader())
$dt.data
$dt.log

# Or alternatively, load a table into a DataSet
$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqladapter.SelectCommand = $sqlcmd
$ds = New-Object System.Data.DataSet
[void]$sqladapter.Fill($ds)

# Close connection
$sqlconn.Close()
 
$defaultdata = $ds.Tables[0].rows[0].data
$defaultlog = $ds.Tables[0].rows[0].log
 
# Or you can use SMO
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
$ds = $server.ConnectionContext.ExecuteWithResults($query)
$ds.Tables[0]
 
$defaultdata = $ds.Tables[0].rows[0].data
$defaultlog = $ds.Tables[0].rows[0].log

About Chrissy LeMaire

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server

19 comments on “Working with Basic .NET DataSets and DataTables in PowerShell”

Colin says:

February 5, 2016 at 1:30 pm

Of all the googled PS DataSet explanations, this one is far and away the best.

Thanks Chrissy.

Reply
SB says:

February 10, 2017 at 9:46 am

Colin; seconded.

Reply
- Chrissy LeMaire says:
  
  March 2, 2017 at 10:12 am
  
  thank, guys! i find myself using it often too lol
  
  Reply
- Matthew Millers says:
  
  June 9, 2017 at 1:51 am
  
  Thirded!
  
  Great post.
  
  Reply
robduncan says:

March 29, 2017 at 10:18 am

Great article – would love to see examples of loading xml files into datatables

Reply
Karl says:

June 7, 2017 at 8:17 pm

Would like to see a follow-up to:
Filling DataTables and DataSets with data from SQL Server then update
the database from which the data was fetched from.

Reply
Keng Onn says:

June 8, 2017 at 6:25 am

Hi, Thanks for sharing your expertise, Chrissy, really appreciate it! :) Just one question though, how do you cater for nulls in the source data? I was using the code from your post at https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9 for bulk insert, but got stuck when I tried to insert data like this “abc,d,,f,,e” where I expect to see the second last column of that table to be null instead of an empty string. I tried using [DBNull]::Value but that didn’t work :(. Would appreciate hearing whatever insight you have on this. Thanks! Cheers, Keng Onn.

Reply
Brain Zender says:

July 11, 2017 at 10:03 pm

Yep. I agree with the comments above — this is a clean, clear explanation. Laissez les bons temps rouler, cher!

Reply
J Spivey says:

August 22, 2017 at 4:25 pm

Thank you for this excellent write up and example. I wish MicroSoft would provide such clear examples. I visited many other pages before finding this one, it was very helpful for a problem I was trying to solve.

Reply
Michael Osmond says:

September 3, 2017 at 10:22 pm

Thank you. Very useful.

Reply
Photonn says:

May 7, 2018 at 4:29 pm

Really useful. Thanks a lot!

Reply
Steve Schaub says:

July 23, 2018 at 2:07 pm

Very useful – any chance you could give an example of selecting data from different sources into datatables and then joining those tables? Thanks.

Reply
Daniel Lawrence says:

January 15, 2019 at 4:14 pm

This is amazing. Thank you!

Reply
sateesh says:

April 18, 2019 at 5:02 am

Thank you so much

Reply
Carl says:

June 3, 2019 at 5:06 pm

Hello,

I’m trying to use the “Create DataTabele from CSV” and I can’t select my columns. My csv is
BEGDOC|ENDDOC|BEGATT|ENDATT|GroupID
ABC123|ABC123|ABC123|ABC124|

When I do
$dt.Columns.Count
$dt.Rows.Count

I get the right counts, 5 columns and 2 rows. But when I do $dt.Select(“BEGDOC = ‘ABC123′”) Powershell can’t says it can’t find the column name, BEGDOC.

However, when I do
$dt.Select(“Column1 = ‘ABC123′”) then I get:
Column1 : ABC123
Column2 : ABC123
Column3 : ABC123
Column4 : ABC124
Column5 :

Please help, my code is:
$datFile = “C:\Users\ccastillo\Desktop\TEST.DAT”;
$datDelimiter = “|”;
$firstRowColumns = $false;

$dt = New-Object System.Data.DataTable;
$reader = New-Object System.IO.StreamReader $datFile;
$columns = (Get-Content $datFile -First 1).Split($datDelimiter);

foreach ($column in $columns)
{
if ($firstRowColumns -eq $true)
{
[void]$dt.Columns.Add($column)
$reader.ReadLine();
}
else
{
[void]$dt.Columns.Add();
}
}

while (($line = $reader.ReadLine()) -ne $null)
{
[void]$dt.Rows.Add($line.Split($datDelimiter))
}
$dt.Columns.Count
$dt.Rows.Count
$dt.Select(“BEGDOC = ‘ABC123′”);
$dt.Select(“Column1 = ‘ABC123′”);

Reply
- Carl says:
  
  June 3, 2019 at 5:20 pm
  
  I got it to work nevermind! sorry
  
  Reply
Carl says:

June 11, 2019 at 1:44 am

Hello again, using “Create DataTabele from CSV” script, how do you get it read special characters? Change it the Encoding to UTF8? I’ve tried this:

$reader = New-Object System.IO.StreamReader::New($datFile, [System.Text.UTF8Encoding]::UTF8)

However, when it fails in this code:

while (($line = $rows.ReadLine()) -ne $null)
{
[void]$dt.Rows.Add($line.Split($datDelimiter))
}

because I can’t call a method on a null-valued expression. Is there a workaround for this?

Reply
- Carl says:
  
  June 11, 2019 at 6:16 am
  
  UPDATE: I got the code to run, but it’s still not reading the text with right encoding.
  
  Code:
  $btnGetDat.add_Click({
  if($dialog.ShowDialog() -eq ‘Ok’) {
  $tbxDatFile.Text = $dialog.FileName
  $datFile = $dialog.FileName
  $datDelimiter = “|”
  $encoding = [System.Text.Encoding]::UTF8
  $reader = New-Object System.IO.StreamReader($datFile, $encoding)
  $firstRowColumns = $false
  $columns = (Get-Content $datFile -Encoding UTF8 -First 1).Split($datDelimiter)
  
  #READ DAT FILE
  #COLUMNS
  foreach ($column in $columns)
  {
  if ($firstRowColumns -eq $true)
  {
  [void]$dt.Columns.Add($column)
  $reader.ReadLine();
  }
  else
  {
  [void]$dt.Columns.Add($column);
  }
  }
  #ROWS
  while (($line = $reader.ReadLine()) -ne $null)
  {
  [void]$dt.Rows.Add($line.Split($datDelimiter))
  }
  
  #DataTable Maniuplation…
  })
  
  #EXPORT NEW DAT
  $btnGenerateDat.add_Click({
  $exportDat = [System.IO.Path]::GetDirectoryName($dialog.FileName) + “\” + [System.IO.Path]::GetFileNameWithoutExtension($dialog.FileName) + “_NEW” + [System.IO.Path]::GetExtension($dialog.FileName)
  $dt | Select-Object -Skip 1 | export-csv -Encoding UTF8 $exportDat -Delimiter “|”-NoTypeInformation | Format-Table -HideTableHeaders
  })
  
  Reply
Guy Stalnaker says:

December 19, 2019 at 9:34 pm

If anyone finds this — the example for pulling data from an external DB uses SQLServer. You can also do this for Oracle DB. Instead of System.Data.SqlClient.SqlConnection use System.Data.OleDb.OleDbConnection. You’ll need a properly formatted datasource and connection string, something like this:

{code}
$Datasource=”(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$myHost)(PORT=$myPort)))(CONNECT_DATA=(SID=$sid)(SERVICE_NAME=$sName)(SERVER=dedicated)))”

$connectionString = “Provider=OraOLEDB.Oracle; ” +
“Data Source=$dataSource; ” +
“User Id=$hostUsername;Password=$hostPW; ”

…

$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$connection.Open()
$command = New-Object Data.OleDb.OleDbCommand $sqlQuery,$connection
$command.CommandTimeout = $timeout
$sqlAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
{code}

This works for me. The OraOLDDB.Oracle provider has to be registered with Windows, but should (!?) be with a normal Oracle install.

Reply