PowerShell Quick Tip: Creating wide tables with PowerShell
A few weeks back I took a trip to Columbus, Ohio where I had the opportunity to meet with the members of the Central Ohio PowerShell Users Group and talk about PowerShell and PowerGUI®. During that event, one of the attendees highlighted a problem they were having. The problem he highlighted is a fairly common one, so I wanted to share the problem and solution here.
When using PowerShell to generate tabular output, the table that is generated is limited by the width of the buffer. This is intentional so that the tables always fit in your console window so that they can be easily understood, and for the most part this doesn’t get in the way too much because you can control which columns are shown in the table so that you get the data that is most important to you or you can use Format-List to generate list output when you really want a lot of data. It can get in the way though if you want to share data from a large table with someone else, store data in a text file for archival purposes, etc. In these cases you are faced with a challenge because Format-Table does not appear to allow you to create large tables like this due to a few issues that get in your way.
Issue #1: PowerShell truncates table output by default
Let’s say you want to create a table showing a list of aliases for Invoke commands and you want to view all properties for those aliases. The command to do this seems pretty straightforward, simply calling Get-Alias -Definition Invoke-* and passing the results to Format-Table -Property * to show all of the columns. Here are the results of that command, showing a table with column names that span multiple lines and rows with data that is not useful:
As you can see, the content of the table is truncated so you can’t really tell what the data is in the table, and if you output the results of that command to a file where the lines can be longer you still get the same results.
Issue #2: Auto-sized tables are limited to the width of your screen buffer
If you try to solve this problem by looking at the help for Format-Table, you may come across the AutoSize parameter. The AutoSize parameter allows you to tell PowerShell that you want the formatter to automatically size the table columns, showing as much data as possible. Here is what happens if you add the AutoSize parameter to the same command you just ran:
This is on the right track for what you are after, but notice the warning text that is output at the top of the resulting table. It indicates that 10 columns do not fit into the display and were removed. If you pipe the results of this command to Out-File, PowerShell will simply pass what you see on the screen to the file you are writing to, which is not sufficient for your needs.
Solution: Strings are not subject to the limitations of other objects in PowerShell
When you pass the results of Get-Alias to Format-Table, the Alias data is converted into objects that describe how the table should look. These objects are then passed implicitly to the Out-Default cmdlet which renders the objects according to their format configuration as well as the current console configuration. In fact, every PowerShell command you run ends with the results being implicitly passed to Out-Default. Even if you pass the results of our Format-Table call to Out-File instead, Out-File will render the objects according to their format configuration and the current console configuration. This is the limitation that is making this seemingly simple task so difficult. What we need to do then is to make sure that the data that is sent to Out-File or Out-Default is pre-formatted so that it is not limited by the size of the screen buffer. How do you do that you ask? The answer is simple: use Out-String.
Out-String is a cmdlet that allows you to convert any output to string format, and you can specify the width of that string using the Width parameter which allows you to exceed the limitations of the PowerShell console buffer size. When strings are passed into Out-Default and Out-File they are simply written as is without any truncation or automatic text wrapping. With that additional piece of knowledge you can pass the results of your Format-Table cmdlet call to Out-String using a width that is large enough for the table output, which successfully forces the objects to be rendered with the width that you want without having to change the buffer size.
Here is a screenshot showing what happens when you pass your Format-Table output to Out-String, specifying a width of 4096 characters:
That might not look very useful either, but look what happens when you take this one step further and pass the results to Out-File. Here is the command to do this:
Get-Alias -Definition Invoke-* `
| Format-Table -Property * -AutoSize `
| Out-String -Width 4096 `
| Out-File C:\aliases.txt
Opening the resulting aliases.txt file shows the following contents:
This is only showing part of the results, but did you notice the horizontal scroll bar at the bottom of the window? It illustrates that you have achieved your goal, creating a table wide enough to show all data in the table inside of a text file that can then be sent to others, stored for archival logging purposes, etc.
Sidebar: If you have a specific property that contains a collection of items, that property may still show an ellipsis in the file produced here if the number of items in that collection exceeds the number assigned to the built-in $FormatEnumerationLimit variable. In this case, if you want to see the entire collection of items, you should temporarily change the value in $FormatEnumerationLimit to something large enough to show your collection (-1 if you want to see all items) and then run a command similar to what I have shown you above. The default value of $FormatEnumerationLimit is 4, which can be limiting when generating a file like this. Increasing this value will give you a full report of what you have in the data you are processing.
With that knowledge in hand, the takeaway for you here is to make sure that you autosize your Format-Table results and that you pass them to Out-String with an appropriate width before you write it to a file if you want to output large tables in text format.
Thanks for listening!