Out-DataTable

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

 
 
 
 
 
(10)
Add to favorites
Databases
12/28/2012
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • 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
       }
       else
       {
        $DR.Item($property.Name) = $property.value 
       }
  • Please use DBNull instead
    4 Posts | Last post October 22, 2013
    • 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.
      
      Thanks
      
    • @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!
  • 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.
      -Bruce
    • @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!
      
      David
    • 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.
      
  • Problem using in conjunction with get-content on a text file
    3 Posts | Last post June 08, 2011
    • I'm using get-content on a text file, and then using the select object on those results.  Finally I'm piping that to out-datatable, but get nothing but the length column.  Since I guess what is getting passed is a string array the actual string value is not grabbed from $object.PsObject.get_properties() in the out-datatable definition.  Is this by design? Does out-datatable not work with string arrays?
    • That's correct out-datatable works against objects. A string is a primitive datatype. There are other options for working with text files. If the text file is delimited you could use import-csv with the delimiter parameter. If the text file is fixed width columns you could use this http://sev17.com/2011/05/use-ace-drivers-and-powershell-to-talk-to-text-files/. Finally you could turn the text file into a object with this 
      cat .\mytextfile.txt  | select @{n='line';e={$_}} | Out-DataTable
    • Thanks for the clarification, I'll try a few of those options.  Just stepping into powershell, so there is still a mystique for how objects are passed and piped around.  You have some great functions though for a data guy like me, thanks for your contributions.
  • Please explain why column.datatype is not set
    2 Posts | Last post April 30, 2011
    • Columns are all set to System.String datatype. I would set the datatype as seen by the property being converted.
      
      Example:
      ....
                      if ($first) 
                      {   
                          $Col =  new-object Data.DataColumn   
                          $Col.ColumnName = $property.Name.ToString()
                          $Col.DataType = $property.value.gettype()  
                          $DT.Columns.Add($Col) 
                      }   
    • That's a great idea. I've updated the script accordingly.
      
      Thanks!