The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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'
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.
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.
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
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:
From your question:
From the csv:
one column called product name
one of them is Productname, and status
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'
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
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 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
How can I add body with package type failed with status message?
$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)
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?
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
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
So email body contains package type and status mesg
$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
Edit: Fixed issue with the mail body creation.
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
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
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?
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial