Null-Valued Expression Error

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 1 year, 9 months ago.

  • Author
    Posts
  • #22773
    Profile photo of Ave Stuart
    Ave Stuart
    Participant

    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: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    [b]My code i'm running is here:[/b]

    $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

  • #22774
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #22775
    Profile photo of Ave Stuart
    Ave Stuart
    Participant

    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?

  • #22776
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #22777
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.