Solved

PowerShell Loop, Compare and Output

Posted on 2011-02-28
8
941 Views
Last Modified: 2012-08-14
Hi all,

I'm just starting with Powershell and I need some help.  I have two files that I need to compare against each other.  I need powershell to compare a similar column in each csv and if information from file1 exists in file2, pull the information from file2 and output.  There is a column with the same name in each file.

I would appreciate a jump start on this.  I'm not having much luck searching for this since I don't really know what to search for.

Thanks.
0
Comment
Question by:jhsaun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 27

Expert Comment

by:KenMcF
ID: 35003704
Can you post an example of the CSV files, and what you want the output to be?
If the one column matches in both files will the other columns match or will there be different data?
Do you want just the data in the column in the output or the whole line, and do you want the to just output to the console?
0
 

Author Comment

by:jhsaun
ID: 35003732
Here's what the headers look like in the CSV files:

CSV FILE1:
MacAddress, IPAddress, Date, Time


CSV FILE2:
SwitchIP, SwitchName, MacAddress, PortNumber

I would like the output to contain all of the information in CSV FILE2 if the MacAddress matches one of the entries in CSV FILE1.  I would then like it to output to HTML.

For example, I might have only one device listed in FILE1, but 4500 in FILE2.  I would only want to see the information from FILE2 for just the one device in FILE1.
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 35003809
You could try something like this


$1 = import-csv C:\temp\1.csv
$2 = import-csv C:\temp\2.csv
$arr = @()
$1 | %{$arr += $_.IPAddress}
$2 | %{
If ($arr -contains $_.SwitchIP){
$_ | Select *
}} | ConvertTo-Html | out-file c:\temp\out.html

Open in new window

0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:jhsaun
ID: 35011035
Ok, that works.  Thanks.

I have one other small problem and then I think I have it.  The second CSV file has spaces between the quote and the data.  This is how it is generated.

Example:  "  00-4a-5f-c4-55-a9   "

Is there anyway that I can tell it to ignore the spaces and only focus on the data (MAC)?  If I manually take out the spaces it is fine, but with them in it my output is blank.

Thanks
0
 
LVL 27

Expert Comment

by:KenMcF
ID: 35011264
Are you comapring the MAC addresses in each CSV file?
0
 

Author Comment

by:jhsaun
ID: 35011295
That is correct.
0
 
LVL 27

Accepted Solution

by:
KenMcF earned 500 total points
ID: 35011345
You can try this
$1 = import-csv C:\temp\1.csv
$2 = import-csv C:\temp\2.csv
$arr = @()
$1 | %{$arr += $_.MacAddress}
$2 | %{
$MAC = $_.MacAddress -replace " ",""
If ($arr -contains $MAC){
$_ | Select *
}} | ConvertTo-Html | out-file c:\temp\out.html

Open in new window

0
 

Author Comment

by:jhsaun
ID: 35012189
That got it.  Thanks much.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Synchronize a new Active Directory domain with an existing Office 365 tenant
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question