Please feel free to provide feedback or file bugs here.

Out-DataTable

Creates a DataTable based on an objects properties. Useful for converting any Powershell object into a DataTable which then can be bulk imported into a SQL Server table.

.EXAMPLE
$dt = Get-PSDrive | Out-DataTable
This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable

This idea comes from Chad Miller's Out-DataTable: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd#content

182 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Aaron NelsonAaron Nelson shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    After much discussion with Aaron Nelson and Chrissy LeMaire, and thanks to the enormous amount of support for this item, we’ve recognized that this is something we need to accomplish, one way or another.

    We don’t want to offer an ETA on this as the work is not well understood by our team yet, and no one currently has immediate bandwidth on starting that investigation. But I want to stress the fact that is an important ask that we’re taking seriously as a priority.

    In the meantime, it would be immensely useful if someone with expertise in the DataTable space could submit an RFC (basically a brief spec) to our PowerShell-RFC repository on GitHub. That way, we can have a discussion about what the design of a ConvertTo-DataTable cmdlet might look like before we dive in on an implementation. The process for doing so is located here: https://github.com/PowerShell/PowerShell-RFC/blob/master/RFC0000-RFC-Process.md#draft

    I should also note that, given our desire to shrink the size of the PowerShell runtime over time, we believe that the development of this cmdlet/module should start on the PowerShell Gallery. However, this does not preclude us adding it to PowerShell 6.x as its quality improves and as others validate its usefulness.

    Thanks everyone!
    -Joey

    19 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Aaron NelsonAaron Nelson commented  ·   ·  Flag as inappropriate

        I agree that the ConvertTo-DataTable name might work, I used the Out-DataTable name because that is already a popular command that the community put forward a long time ago, as well as the example.

      • TroyTroy commented  ·   ·  Flag as inappropriate

        I think the ConvertTo-DataTable name would be appropriate. Out is to send it outside of the environment like to another device (host, printer, file). Here, we are converting the PSObject to be converted into a DataTable format.

      • Warren F.Warren F. commented  ·   ·  Flag as inappropriate

        Voted. Makes sense to me. Yes, SQL Server is a common use case, but as Chrissy and others have mentioned, this is a generic construct that many folks find handy for other scenarios, e.g.:

        * In memory database-esque usage
        * Integration with various database systems and other technologies that can work with datatables

        Lastly - IMHO every new function added to PowerShell (with rare exceptions) should be included in a module, allowing folks to install / import it as needed. This case in particular, where the code could easily work down-level, should be included in a module that folks with older PowerShell versions could use. That said, with core functionality like this, even if it's a module, IMHO it would be better handled by the PowerShell team, or another Microsoft team (with the help from the OSS community, of course)

        Cheers!

      • Aaron NelsonAaron Nelson commented  ·   ·  Flag as inappropriate

        Jaykul,
        Please read below. We have already shown why this doesn't belong in the now defunct SQLPS module and should instead be available to every PowerShell user.

      • Anonymous commented  ·   ·  Flag as inappropriate

        It is hard to imagine working in PowerShell and SQL Server without Out-DataTable. (or ConvertTo-Datatable) because the SqlBulkCopy Class, which eats datatables, is the obvious way of copying data into SQL Server.

      • Ben MillerBen Miller commented  ·   ·  Flag as inappropriate

        I use Out-DataTable every day and have automated processes that use it. I believe this is absolutely the right thing to do for the SQLPS side of things. When I am getting data out of one instance because SMO does not have access to all the DMVs, it would be great to not have to convert the results of rows into a DataTable for inserting into the stats instance.

      • Matthew Reynolds [MSFT, but not powershell team]Matthew Reynolds [MSFT, but not powershell team] commented  ·   ·  Flag as inappropriate

        Adding flavor to the conversation.... You can use ORMs like EntityFramework to work with database data in an object-y, scalable (depending on usage) way. I love doing this in PowerShell.
        This makes me wonder how folks are using DataTable. Are people using it as a sort of ORM? Are they using it because of the query/sort/group semantics, something else?
        I feel like if we understood the scenarios more it would be easier to scope this.

      • Martin PughMartin Pugh commented  ·   ·  Flag as inappropriate

        Just wondering if this shouldn't be called ConvertTo-DataTable (keeping in line with ConvertTo-CSV, etc).

      • Chrissy LeMaireChrissy LeMaire commented  ·   ·  Flag as inappropriate

        Hey Max,
        Aaron and I had talked about this and believe it's in the realm of the PowerShell team which is why we filed it here and not Connect.

        DataTables are part of System.Data and aren't specific to SQL Server. I use DataTables when working with CSVs, SQLite and another guy used it with Postgres. It would be beneficial if it was a global out, like Out-GridView.

        It will be cool when PowerShell gets to Linux, too, because then people could use it with MySQL ;)

      • Scott StaufferScott Stauffer commented  ·   ·  Flag as inappropriate

        I agree with Aaron and Chrissy's assertions. It should be all about adoption. After adoption, you will get more input. It's the classic chicken and egg thing... or wait, no... maybe it's the "putting the horse before the carriage" thing... Regardless, PowerShell is cool, Microsoft seems to want to use it for automation - probably a good choice. DBAs love automation and are wondering why they should use it if it only does a small bit of what they need without doing back flips. Increase adoption - where is MS Marketing when you need them! :) UserVoice is a great and Aaron and Chrissy's efforts to draw people into the conversation are equally great. I'd see value in "Out-DataTable" and it might make me take some time with PowerShell - "Make PowerShell Great Again!"

      • Chrissy LeMaireChrissy LeMaire commented  ·   ·  Flag as inappropriate

        Hey Joey,
        Thanks for responding. As you may be aware, getting the SQL Server community to adopt PowerShell has been challenging for a variety of reasons. Because of this, it will be hard to get proponents of Out-DataTable to jump in and vote because... we'll be trying to use Out-DataTable to recruit them.

        Out-DataTable will be key for easy SQL imports. If we get it, we can also suggest to the SQL Server team a few fun ways to take advantage of it in SQLPS.

        I understand your position, however, and will work to call in the cavalry.

      • Chrissy LeMaireChrissy LeMaire commented  ·   ·  Flag as inappropriate

        YES PLEASE! DataTables are amazing, and if they are made easily accessible, it will help the adoption of PowerShell within the SQL Server community.

      Feedback and Knowledge Base