Array of variables in PowerShell has null members

Question

I have a PowerShell script, where I want to make sure certain variables have value before proceeding.

So I have the following:

$dataRow = $sheet.Cells.Find($country).Row
$serverCol = $sheet.Cells.Find($serverString).Column
$databaseCol = $sheet.Cells.Find($databaseString).Column
$userCol = $sheet.Cells.Find($userString).Column
$passwordCol = $sheet.Cells.Find($passString).Column
$partnerCol = $sheet.Cells.Find($partnerString).Column

#All variables in this array are required. If one is empty - the script cannot continue
$requiredVars = @($dataRow, $serverCol, $databaseCol, $userCol, $passwordCol, $partnerCol)

But when I foreach over the array like so:

foreach ($var in $requiredVars)
{
    Write-Host DataRow = ($dataRow -eq $var)
    Write-Host ServerCol = ($serverCol -eq $var)
    Write-Host DatabaseCol = ($databaseCol -eq $var)
    Write-Host UserCol = ($userCol -eq $var)
    Write-Host PasswordCol = ($passwordCol -eq $var)
    Write-Host PartnerCol = ($partnerCol -eq $var)
    if ($var -eq $null)
    {
        [System.Windows.Forms.MessageBox]::Show("No data found for given string!")
        $excel.Quit()
        return
    }
}

I always get the MessageBox. I added the "Write-Host" part to see the value of each variable, then changed it to see which variable was null but all variables have values in them and all the checks you see here return "False".

I'd like to know what I'm doing wrong and if the $requiredVars array only copies values, not references or something.

A confusing way to write it. Of course, 80% of these statements will return false either way. — marsze, Commented Dec 19, 2018 at 8:14
Also, you should write $null -eq $var instead. Have you tried that? Look here: rencore.com/blog/powershell-null-comparison — marsze, Commented Dec 19, 2018 at 8:26
@marsze, why is it confusing? If you mean the "Write-Host" bit, that is just for debugging/testing purposes. But still I welcome any advice on improvement. Please divulge. — J. Doe, Commented Dec 19, 2018 at 9:23

Theo · Accepted Answer · 2018-12-19 11:54:03Z

Instead of using separate variables, you may consider using a Hashtable to store them all.
This makes checking the individual items a lot simpler:

# get the data from Excel and store everything in a Hashtable
# to use any of the items, use syntax like $excelData.passwordCol or $excelData['passwordCol']
$excelData = @{
    'dataRow'     = $sheet.Cells.Find($country).Row
    'serverCol'   = $sheet.Cells.Find($serverString).Column
    'databaseCol' = $sheet.Cells.Find($databaseString).Column
    'userCol'     = $sheet.Cells.Find($userString).Column
    'passwordCol' = $sheet.Cells.Find($passString).Column
    'partnerCol'  = $sheet.Cells.Find($partnerString).Column
}

# check all items in the hash. If any item is $null then exit
foreach ($item in $excelData.Keys) {
    # or use: if ($null -eq $excelData[$item])
    if (-not $excelData[$item]) {   
        [System.Windows.Forms.MessageBox]::Show("No data found for item $item!")
        $excel.Quit()

        # IMPORTANT: clean-up used COM objects from memory when done with them
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null
        # Your code doesn't show this, but you'll have a $workbook object in there too
        # [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()

        return
    }
}

gvee · Accepted Answer · 2018-12-19 09:12:01Z

One way to directly solve your question is this:

$a = "foo"
$b = "bar"
$c = $null

$requiredVariables = $a, $b, $c

# How many total entries in array?
($requiredVariables).Count

# How many of them have a value?
($requiredVariables | Where-Object {$_}).Count

# So one option for a single check would be:
if (($requiredVariables.Count) -ne ($requiredVariables | Where-Object {$_}).Count) {
    Write-Warning "Not all values provided"
}

However an alternative [and better] approach is to make your code in to a function that includes parameter validation

function YourCustomFunction {
    Param (
    [ValidateNotNullOrEmpty()]
    $a
    ,
    [ValidateNotNullOrEmpty()]
    $b
    ,
    [ValidateNotNullOrEmpty()]
    $c
    )

    Process {
        Write-Output "Your function code goes here..."
    }
}

# Call your function with the params
YourCustomFunction -a $a -b $b -c $c

Example output:

Test-YourCustomFunction: Cannot validate argument on parameter 'c'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again. At line:39 char:48

Thanks, but the point of the script is to read the mentioned variables from an Excel file. I added the validation to check if the read was successful. — J. Doe, Commented Dec 19, 2018 at 9:45
Simple: put the Excel part before the call to your function. — gvee, Commented Dec 19, 2018 at 11:02
Am I correct in assuming using you approach the error will only be shown on the command line? Because I have a shortcut that starts my script and the window closes almost immediately after it returns. And I want to keep it that way, that is why I used a MessageBox. — J. Doe, Commented Dec 20, 2018 at 11:41

Collectives™ on Stack Overflow

Array of variables in PowerShell has null members

2 Answers 2

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Related