Powershell Select-Object calculated property data type

Question

I've added a calculated column using Select-Object.

$ParsedContent = $ContentSplit.Groups[2].Value |  
    ConvertFrom-String -TemplateFile $TemplatePath |
        Select-Object -Property *,
            @{Name = 'ParcelNo' ; Expression = {[int]::parse($_.Reference.SubString(0,3))}}

When I then attempt to use this ParcelNo value in another function, it gives me an error that the value is PSObject rather than Int32.

Write-SqlTableData @SqlArguments
     |      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Unable to cast object of type 'System.Management.Automation.PSObject' to type 'System.IConvertible'.Couldn't store <1> in ParcelNo Column. 
     | Expected type is Int32.

Checking type using GetType() shows Int32, but inspecting variables in VSCode shows PSObject instead.

Any idea why these would be different? How would I make sure that the calculated property is an actual Int32.

Powershell version 7.0.3

Edit: Here's how building SqlArguments looks. Sorry it's a bit convoluted, I might refactor some bits of that eventually. The ParseTxt function is the bit containing $ParsedContent from my screenshot.

$DataToUpload = @()
Foreach($File in $FilesToProcess){
    $DataToUpload += (ParseTxt -Path $File.FullName -TemplatePath $TemplatePath)
}

$SqlArguments = @{
    InputData = $DataToUpload
    ServerInstance = 'myserver'
    DatabaseName = 'mydb'
    SchemaName = 'dbo'
    TableName = 'mytable'
}

Can you show how you're building the ```$SqlArguments``` variable? You *might* be able to fix the issue by casting your property to ```[int]``` similar to this: https://stackoverflow.com/questions/47727369/powershell-trying-and-failing-to-cast-a-psobject-to-int32. e.g. ```$SqlArguments = @{ "xyz" = $ParsedContent.ParcelNo }``` becomes ```$SqlArguments = @{ "xyz" = [int] $ParsedContent.ParcelNo }``` — mclayton, Sep 22 '20 at 11:28
@mclayton I don't think that will work in my case, since the InputData parameter inside $SqlArguments is an array holding both ints and strings (and PSObject somehow, which is the issue). — OwlsSleeping, Sep 22 '20 at 11:49
Updated screenshots to more clearly show the content of $DataToUpload — OwlsSleeping, Sep 22 '20 at 11:54
What do you get if you run `$DataToUpload[0].ParcelNo.GetType()` (instead of `$DataToUpload.ParcelNo[0].GetType()`? — notjustme, Sep 22 '20 at 11:56
@notjustme good point, that's a dumb typo. Both give int32. Same value too, it's interesting that they somehow work the same. — OwlsSleeping, Sep 22 '20 at 12:02
If you do ```$DataToUpload[0] | gm```, does it show ```ParcelNo``` as a ```int```, ```NoteProperty``` or ```ScriptProperty```? I think the problem is PowerShell sometimes likes to wrap values in its own (semi-invisible) wrappers, which means they can seem like an ```[int]``` for *most* intents and purposes, but really be a ```psobject``` when used in other ways. See this pretty comprehensive answer for more details... https://stackoverflow.com/a/51178465/3156906 — mclayton, Sep 22 '20 at 12:43
@mclayton thanks for the link, I'll give that a good read. The result with Get-Member is that ParcelNo which I added with a calculated property is system.int32 but the others are just int. For comparison: NoteProperty int BoxNum=1. NoteProperty System.Int32 ParcelNo=1 — OwlsSleeping, Sep 22 '20 at 12:54

OwlsSleeping · Answer 1 · 2020-09-22T21:24:19.610

1

For future readers, here's the workaround I used-

Foreach($DataRow in $DataToUpload){
    $DataRow.ParcelNo = [int]$DataRow.ParcelNo
}

If somebody comes up with a proper solution that handles it during the initial Select-Object rather than requiring a workaround afterwards, I'll accept that as answer.

edited Sep 22 '20 at 21:24

answered Sep 22 '20 at 20:36

OwlsSleeping

1,487
2
11
19

Powershell Select-Object calculated property data type

1 Answers1