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.

Answer 2

The workaround I have come up with is to use ($_ -split ' ')[1..100] for second column #JOBNAME and ($_ -split ' ')[0] for first column #JOBID.

Note: This only works because the JOBID column values is phrased as one single word. It doesn't work for other queries if the first column has random number of words.

Here is the final command and output :

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

Output:

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

There is also Invoke-SQLcmd method but it takes 30 seconds to add snap-in and 2 seconds to execute query.

    Add-PSSnapin SqlServerCmdletSnapin100; $coma=''; Write-Output '{"data":[';
    (Invoke-Sqlcmd -ServerInstance 'x.x.x.x' -username 'user' -password 'password' -inputfile "C:\query.sql") | %{
        try {
    ($coma + '{"{#JOBID}":"' + $_.job_id  + '",' + '"{#JOBNAME}":"' + $_.name + '"}');
    $coma=',';
        } catch {}
    };
    Write-Output "]}"

If you have Powershell v3 then you could just use Sqlcmd... | ConvertTO-Json