Avatar of aravind anche
aravind anche
Flag for United States of America asked on

Compare CSV files with powershell

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
Powershell

Avatar of undefined
Last Comment
oBdA

8/22/2022 - Mon
David Favor

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.
oBdA

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

aravind anche

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
aravind anche

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

ASKER
it ran without the error, it created the csv with no output
aravind anche

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
oBdA

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

aravind anche

ASKER
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
aravind anche

ASKER
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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
aravind anche

ASKER
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?
oBdA

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

aravind anche

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
oBdA

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
aravind anche

ASKER
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
oBdA

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

ASKER
this time received email with body ":"
I am sorry, I aam asking so many
Your help has saved me hundreds of hours of internet surfing.
fblack61
oBdA

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

ASKER
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?
oBdA

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
aravind anche

ASKER
thanks alot
aravind anche

ASKER
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
oBdA

Just check if $Result is empty and act accordingly.
If ($Result) {
...
} Else {
...
}

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23