Unable to get Not Equal with SQL / Excel Call

PowerShell Help
1

I’m currently attempting to update an very large excel spreedsheet using OLEDB provider. I have to update blank field within one column. The Excel file is 55K+ lines, and probably more than half of them are blank. I’m able to Select * from file and get all the listed Items in the column including the blank. I want to update the blank cells, so I’m using the Update SQL command. The issue is the <> operand is not selecting the blank cells. If I set the operand to = it will replace the cells that are equaled. It doesn’t take ‘’ or ’ ’ as the same as the blank cell. The below code return 0 rows effected, even though there are 20+ rows that don’t equal Operations in the same data. Any Help would be great.

$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source=`"$ExcelFile`""
$strExtend = "Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

$strQuery = "Update [$strSheetName] set [Collection Name] = 'N\A' WHERE [Collection Name] <> 'Operations' "
$objConn = New-Object System.Data.OleDb.OleDbConnection
$objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$strFileName`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$sqlCommand.ExecuteNonQuery()
$objConn.Close()

 

2

From your question, I don’t see any PowerShell specific issue, is this something that you can try executing via ssms ?

3

No I don’t have access to SSMS, and it’s not an actual DB. It’s and Excel file. I think I figured it out. I used the IS NULL value and that work. Funny thing is that doesn’t work when doing a SELECT for the SQL statement within Powershell.

I just didn’t know if powershell handled the DB call differently.