Unable to get Not Equal with SQL / Excel Call

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

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
2 months, 2 weeks ago.

  • 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: 8
    Replies: 1041
    Points: 3,439
    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.

You must be logged in to reply to this topic.