Null-Valued Expression Error

This topic contains 4 replies, has 2 voices, and was last updated by  Don Jones 2 years, 7 months ago.

  • Author
    Posts
  • #22773

    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

    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

    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

    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

    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.