Out-DataTable

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

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

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!

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.

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.

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.

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!