Compare CSV files with powershell

aravind anche
aravind anche used Ask the Experts™
Hello Experts,

I have a csv file lets say X with one column called product name. it has 70 rows. that CSV is standard. I created another script it does lot of stuff and generates csv file lets say Y. Y has several headers, but one of them is Productname, and status how can I match the 2 columns when ever any item from X falls in Y and status is failed, print out the whole row.
Is that possible?



Thanks in advance.

Anche
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018
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.
Most Valuable Expert 2018
Distinguished Expert 2018
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'

Open in new window

Author

Commented:
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.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

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

Author

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

Author

Commented:
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
Most Valuable Expert 2018
Distinguished Expert 2018
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'

Open in new window

Author

Commented:
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

Author

Commented:
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?

Author

Commented:
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) 

Open in new window


How can I add body with package type failed with status message?
Most Valuable Expert 2018
Distinguished Expert 2018
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

Open in new window

Author

Commented:
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
Most Valuable Expert 2018
Distinguished Expert 2018
$Result = Import-Csv -Path 'c:\z.csv' 
$body = "Some packages failed to install:`r`n" + (($Result | ForEach-Object {"[$($_.PackageType)]:`t$($_.StatusMsg)"}) -join "`r`n")
$param = @{
	Smtpserver =  ""
	From =        ""
	To =          ""
	CC =          ""
	Subject =     "Package Failure Report"
	Body =        $body
#	Attachments = ".\z.csv"
}
Send-MailMessage @param

Open in new window


Edit: Fixed issue with the mail body creation.

Author

Commented:
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
Most Valuable Expert 2018
Distinguished Expert 2018
My bad, sorry, too much copy and paste; edited the scripr above, just download again.

Author

Commented:
this time received email with body ":"
I am sorry, I aam asking so many
Most Valuable Expert 2018
Distinguished Expert 2018
No, my bad; can't test it properly at the moment.
Edited again, should be fixed now.

Author

Commented:
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?
Most Valuable Expert 2018
Distinguished Expert 2018
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

Open in new window

Author

Commented:
thanks alot

Author

Commented:
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
Most Valuable Expert 2018
Distinguished Expert 2018
Just check if $Result is empty and act accordingly.
If ($Result) {
...
} Else {
...
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial