Compare CSV files with powershell

Wow... in Powershell, eh...

You're looking at writing a fair bit of code which already exists.

If I were doing this, I'd take one of these approaches.

1) Write a csv diff PERL script using https://metacpan.org/pod/Text::CSV_XS, which allowed an option to specific column name(s) to compare.

2) Or better, use https://github.com/fiisch/csvdiff or some similar existing csv diff tool.

That should do it, if I understood you correctly:

$include = Import-Csv -Path '.\x.csv' | Select-Object -ExpandProperty 'Product Name'
Import-Csv -Path '.\y.csv' |
	Where-Object {($_.Status -eq 'failed') -and ($include -contains $_.ProductName)} |
	Export-Csv -NoTypeInformation -Path '.\z.csv'

Select allOpen in new window

Thanks David,
Both links confused me.
So there is nothing we can do in the script to compare 2 CSV and if there is match print that line?

When I started the script thought it might me easy and now its getting complicated.

thanks Obda, testing it now, but the resullt i dont want to send email as of now.

it ran without the error, it created the csv with no output

Here is the csv files
Name: Reference
PackageName
Dell, Inc. Dell Latitude 5300 - Win10x64 A03 English
Dell, Inc. Dell Latitude 5480 - Win10x64 A07 English
Dell, Inc. Dell Latitude 5490 - Win10x64 A06 English
Dell, Inc. Dell Latitude 5500 - Win10x64 A03 English
Dell, Inc. Dell Latitude 5590 - Win10x64 A07 English
Dell, Inc. Dell Latitude 5591 - Win10x64 A07 English
Dell, Inc. Dell Latitude 7212 - Win10x64 A03 English
Dell, Inc. Dell Latitude E6430s - Win10x64 A01 English
Dell, Inc. Dell Latitude E7240 - Win10x64 A04 English
Dell, Inc. Dell Latitude E7440 - Win10x64 A04 English
Dell, Inc. Dell Latitude E7450 - Win10x64 A07 English
Dell, Inc. Dell Optiplex 5050 - Win10x64 A07 English
Dell, Inc. Dell Optiplex 5060 - Win10x64 A00 English
Dell, Inc. Dell Optiplex 7010 - Win10x64 A01 English
Dell, Inc. Dell Optiplex 7060 - Win10x64 A01 English
Dell, Inc. Dell Optiplex 7070 - Win10x64 A00 English
Dell, Inc. Dell Optiplex 9010 - Win10x64 A02 English
Dell, Inc. Dell Optiplex 9020 - Win10x64 A05 English
Dell, Inc. Dell Precision 3420 - Win10x64 A11 English
Dell, Inc. Dell Precision 3430 - Win10x64 A04 English


Second file
PackageName      PackageType      DistributionState            
Windows 7 x64 - Latitude E7240      Driver Package      Failed            
Windows 7 x64 - Latitude 14 Rugged (5404)      Driver Package      Failed            
Microsoft OneDrive 17.3.6390.0509      Application Content Package      In Progress            
Windows 7 x64 - XPS 13 9343      Driver Package      Failed            
Windows 7 x64 - Precision R7910      Driver Package      Failed            
Windows 7 x64 - Precision M4600      Driver Package      Failed            
Windows 7 x64 - Optiplex 7020      Driver Package      Failed            
Windows 7 x64 - Optiplex 9020m      Driver Package      Failed            
Windows 7 x64 - Optiplex 9020      Driver Package      Failed            
Windows 7 x64 - Precision T7910      Driver Package      Failed            
Dell, Inc. Dell Precision 3430 - Win10x64 A04 English      Driver Package      Failed            


There is one match in 2 files and distributionstate is failed.
So when script run if distribution state is failed for a package in reference CSV print the whole row in 2nd CSV

Well, then that's where the problem is. None of the column names you posted was correct.
From your question:

one column called product name
one of them is Productname, and status

From the csv:

