Output to Excel With Custom Table

PowerShell Help
1

Hello,

I’m attempting to output a PS custom table to Excel but I receive the following when I open Excel:

ClassId2e4f51ef21dd47e99d3c952918aff9cd	pageHeaderEntry	pageFooterEntry	autosizeInfo	shapeInfo	groupingEntry
033ecb2bc07a4d43b5ef94ed5a35d280			Microsoft.PowerShell.Commands.Internal.Format.AutosizeInfo	Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo

Here’s the code:

Connect-AzAccount
 
#Select Azure Subscription
Get-AzSubscription -SubscriptionName $SubscriptionName | Select-AzSubscription

$cusTab1 = @{Expression={$SubscriptionName};Label="Subscription Name";width=15},
           @{Expression={$_.ResourceGroupName};Label="Resource Group";width=50}, `
           @{Expression={$_.ResourceName};Label="Resource Name";width=50}, `
           @{Expression={$_.ResourceType};Label="Resource Type";width=100}
 
$AzVMS = Get-AzResource | Where-Object ResourceType -EQ Microsoft.Compute/virtualMachines | select $SubscriptionName, ResourceGroupName, ResourceName, ResourceType

$AzVMS | Format-Table -AutoSize $cusTab1 | Export-Excel -Path 'C:\PowerShell\output\azr-database-list-2.xls'

Any idea?

Thanks!

2

Format cmdlets like Format-Table are used to format console output only. If you want to use the collected data for further steps you must NOT use format cmdlets.

This should do the trick:

Connect-AzAccount
Get-AzSubscription -SubscriptionName $SubscriptionName | Select-AzSubscription
$cusTab1 = @{Expression={$SubscriptionName};Label="Subscription Name"},
           @{Expression={$_.ResourceGroupName};Label="Resource Group"},
           @{Expression={$_.ResourceName};Label="Resource Name"},
           @{Expression={$_.ResourceType};Label="Resource Type"}
 
$AzVMS = 
    Get-AzResource | 
        Where-Object ResourceType -EQ Microsoft.Compute/virtualMachines 

$AzVMS | 
    Select-Object -Property $cusTab1 |
        Export-Excel -Path 'C:\PowerShell\output\azr-database-list-2.xls'
3

There’s no -Property argument available for the ‘Select-Object’ cmdlet:

Select-Object : The width key is not valid.
 char:5
+     Select-Object -Property $cusTab1 |
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Select-Object], NotSupportedException
    + FullyQualifiedErrorId : DictionaryKeyIllegal,Microsoft.PowerShell.Commands.SelectObjectCommand

I changed the code according to what you provided.

4

I think i know what needs to be done. I’ll post shortly.

5

I changed my code suggestion just a minute after I posted it initially. Probably you took the “original” - try it now.

6

That works, but it’s still adding an extra column with the following:

shapeInfo
Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo
7

Are you still using a format cmdlet? Please show the code you’re using to get this output?

8

No format cmdlet.

PARAM(
    [Parameter(Mandatory = $True, 
    HelpMessage = "Choose subscription you want be inventoried")]
    [string]
    $SubscriptionName
    )

Connect-AzAccount
 
#Select Azure Subscription
Get-AzSubscription -SubscriptionName $SubscriptionName | Select-AzSubscription
#Format output using custom table
$cusTab1 = @{Expression={$SubscriptionName};Label="Subscription Name"},
           @{Expression={$_.ResourceGroupName};Label="Resource Group"}, `
           @{Expression={$_.ResourceName};Label="Resource Name"}, `
           @{Expression={$_.ResourceType};Label="Resource Type"}
$AzVMS = 
    Get-AzResource | 
        Where-Object ResourceType -EQ Microsoft.Compute/virtualMachines 

$AzVMS | 
    Select-Object -Property $cusTab1  |
        Export-Excel -Path 'C:\PowerShell\output\azr-database-list-2.xls'
9

I cannot reproduce your behaviour. When I run this code (except of using xlsx instead of xls) I get exactly the expected result.

10

This time before I ran it, I deleted the existing file, then ran. No issues.

Thanks for all of your help!

11

There’s a good chance there was something “cached”. Always good to try and restart the software that’s being used to run the code!