Have PowerShell pass results to Pentaho

Question

I have a PowerShell script that processes a json string. My goal is to have this pass a resultset to Pentaho so I can process it and put it in a database table.

My PowerShell script works as expected outside of Pentaho. I can parse the files and get the information I need without any issues. It's when I try to pass those values is when Pentaho returns goofy results.

Here is my script

$scriptMode = 'GetFileInfo'

$json = '{
"building": [
    {
        "buildingname": "NAPA Auto Parts",
        "files": [{
                "sheets": [{
                        "name": "BATTERY",
                        "results": [{
                                "filename": "BATTERY - 1679568711.xlsx",
                                "sku": "1679568711"
                            }
                        ]
                    }
                ],
                "name": "2.15.19.xlsx",
                "status": "processed",
                "fileId": "c586bba6-4382-42c4-9c29-bffc6f7fe0b6"
            }, {
                "name": "Oct-Nov 2018 11.30.18.xlsx",
                "errors": ["Unknown sheet name: TOILET PLUNGER"],
                "status": "failed",
                "fileId": "afa7c43f-26dc-421c-b2eb-45ad1e899c42"
            }
        ]
    },
    {
        "buildingname": "O''Reily Auto Parts",
        "files": [{
                "sheets": [{
                        "name": "ALTERNATOR",
                        "results": [{
                                "filename": "ALTERNATOR - 6.3.19 1629453444.xlsx",
                                "sku": "1629453444"
                            }
                        ]
                    }, {
                        "name": "OIL FILTER",
                        "results": [{
                                "filename": "OIL FILTER - 6.3.19 1629453444.xlsx",
                                "sku": "1629453444"
                            }
                        ]
                    }
                ],
                "name": "6.3.19.xlsx",
                "status": "processed",
                "fileId": "647089fe-9592-4e2b-984f-831c4acd4d9c"
            }
        ]
    }
]
}'

$psdata = ConvertFrom-Json -InputObject $json

IF ($scriptMode -eq "GetFileInfo") {

$psdata.building | foreach-Object {
    foreach ($File in $_.files)
    {
        [PSCustomObject]@{
            BuildingName = $_.buildingname
            FileName = $File.name
            fileId = $File.fileId
            Status = $File.status} 
    }
  }
}

ElseIF ($scriptMode -eq "GetErrorInfo") {

$psdata.building | foreach-Object {
    foreach ($File in $_.files)
    {
        [PSCustomObject]@{
            BuildingName = $_.buildingname
            Errors = $File.errors
            SheetName = $File.sheets.name
            fileId = $File.fileId} | Where-Object {$_.errors -ne $null}
    }
  }
}

And here's how I have my transformation setup. I have a table input query that will set the run command for PowerShell based on what I want the script to do (either get file info or get error info).

Then I have the "Execute a process" step run the PowerShell command

This is what is returned in Pentaho vs what PowerShell returns

I'm expecting the results to be returned exactly as PowerShell returns them. I'm hoping I can accomplish this without exporting the data to another format. We have had nothing but issues with the Json Input step in Pentaho, so we chose PowerShell over the "Modified Javascript Value" step in Pentaho.

Any idea how I can get this to return a result set (like a SQL query would return) back to Pentaho?

Answer 1

Most likely your result set is returning the entire thing, just not "tabled" as you expected, it's probably returning the entire table all summed up in one long text format, but still having all the line breaks / column breaks.

Try using Split steps in your pentaho flow to work on the returned String. First off, try using a "Split field to rows" with the delimiter as "${line.separator}". From there all you to do is pretty much split the whole thing until it is a table in pentaho.