Working with Basic .NET DataSets and DataTables in PowerShell

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

Create Basic Datatable

dt

Create Basic DataSet (Collection of Tables)

dt

Selecting

Create Datatable from CSV

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

Managing Duplicates

Create new table using another datatable’s schema

Filling DataTables and DataSets with data from SQL Server

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
18 comments on “Working with Basic .NET DataSets and DataTables in PowerShell
  1. Colin says:

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

    Thanks Chrissy.

  2. SB says:

    Colin; seconded.

  3. robduncan says:

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

  4. Karl says:

    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.

  5. Keng Onn says:

    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.

  6. Brain Zender says:

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

  7. J Spivey says:

    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.

  8. Michael Osmond says:

    Thank you. Very useful.

  9. Photonn says:

    Really useful. Thanks a lot!

  10. Steve Schaub says:

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

  11. Daniel Lawrence says:

    This is amazing. Thank you!

  12. sateesh says:

    Thank you so much

  13. Carl says:

    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′”);

  14. Carl says:

    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?

    • Carl says:

      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
      })

Leave a Reply

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

*