Reference: PackageName
Second file: PackageName, DistributionState

$include = Import-Csv -Path '.\x.csv' | Select-Object -ExpandProperty 'PackageName'
Import-Csv -Path '.\y.csv' |
	Where-Object {($_.DistributionState -eq 'failed') -and ($include -contains $_.PackageName)} |
	Export-Csv -NoTypeInformation -Path '.\z.csv'

Select allOpen in new window

thanks Obda, it exported the results. Also what if i want to exclude particular package type?
currently all are driver packages, if thera re any standard package I dont want them. I mean i am not worried about those

Sorry that my dumb question, I got it.

I want this to send this as alert, how can I do that? not as CSV but packagename in subject and package type and statusmessage in body.
is that possible?

I came up below its sending email but not as expected

$Result = import-csv -Path 'c:\z.csv' 
###########Define Variables######## 
 
 
$fromaddress = 
$toaddress =  
#$CCaddress1 = 
#$CCaddress2 =
#$CCaddress3 =
$Subject = "Package Failed"
$body = $Result.packagetype + $result.statusmsg
$smtpserver = 
 
#################################### 
 
$message = new-object System.Net.Mail.MailMessage 
$message.From = $fromaddress 
$message.To.Add($toaddress) 
#$message.CC.Add($CCaddress1) 
#$message.CC.Add($CCaddress2) 
#$message.CC.Add($CCaddress3)
$message.IsBodyHtml = $True 
$message.Subject = $Subject 
$message.body = $body 
$smtp = new-object Net.Mail.SmtpClient($smtpserver) 
$smtp.Send($message) 

Select allOpen in new window

How can I add body with package type failed with status message?

First of all: use Send-MailMessage.
And why don't you just attach the csv?

$param = @{
	Smtpserver =  ""
	From =        ""
	To =          ""
	CC =          ""
	Subject =     "Package Failure Report"
	Body =        "Some packages failed to install, see attachment"
	Attachments = ".\z.csv"
}
Send-MailMessage @param

Select allOpen in new window

Thanks, but we have this requirement to send email. and also these will be sent to different groups.
So email body contains package type and status mesg

Thanks OBda

The c:\z.csv file has 2 rows with 2 diffrent package types, and status msg
PackageName      PackageType      DistributionState
Windows 7 x64 - Precision T7910      Driver Package      Failed            
Dell, Inc. Dell Precision 3430 - Win10x64 A04 English      Driver Application      Failed        

email received is    
Driver Package Driver Application : failed failed

My bad, sorry, too much copy and paste; edited the scripr above, just download again.

this time received email with body ":"
I am sorry, I aam asking so many

No, my bad; can't test it properly at the moment.
Edited again, should be fixed now.

thanks alot oBdA, perfect email.
Sorry one question, what if we need to send in 2 different emails (just for my learning purpose), one package in one email and other in another email and also multiple email address?

You filter the results twice, set the Body in the hashtable twice, and send two emails:

$Result = Import-Csv -Path 'c:\z.csv'
$param = @{
	Smtpserver =  ""
	From =        ""
	To =          ""
	CC =          ""
	Subject =     "Package Failure Report"
}
$filtered = $Result | Where-Object {...}
$param['Body'] = "Some packages failed to install:`r`n" + (($filtered | ForEach-Object {"[$($_.PackageType)]:`t$($_.StatusMsg)"}) -join "`r`n")
Send-MailMessage @param
$filtered = $Result | Where-Object {...}
$param['Body'] = "Some packages failed to install:`r`n" + (($filtered | ForEach-Object {"[$($_.PackageType)]:`t$($_.StatusMsg)"}) -join "`r`n")
Send-MailMessage @param

Select allOpen in new window

thanks alot

Hello ObDa,

What if there are no failed one then is there anything we can do that changes the body of email say "no failed ones" or similar like that

Just check if $Result is empty and act accordingly.

If ($Result) {
...
} Else {
...
}

Select allOpen in new window