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.

If I assign sqlcmd output to` $var1` and then run `$var1 | select $_ | get-member` I get this output. https://imgur.com/a/McJ1U It seems like the whole sqlcmd output is saved as text based table so maybe properties for it? I'm not sure — user630702, Mar 14 '18 at 10:26
There is absolutely NO valid reason for using PowerShell v1. All currently supported Windows versions can run at least PowerShell v3. Even Windows XP and Server 2003 can run PowerShell v2 at the very least. Upgrade now. — Ansgar Wiechers, Mar 14 '18 at 10:48
This isn't about version, just trying to get something done through write-host. If fixed, I could post it to the community where they can use either version 1 or version 3 method. I really did not wanted any comments about version 1 and that's why I specifically mentioned that I'm aware of it. I do recommend version 3 to others but this 1.0 issue seems simple if we could just find a way to split the data. Rest of the format is JSON ready. Do you have any inputs for $_.name issue? — user630702, Mar 14 '18 at 11:02
It is the same output as Select-Object $_. I first tried without select-object but get-member was same for both — user630702, Mar 14 '18 at 12:41
Also if I try `$var1 | select name` it returns empty. If I don't specify name or $_ it returns all lines. Note: the output shown above shows extra spaces as if its like tab, but in reality the output has only one space separating JOBID and JOBNAME. I'll correct the output in the post — user630702, Mar 14 '18 at 12:49
Can you think of someway to set headers or properties when assigning `$var=(sqlcmd -s x.x.x. ..)` so it can be called with `$_.property`? — user630702, Mar 14 '18 at 12:51

user630702 · Answer 1 · 2018-03-15T10:02:04.137

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

user630702 · Accepted Answer · 2018-03-15T10:02:47.590

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.

Select (or split) column from SQLCMD output

2 Answers2