Null-Valued Expression Error

PowerShell Help
1

Hello All,

I’m very new to Powershell and am trying to execute some code which will remove commas from certain fields in a CSV file, then output the result back to a file. While my script does generate the following error, it still does finish and create a new file when I run it from the Powershell ISE. However, I need to run this from a SQL job agent (i’m a data guy) and the error actually stops the job.

There error is here:

You cannot call a method on a null-valued expression.
At line:9 char:9

  • if ($i.Community_Name.split(',')[1])

  •     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidOperation: (:slight_smile: , RuntimeException
    • FullyQualifiedErrorId : InvokeMethodOnNull

My code i’m running is here:

$array = Import-Csv -Path D:\DataExtracts\HolidayPricing\holiday.csv | Select UniqueID,Community_Name,Company_Number,LastUpdated,UnitType,MinStartMarketRate

$finalarray = @()

Foreach ($i in $array)
{
$newobject = New-Object System.Object
$newobject | Add-Member -Type NoteProperty -Name ‘Unique ID’ -Value $i.UniqueID
if ($i.Community_Name.split(‘,’)[1])
{
$formatted = $i.Community_Name.split(‘,’)[0] + ‘;’ + $i.Community_Name.split(‘,’)[1]
}
else
{
$formatted = $i.Community_Name.split(‘,’)[0]
}
$newobject | Add-Member -Type NoteProperty -Name ‘Community_Name’ -Value $formatted
$newobject | Add-Member -Type NoteProperty -Name ‘Company_Number’ -Value $i.Company_Number
$newobject | Add-Member -Type NoteProperty -Name ‘LastUpdated’ -Value $i.LastUpdated
$newobject | Add-Member -Type NoteProperty -Name ‘UnitType’ -Value $i.UnitType
$newobject | Add-Member -Type NoteProperty -Name ‘MinStartMarketRate’ -Value $i.MinStartMarketRate
$finalarray += $newobject
}
$finalarray | Export-Csv -Path D:\DataExtracts\HolidayPricing\holiday_Out.csv

2

At that point, either $i is empty of $i.Community_Name doesn’t exist.

Note that there’s no need to use $array…

foreach ($i in (Import-CSV ...)) {}

Will save a bit of memory.

You might look into using Write-Verbose (you’ll need to enable the Verbose pipeline by setting $VerbosePreference=‘Continue’) to output the contents of $i

Write-Verbose "$i"

Before your If construct. That would let you see what $i really contains at that point. It’s also possible that $i.Community_Name isn’t evaluating as a String, but that’d usually be a different error. It’s likely that Community_Name is coming up empty.

3

Thanks, Don.

Community_Name is actually empty in one instance, when I run just the $array portion of the script it returns the data from the file, however the very last one is empty for all columns.

How can I handle this?

4

Add another If construct to test the property for being equal to $null, or an empty string, before you try to do something with it.

5

Or, fix your CSV file to not have a blank line at the end, if that’s possible. Or, filter those out:

foreach ($i in (
  import-csv blah blah | where { $_.Community_Name -neq $null }
)) {}

Or something like that, logic-wise.