Copy entire row from Excel if condition false? in PS

Tagged: ,

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Matt Bloomfield Matt Bloomfield 7 months, 3 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #34904
    Profile photo of Er Reddy
    Er Reddy
    Participant

    Hi Team,

    I have a Excel file with as below

    —– A ——- B ————— C

    1 HY HY-L-0098 LAP

    2 BL BL-W-987 DKP

    3 GH GH-L-0ZGT LAP

    4 DR DR-L-Z788 LAP

    1. I need a script for read A1 and check first two letters from B1 equal (HY=HY) or not, If condition false copy entire row to different Excel file (as a output).

    2. Read C1 and check if C1=LAP {

    Dim Val='L'

    read 4th character of B1 and check If Val = 4th character of B1, If condition false copy entire row to different Excel file – i mean append to next next (as a output).

    }

    Else If C1=DKP {

    Dim Val='W'

    read 4th character of B1 and check If Val = 4th character of B1, If condition false copy entire row to different Excel file – i mean append to next next (as a output).

    }

    Thanks in advance

    Reddy

    #34928
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    You've pretty much written the script in pseudocode. It's not a massive step to turn that into some functioning PowerShell.

    Rather than working with Excel spreadsheets, you'll find it easier to work with CSV files using PowerShell's Import-CSV cmdlet. You can then access each row in the CSV file to check against your criteria.
    e.g.

    #Import the CSV file with all the data.
    $csv = Import-CSV C:\mycsv.csv
    
    #Process the CSV file one row at a time
    foreach ($row in $csv) {
    
    #Each column is accessed using the column header, so in your case a, b and c.
    #We can use the -not operator to specify 'doesn't match'.  The test here is:
    #If the data in column a of the current row is not the same as the first two letters
    #of the data in column b of the current row...
        if (-not($row.a -eq $row.b.substring(0,2))) {
    
    #... Export that row to a new CSV file. 
            $row | Export-CSV C:\mynewcsv.csv -Append -NoTypeInformation
        }
    }
    
    

    The rest of your requirements will require very similar code to that above. Let us know how you get on.

    #34976
    Profile photo of Er Reddy
    Er Reddy
    Participant

    Thanks Matt 🙂

    Code working great.

    I have one more requirement as below, Could you please help me on this.

    ———D —————- E ————– F

    1) 2/21/2014— 2/20/2016– 5/23/2015
    2) Empty cell — 3/13/2015 – 6/8/2014

    1. i want to check D1 date less than or -eq <= E1 date (Note: if both D1 and E1 have proper date only we need to check this condition. like If one/two cell is empty or 0 no need to perform this check) 2. I want to check F1 date less than or -eq <= current date (Note: If F1 have proper date only we need to check this condition. like If F1 cell is empty or 0 no need to perform this check)Thanks in Advance Reddy

    #35008
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    The first thing to do is check if the column has a sensible date format i.e. dd/mm/yyyy. I used a regular expression to check for this.

    If a valid date format is found, it's turned into a standard ISO date yyyy-MM-dd which makes it easy to compare dates.

    A similar check and conversion is performed for column F but its value is compared to the current date which we can get with Get-Date.

    Here's some code. I've made it pretty verbose so that you can see what's going on and hopefully you can use these ideas in your script.

    # Sample data saved as date.csv
    
    #d,e,f
    #03/02/2015,03/11/2015,02/08/2016
    #0,04/09/2015,
    #04/11/2014,,01/01/2016
    #05/12/2016,07/12/2014,05/12/2015
    
    $csv = Import-Csv F:\__Temp\date.csv
    
    $rowCount = 0
    
    foreach ($row in $csv) {
    
        $rowCount++
    
        # check if the data in the column looks like a date dd/mm/yyyy.
        
        if  (($row.d -match "\d{2}/\d{2}/\d{4}") -and ($row.e -match "\d{2}/\d{2}/\d{4}")) {
    
            Write-Output "Processing row $rowCount."
    
            # convert the date to ISO standard date format for easy comparison.
    
            $dDate = Get-Date $row.d -format "yyyy-MM-dd"
            $eDate = Get-Date $row.e -format "yyyy-MM-dd"
    
            #compare the dates and write out the result.
            
            if ($dDate -le $eDate) {
        
                Write-Output "$dDate (D) is earlier than or equal to $eDate (E)."
    
            } #end if
    
            else {
    
                Write-Output "$dDate (D) is later than $eDate (E)."
    
            }  #end else
    
       } #end if
    
       else {
    
            Write-Output "Skipping D/E comparison on row $rowCount because one or both columns do not contain a date."
    
       } #end else
    
       
        if ($row.f -match "\d{2}/\d{2}/\d{4}") {
    
            $fDate = Get-Date $row.f -format "yyyy-MM-dd"        
            $currentDate = Get-Date -Format "yyyy-MM-dd"
            
            if ($fDate -le $currentDate) {
    
                Write-Output "$fDate is earlier than or equal to today's date."
    
            } #end if
    
            else {
    
                Write-Output "$fDate is in the the future."
    
            } #end else
        
        } #end if
    
        else {
    
            Write-Output "Skipped column F for this row because it doesn't appear to be a date."
    
        } # end else 
    
    } #end foreach
    
    
Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.