Accessing text in PowerShell object. Why doesn't it seem to be stored in an array?

Question

I have taken state plane coordinates from a SQL Server table and populated an object with 100 concatenated strings that will serve as my input into a java application.

$key = Invoke-Sqlcmd -Query "SELECT DISTINCT CONCAT('spc,'<spcZone>,',',
<northing>,',',<easting>, ',',<units>,',',<inDatum>,',',<outDatum>) as 
conversionString FROM <source table>;" -ServerInstance "<server>" -Database 
"<database>"

The result will be 100 strings that look like this in an array:

spc,2402,173099.419,503626.812,m,NAD83(2011),NAD83(2011)

I then use NOAA's gtk java application and run through all 100 observations to be converted:

$result = FOREACH ($k in $key.conversionString) 
{
java -Dparms="$k" -jar H:\gtk\jtransform_thin.jar
}

The returned output for one observations looks like this:

{
"ID":"1489004917960",
"nadconVersion":"5.0",
"srcLat":"40.0000000000",
"srcLatDms":"N400000.000000",
"srcLon":"-80.0000000000",
"srcLonDms":"W0800000.000000",
"destLat":"40.0000000000",
"destLatDms":"N400000.000000",
"destLon":"-80.0000000000",
"destLonDms":"W0800000.000000",
"sigLat":"0.000000",
"sigLon":"0.000000",
"srcEht":"100.000",
"destEht":"100.000",
"sigEht":"0.000",
"srcDatum":"NAD83(1986)",
"destDatum":"NAD83(1986)",
"spcZone":"PA S-3702",
"spcNorthing_m":76470.584,
"spcEasting_m":407886.482,
"spcNorthing_usft":250887.241,
"spcEasting_usft":1338207.566,
"spcNorthing_ift":250887.743,
"spcEasting_ift":1338210.243,
"spcConvergence":"-01 27 35.224524",
"spcScaleFactor":0.99999024,
"spcCombinedFactor":0.99997455,
"utmZone":"UTM Zone 17",
"utmNorthing":4428236.065,
"utmEasting":585360.462,
"utmConvergence":"00 38 34.174932",
"utmScaleFactor":0.9996897,
"utmCombinedFactor":0.99967402,
"x":849623.061,
"y":-4818451.818,
"z":4078049.851,
"usng":"17TNE8536028236"
}

The problem I'm encountering is accessing the stored $result object's returned fields. If I type $result. It will return all text from all 100 observations. If I type $result[1] I only get the ID of the first observation. If I type $result.ID I do not have anything returned.

This is where I am trying to get to:

$add = foreach ($r in $result)
{
"INSERT INTO SPCtoLatLong VALUES ('" + $r.spcZone + "','" + 
$r.spcNorthing_usft + "','" + $r.spcEasting_usft + "','" + $r.srcLat + "','" 
+ $r.srcLon + "','" + $r.srcDatum + "','" + $r.destDatum + "')" + $nl
}

I only have 2 weeks experience in PowerShell, what am I doing wrong? Thank you for any help.

Answer 1

Stdout from external applications is returned as a string array (one line = one enum). You would need to parse the return data into an object, or, if you're on PowerShell 5+, you can utilize the ConvertFrom-String to create templates for these objects and pass it output to it to be converted.

But! Based on how well-formed your data is, I would do the following:

$Expected = $Result | ConvertFrom-Json

PS C:\> $Expected.GetType()
PSCustomObject

Answer 2

I figured out to solve this problem you should add this to the java application execution component.

$result = FOREACH ($k in $key.conversionString) 
{
java -Dparms="$k" -jar H:\gtk\jtransform_thin.jar | ConvertFrom-Json
}