Select (or split) column from SQLCMD output

Question

How do I select the columns from SQLCMD output in PowerShell v1? I'm trying to make JSON output using Write-Output in PowerShell v1.

Query output at the end.

$_ returns both columns. If only we could use $_.name and $_.jobid, but they both returns empty lines. Fixing this would be the preferred solution.

Here is the PowerShell command:

Write-Output '{"data":[';
(SQLCMD -S 'x.x.x.x' -U 'user' -P 'passwors' -i "C:\query.sql" -W) | %{
    try {
        ($coma + '{"{#JOBID}":"' + $_  + '",' + '"{#JOBNAME}":"' + $_ + '"}');
        $coma=',';
    } catch {}
};
Write-Output "]}"

What it returns:

{"data":[
,{"{#JOBID}":"12345-aaaa-1234-5678-000000000000000 Clear DB entries","{#JOBNAME}":"12345-aaaa-1234-5678-000000000000000 Clear DB entries"}
,{"{#JOBID}":"12345-bbbb-1234-5678-000000000000000 TempLog DB","{#JOBNAME}":"12345-bbbb-1234-5678-000000000000000 TempLog DB"}
]}

What I expect:

{"data":[
,{"{#JOBID}":"12345-aaaa-1234-5678-000000000000000","{#JOBNAME}":"Clear DB entries"}
,{"{#JOBID}":"12345-bbbb-1234-5678-000000000000000","{#JOBNAME}":"TempLog DB"}
]}

I'm not sure how to use split with tab delimiter ($_ -split "t") for both job_id and name. My attempts either returned both column names as one and in some cases it returned empty.

Here is the query and its output on a command line:

PS C:\> SQLCMD -S 'x.x.x.x' -U 'user' -P 'password' -i "C:\query.sql" -W

job_id name
12345-aaaa-1234-5678-000000000000000 Clear DB entries
12345-bbbb-1234-5678-000000000000000 TempLog DB
(2 rows affected)

I know about ConvertTo-Json on version 3, but I want to get it working on PowerShell v1 so it'd be helpful to those who can't upgrade for whatever reason.

Answer 1

Another method, seems reliable. Thanks to Bacon Bits answer.

$coma=''; Write-Output '{"data":[';
(SQLCMD -S 'x.x.x.x' -U 'user' -P 'password' -i "C:\query.sql" -W -m 1 -s `"`t`") | ConvertFrom-Csv -Delimiter "`t" |  Select-Object -Skip 1 | %{
    try {
        ($coma + '{"{#JOBID}":"' + $_.job_id  + '",' + '"{#JOBNAME}":"' + $_.name + '"}');
        $coma=',';
    } catch {}
};
Write-Output "]}"

If your data contains tabs, you'll need a different separator. The Select-Object -Skip 1 is to skip the underline row that sqlcmd always creates below the header.

Also be aware that you should use the -w parameter on sqlcmd to prevent any incorrect wrapping. Also beware that null values are always output as a literal string NULL.

Again Powershell v3 or Invoke-SQLcmd is recommended over this method.