CODEWITHSUNDEEP
powershelltype-conversionbooleanbitinvoke-sqlcmd
Rami Aboulissane
Rami Aboulissane

Reputation: 41

Powershell converting bit values to Boolean in result set

I have an issue whereby a query called via Invoke-SQLCmd returns a certain set of results. For some columns the value is a 0 or 1 of BIT data type. The results instead come back as False or True in powershell.

Is there anyway to stop this happening?

I have searched far and wide and aside from suggestions to amend the select query and add conversions (which would be too much work considering I am just leveraging the same query but within Powershell in order to generate some logs for monitoring reasons)

Upvotes: 1

Views: 78

Answers (3)

mklement0
mklement0

Reputation: 439777

  • sirtao's helpful answer explains why the MSSQL BIT data type of necessity becomes the [bool] data type in .NET (and therefore in PowerShell), the closest logical equivalent.

  • Your own answer shows a CSV-based solution that transforms False and True string values to 0 and 1 after the fact, using string replacements. However:

    • It can yield false positives.
    • It is limited to use case where a text-based format is the desired output format.

The following sample code demonstrates how to generically transform the output objects (data rows, System.Data.DataRow instances) from an Invoke-SQLCmd call by replacing their [bool] property (column) values with their [byte] equivalents, i.e. by transforming $true to 1 and $false to 0 (if you know the columns of interest ahead of time and are willing to hard-code them, the solution would be simpler):

# Create a sample DataTable as a stand-in for Invoke-SqlCmd output,
# with 2 columns:
#  * Name (type [string])
#  * BitAsBoolean (type [bool])
$dt = [System.Data.DataTable]::new('sample')
$dt.Columns.AddRange([System.Data.DataColumn[]] ( @{ ColumnName = 'Name'; DataType = [string] }, @{ ColumnName = 'BitAsBoolean'; DataType = [bool] } ))
@{ Name = 'John'; BitAsBoolean = $true }, @{ Name = 'Susan'; BitAsBoolean = $false } | 
  ForEach-Object { 
    $dt.Rows.Add(($dr = $dt.NewRow()))
    foreach ($entry in $_.GetEnumerator()) { $dr[$entry.Key] = $entry.Value }
  }

# Process output from a (simulated) Invoke-SqlCmd call and transform
# each data row by converting [bool] column values to [byte] ones.
$propNames = $null
$dt.Rows | 
  ForEach-Object { 
    $row = $_
    if ($null -eq $propNames) {
      $columns = $row.Table.Columns
      # Get all property (column) names.
      $propNames = $columns.ColumnName
      # Determine the names of those properties that are of type Boolean.
      $boolPropNames = $columns.Where({ $_.DataType -eq [bool] }).ColumnName
    }
    # Create a copy of the input row, so that we can modify it.
    $rowCopy = $_ | Select-Object $propNames
    # Replace the [bool] values with their [byte] equivalents, 
    # i.e. transform $true to 1 and $false to 0.
    $boolPropNames.ForEach({ $rowCopy.$_ = [byte] $rowCopy.$_ })
    $rowCopy # Output the modified row copy.
  }

The OO (object-oriented) nature of the above operation gives you a choice of what output format you'd like to generate from it; in your case, where CSV is desired, simply append something like the following to the pipeline above:
| Export-Csv -NoTypeInformation -Encoding utf8 output.csv

Outputting $dt.Rows, i.e. the results of the simulated query, yields:

Name  BitAsBoolean
----  ------------
John          True
Susan        False

Running the transformation code yields:

Name  BitAsBoolean
----  ------------
John             1
Susan            0

Note:

  • For a more sophisticated function wrapper for the above functionality,
    Convert-BitColumsToInt, which outputs DataRow instances rather than [pscustomobject] instances, see the bottom section of this answer.

Upvotes: 2

Rami Aboulissane
Rami Aboulissane

Reputation: 41

In the end I generated the csv file by piping the invoke-sqlcmd results that are in a variable using export-csv then i used the below two lines just after the exporting.

(Get-Content -path %pathtoCsv%) -replace 'False', '0' | set-content "%pathtocsv%"

(Get-Content -path %pathtoCsv%) -replace 'True', '1' | set-content "%pathtocsv%"

Upvotes: 0

sirtao
sirtao

Reputation: 2880

There is no bit type in Powershell.
Thus the result gets converted in its closest equivalent: bool.

...which is just semantic bit anyway.

Details would depend on your specific script, but you can simply use .ToInt16($null) to convert $trueto 1 and $false to 0

Upvotes: 2

Related Questions