type conversion - Powershell converting bit values to Boolean in result set - Stack Overflow

admin管理员组

文章数量:1287656

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)

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)

• powershell
• type-conversion
• boolean
• bit
• invoke-sqlcmd

Share Improve this question Follow edited Feb 24 at 20:32 mklement0 440k6868 gold badges702702 silver badges915915 bronze badges asked Feb 24 at 9:42 Rami AboulissaneRami Aboulissane 4111 gold badge11 silver badge55 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

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

• 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.

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%"

本文标签： type conversionPowershell converting bit values to Boolean in result setStack Overflow