Author Posts

February 5, 2016 at 12:43 am

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

February 5, 2016 at 11:03 am

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.

February 7, 2016 at 5:14 pm

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

February 8, 2016 at 1:15 pm

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