Creates a DataTable for an object, based on script by Marc van Orsouw

4.6 Star
Add to favorites
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question

  • Seems like a bug: if ($property.value)
    2 Posts | Last post January 03, 2018
    • Why is "if ($property.value)" test there?
      I have INT32 Columns in my original table with my first record having value "0".
      that clause translates ($property.value) to FALSE....so the "$Col.DataType = ..." assignment to INT32 never happens and the column gets the default is String.
      The BUG is masked 2 ways:
      * In Bulk Insert for example, implicit conversion from String to INT succeeds.
      * If your first record doesn't have value "0", the COL is typed correctly...so you don't see the issue...but if by happenstance, your first record has 0 for a numeric column, the bug happens.
      One of my SQL Table Columns is BIT (ie. Booleon), not INT and it blew up the Bulk Insert of a string "0". That's how I saw the issue.
      (NOTE: Sure I could change my SQL table column from BIT to INT...but that avoids the real issue in this script...numeric columns that have value 0 in the first row won't get typed correctly. relying on implied conversions is not what I want.)
      Especially concerning is if the column is typed String, potentially bad logic based on string numerics may execute undetected if its "masked".
      So I'm not sure what problem the original test was avoiding...but I am using this hacky statement instead.
      if (($property.value) -or ($property.value -eq 0))
      hope that makes sense.
    • I think the problem is with the approach of this cmdlet (and other datatable cmdlets on the internet). It wants to define whole column type based on only the first object (row). This has triggered me to write a similar cmdlet that does some smarter column type defining. See: https://powersnippets.com/convertto-datatable/ 
  • Out-DataTable in WPF UI for PowerShell
    1 Posts | Last post July 18, 2017
    • I have a DataTable ($dt) coming from Out-DataTable and it's bound to a WPF DataGrid Control. 
      Using DataGrid.itemssource = $dt.DefaultView line I was able to bind the DataGrid to $dt
      however, I wasn't able to edit the data nor add new records to WPF DataGrid 
      I tried the same thing, this time I created the datatable manually in powershell script. 
      The manual datatable was bound successfully to WPF datagrid and I was able to edit the data in the grid. 
      Why can't I edit the data in the datatable that's coming from out-datatable?
      Thank you 
  • Out-DataTable Module
    1 Posts | Last post March 07, 2016
    • I saved the file as a .psm1 (powershell module) and saved it to the directory: 
      I then created the manifest file by running the following but this is not required.
      New-ModuleManifest Out-DataTable.psd1 -ModuleVersion "1.7" -Author "Chad Miller" 
      This makes like some much easier since I have several processes that reference this.
  • Please explain why column.datatype is not set
    3 Posts | Last post January 03, 2016
    • Columns are all set to System.String datatype. I would set the datatype as seen by the property being converted.
                      if ($first) 
                          $Col =  new-object Data.DataColumn   
                          $Col.ColumnName = $property.Name.ToString()
                          $Col.DataType = $property.value.gettype()  
    • That's a great idea. I've updated the script accordingly.
    • So I realize I'm a little late to the party, but all Columns are still set to system.string datatype unless its the first column. 
  • Please use DBNull instead
    6 Posts | Last post November 04, 2015
    • Hi Chad,
      I'm encountering this error when grabbing database information from SMO:
      Exception calling "set_Item" with "2" argument(s): "Cannot set Column 'SpaceAvailable' to be null. Please use DBNull instead."
      At C:\Users\glyons\Documents\WindowsPowerShell\Modules\Out-DataTable\Out-DataTable.psm1:92 char:29
      +                     $DR.Item <<<< ($property.Name) = $property.value 
          + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
          + FullyQualifiedErrorId : DotNetMethodException
      It's happening with fields that are sometimes null, and I haven't been able to successfully force the datatype by using ConvertTo...
      Stupid thing is, despite this, the data actually writes fine, just all the red ink is annoying! Using v1.6 of the script.
    • @Garareth -- I've had the same problem with data with mixed null and not null. I haven't figured how to account for null when creating the datatable programmatically. One suggestion, if possible you can convert null to not null. Here's a script I wrote to compare drives where if the size is null it return 0 for the property: http://poshcode.org/3768.
      If you figure out a way more gracefully handle null values, please post your solution.
    • Thanks, I'll take a look.
    • So, the work around is to sort it out in the select:
      select @{n="Size";e={[System.Double]$_.Size}} (null Size = 0)
      select @{n="Size";e={[System.String]$_.Size}} (null Size = blank string)
      select @{n="Size";e={if ($_.Size) {$_.Size} else {0}}} (from Chad's drives script above; null Size = 0)
      select @{n="SpaceAvailable";e={$_.SpaceAvailable/1KB}} also seems to work.
      Hope that's helpful to someone!
    • I think the correct solution should be to replace the line
      $DR.Item($property.Name) = $property.value 
      with the lines
      if ($property.value) {
          $DR.Item($property.Name) = $property.value 
      else {
          $DR.Item($property.Name) = [DBNull]::Value
    • @Florian's solution worked for me.
  • NULL values
    1 Posts | Last post June 05, 2014
    • I had an issue with using the datatable produced by this fucntion in the SqlBulkCopy because empty values from csv are by default represented as empty string, whereas for int columns this causes an exception: "The given value of type String from the data source cannot be converted to type int of the specified target column."
      I had to add following change to the funtion:
       if($property.value -eq "")
         $DR.Item($property.Name) = $null
        $DR.Item($property.Name) = $property.value 
  • Incorrect Link
    1 Posts | Last post April 06, 2013
    • Great script! I'm using it to populate datagridviews with custom objects without any issues at all. One small thing, your referenced link in the code comments points to a page which doesn't work ;)
  • SQL Server permissions
    4 Posts | Last post January 15, 2013
    • Hi Chad, love this function.  Any idea what the minimum SQL Server permissions are needed to use this?  I tried various things but no luck so far other than db_owner on the database that contains the table.
    • @BruceKasnof -- The out-datatable function doesn't have anything to do with SQL Server, so SQL Server permissions are irrevelant. The out-datatable function simply takes any object and output an equivalent ADO.NET datatable where the properties of the object become data columns.
    • Sorry Chad, I was asking about Write-DataTable, posted in the wrong place.  I'll experiment with various permissions like ddladmin
        $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString 
    • ADO.NET SqlBulkCopy generally only requires select and insert permissions. See http://stackoverflow.com/questions/655293/what-permission-do-i-need-to-use-sqlbulkcopy-in-sql-server-2008
  • Strict Mode compatible?
    3 Posts | Last post December 28, 2012
    • Importing out-datatable into scripts with set-strictmode -version latest under PoSH V3 causes scripts to error out. Any chance of an update to bring in strictmode compatibility?
      Thanks for the very useful function!
    • I think I found the issue. I corrected the line calling IsArray incorrectly. Seems to work with strict mode set to latest, now.
    • Working great. Thanks, Chad!
  • Problem with your Get-Alias example
    3 Posts | Last post May 28, 2012
    • I must have missed something... Your own example of:
         $dt = Get-Alias | Out-DataTable
      generates errors for each alias when I run it:
      Exception calling "set_Item" with "2" argument(s): "Type of value has a mismatch with column typeCouldn't store <Invok
      e-Ternary> in ReferencedCommand Column.  Expected type is CmdletInfo."
      At C:\Users\ww\Documents\WindowsPowerShell\Modules\CleanCode\SqlSupport\Out-DataTable.ps1:61 char:32
      +                 else { $DR.Item <<<< ($property.Name) = $property.value }   
          + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
          + FullyQualifiedErrorId : DotNetMethodException
      Exception calling "set_Item" with "2" argument(s): "Type of value has a mismatch with column typeCouldn't store <Invok
      e-Ternary> in ResolvedCommand Column.  Expected type is CmdletInfo."
      At C:\Users\ww\Documents\WindowsPowerShell\Modules\CleanCode\SqlSupport\Out-DataTable.ps1:61 char:32
      +                 else { $DR.Item <<<< ($property.Name) = $property.value }   
          + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
          + FullyQualifiedErrorId : DotNetMethodException
      Any thoughts on why this occurs and how to fix it?
    • You're right it is bad example, but also it's an example of the limitations of out-datatable. The function is inferring the data type of a collection by looking at the first item in th collection. The problem with get-alias is there are different types being returned for the referencedcommand. Here's a command to illustrate
      get-alias | select name, @{n='type';e={$_.referencedcommand.gettype().Name}}
      Notice how clear returns functioninfo while much of the rest returns cmdletinfo. I updated the example to use get-psdrive. If you want to "fix" the example and use get-alias you'll need to convert the referencedcolumn to a string as follows:
      get-alias | select name, @{n='referencedcommand';e={$_.referencedcommand.tostring()}} | out-datatable
    • As luck would have it :-), on my system Get-PsDrive displayed the same problem! Armed with your useful tidbits, though, I tracked this to the fact that PowerShell Community Extensions adds a PSCX drive that is a different type than all the others. Sigh. For a quick fix, I added a check for differing types so that instead of a cryptic exception I get a more palatable message like this:
      WARNING: Skipping 13th object (type=Pscx.Providers.PscxSettingsProvider+PscxSettingsDriveInfo, expected=System.Management.Automation.PSDriveInfo)
      If I get around to it--or if you do--I am thinking a better solution would be to provide an optional type filter parameter so one could, in this example, include only the PSDriveInfo objects and not have any warnings.
1 - 10 of 11 Items