Search code examples
sqlpowershellnulloledb

WHERE clause not working with blank values

In the code below I try to query an Excel file for a particular field "Username" Where there are blanks (= ''). But nothing gets returned to the data adapter. The WHERE clause will return data for example if I specify a NOT LIKE 'thistext%', but again omits blanks. Is there some way to better code the clause?

I have read there is an issue if fields are blank and need to be converted to DB NULL (or populated with a value) unsure how to do it. 

$connection.ConnectionString = $connectstring
$connection.Open()
$objOleDbCommand.Connection = $connection
$objOleDbCommand.CommandText = "SELECT * FROM [$strSheetName] WHERE [Username] = ''"

$objOleDbAdapter.SelectCommand = $objOleDbCommand

$objOleDbAdapter.Fill($objDataTable)

$objDataTable | Export-Csv "C:\output\MyData_$dateandtime.csv" -NoTypeInformation

$connection.Close()
Solution

  • Try this instead to cover blanks and nulls.

    Select * from [$strSheetName] where   [Username] = '' OR [USERNAME] IS NULL

    Note that this itself is not a powershell issue technically, you might encounter this same issue with other spreadsheets and databases.