Need to Create Powershell output file with Headers if there is no Data

Question

I have created a script which exports the firewall rules from Azure and it works Perfectly , However I need to create .csv with headers even if there is no data. Currently it is creating a blank file.

``function create($path) {
    $exists = Test-Path -path $path
    Write-Host "tried the following path: $path, it" $(If ($exists) {"Exists"} Else {"Does not Exist!"}) 
    if (!($exists)) { New-Item $path -itemType Directory }
}

# reading file contents 
$subs_file =  "C:\script\Subscriptions.xlsx"
$azSubs = Import-Excel $subs_file
$azSubs
$output_folder = "C:\audit-automation"
# creating folder for outputing data 
create("$output_folder")
# New-Item $output_folder -itemType Directory

# iterating over subscriptions 
ForEach ( $sub in $azSubs ) {
    # sub
    $azsub = $sub.Subscription
    # app
    $app = $sub.Application
    $azsub
    $app
    # creating folder to save data for apps  
    # New-Item $output_folder\$app -itemType Directory
    # setting config for azure 
    Set-AzContext -SubscriptionName $azsub
        
    # FIREWALL RULES
    $azNsgs = Get-AzNetworkSecurityGroup
    # iterating over retrieved NSGs 
    $Output = ForEach ( $azNsg in $azNsgs ) {
        #Export custom rules
        Get-AzNetworkSecurityRuleConfig -NetworkSecurityGroup $azNsg | `
            Select-Object @{label = 'NSG Name'; expression = { $azNsg.Name } }, `
        @{label = 'NSG Location'; expression = { $azNsg.Location } }, `
        @{label = 'Rule Name'; expression = { $_.Name } }, `
        @{label = 'Source'; expression = { $_.SourceAddressPrefix } }, `
        @{label = 'Source Application Security Group'; expression = { $_.SourceApplicationSecurityGroups.id.Split('/')[-1] } },
        @{label = 'Source Port Range'; expression = { $_.SourcePortRange } }, Access, Priority, Direction, `
        @{label = 'Destination'; expression = { $_.DestinationAddressPrefix } }, `
        @{label = 'Destination Application Security Group'; expression = { $_.DestinationApplicationSecurityGroups.id.Split('/')[-1] } }, `
        @{label = 'Destination Port Range'; expression = { $_.DestinationPortRange } }, `
        @{label = 'Resource Group Name'; expression = { $azNsg.ResourceGroupName } },
        @{label = 'Subscription Name'; expression = { $azSub } } 
    }
    # creating folder to save 
    # New-Item $output_folder\$app\firewall_rules -itemType Directory
    create("$output_folder\$app")
    $Output | Export-Csv -Path $output_folder\$app\$app-firewall_rules_data$((Get-Date).ToString("yyyy-MM-dd")).csv -Append`

You are printing the header data inside the Foreach loop : ForEach ( $sub in $azSubs ). When $azSubs is empty no header is generated. — jdweng, Feb 15 '23 at 14:06
Side-note - you don't use brackets in cmdlet calls in PowerShell - ```create("$output_folder\$app")``` should be ```create "$output_folder\$app"``` - see https://stackoverflow.com/a/4988239/3156906 — mclayton, Feb 15 '23 at 14:07
@jdweng This is part of my script which. There is one more part of the script and it is working fine. I referred below question for the solution. https://stackoverflow.com/questions/75446835/powershell-does-not-create-csv-if-there-is-no-data-to-export-in-azure#comment133126182_75450401 — Rav, Feb 15 '23 at 14:15
The csv file(s) with no header are the ones where the excel file has no subscriptions : $azSubs = Import-Excel $subs_file. Yo udo not enter the foreach loop so no header is added to the file. — jdweng, Feb 15 '23 at 14:26

score 0 · Accepted Answer · answered Feb 15 '23 at 14:31

Test whether the $Output variable contains anything - if not, create a "dummy object" with the same shape as the actual output objects and use that in conjunction with ConvertTo-Csv to generate the header:

# ...
create("$output_folder\$app")
$outputPath = "$output_folder\$app\$app-firewall_rules_data$((Get-Date).ToString("yyyy-MM-dd")).csv"

if($Output) {
    $Output | Export-Csv -Path $outputPath -Append -NoTypeInformation
}
elseif (-not(Test-Path $outputPath -PathType Leaf)) {
    # no output data, and the file doesn't already exist - create headers only
    $dummyObject = [pscustomobject]@{
        'NSG Name' = ''
        'NSG Location' = ''
        'Rule Name' = ''
        'Source' = ''
        'Source Application Security Group' = ''
        'Source Port Range' = ''
        'Destination' = ''
        'Destination Application Security Group' = ''
        'Destination Port Range' = ''
        'Resource Group Name' = ''
        'Subscription Name' = ''
    }

    # generate dummy CSV document, then discard the row representing the dummy object
    $header = $dummyObject |ConvertTo-Csv -NoTypeInformation |Select -SkipLast 1
    $header |Set-Content $outputPath

}

mclayton · Answer 2 · 2023-02-15T15:13:15.177

The problem you've got is that if there's no objects in $output then Export-Csv isn't able to determine what headers to use. In that case, you're going to need to generate the header text file yourself...

This is slightly more convoluted than @MathiasRJessen's answer, but it has the advantage that you only need to declare your headers once in your code, rather than keep then in sync in the Select-Object parameters and the $dummyObject path...

First, we'll move the property definitions out of Select-Object into a variable...

$csvColumns = @(
    @{label = 'NSG Name'; expression = { $azNsg.Name } },
    @{label = 'NSG Location'; expression = { $azNsg.Location } },
    @{label = 'Rule Name'; expression = { $_.Name } },
    @{label = 'Source'; expression = { $_.SourceAddressPrefix } },
    @{label = 'Source Application Security Group'; expression = { $_.SourceApplicationSecurityGroups.id.Split('/')[-1] } },
    @{label = 'Source Port Range'; expression = { $_.SourcePortRange } },
    "Access",
    "Priority",
    "Direction",
    @{label = 'Destination'; expression = { $_.DestinationAddressPrefix } },
    @{label = 'Destination Application Security Group'; expression = { $_.DestinationApplicationSecurityGroups.id.Split('/')[-1] } },
    @{label = 'Destination Port Range'; expression = { $_.DestinationPortRange } },
    @{label = 'Resource Group Name'; expression = { $azNsg.ResourceGroupName } },
    @{label = 'Subscription Name'; expression = { $azSub } }
);

and your Select-Object then becomes this:

Get-AzNetworkSecurityRuleConfig -NetworkSecurityGroup $azNsg `
    | Select-Object $csvColumns

Once we've got that working we can re-use the $csvColumns variable to generate the headers with this function:

function Get-CsvHeaderText
{
    param( $CsvColumns )

    # create a temporary hashtable to hold the header names in the keys
    $csvHeaders = [ordered] @{};

    # add the header names for each csv column - note that each item
    # can be a string (e.g. "Access") or a hashtable (e.g. "@{label = 'NSGLocation'; expression = { $azNsg.Location } }")
    foreach( $csvColumn in $CsvColumns )
    {
        if( $csvColumn -is [string] )
        {
            $csvHeaders.Add($csvColumn, [string]::Empty);
        }
        else
        {
            $csvHeaders.Add($csvColumn.Label, [string]::Empty);
        }
    }

    # now that we've got a fake object with the correct property names
    # we can convert it to csv and extract the header row(s). note - 
    # use "-SkipLast 1" to ignore the empty data row instead of
    # "-First 1" in case a header name has line breaks in it
    # (unlikely, but technically possible)
    $csvHeaderText = $headers | ConvertTo-csv | select-object -SkipLast 1;

    return $csvHeaderText;

}

Example usage:

$headerText = Get-CsvHeaderText -CsvColumns $csvColumns;

which you can then write to your file if $output is $null:

if( $null -eq $Output ) {
    $headerText = Get-CsvHeaderText -CsvColumns $csvColumns;
    $headerText | Set-Content "myfile.csv";
}
else
{
    $Output | Export-Csv -Path "myfile.csv" -Append -NoTypeInformation;
}

Need to Create Powershell output file with Headers if there is no Data

2 Answers2