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
-
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).
-
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
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.
Thanks Matt 
Code working great.
I have one more requirement as below, Could you please help me on this.
---------D ---------------- E -------------- F
- 2/21/2014— 2/20/2016-- 5/23/2015
- Empty cell – 3/13/2015 - 6/8/2014
- 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)
- 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
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