Unable to get Not Equal with SQL / Excel Call

Welcome Forums General PowerShell Q&A Unable to get Not Equal with SQL / Excel Call

Viewing 2 reply threads
  • Author
    Posts
    • #163683
      Participant
      Topics: 2
      Replies: 3
      Points: 38
      Rank: Member

      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()

       

    • #163695
      Senior Moderator
      Topics: 9
      Replies: 1236
      Points: 4,443
      Helping Hand
      Rank: Community Hero

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

    • #163698
      Participant
      Topics: 2
      Replies: 3
      Points: 38
      Rank: Member

      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.

Viewing 2 reply threads
  • The topic ‘Unable to get Not Equal with SQL / Excel Call’ is closed to new replies.