Solved

PowerShell Loop, Compare and Output

Posted on 2011-02-28
8
940 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
  • 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Set OWA language and time zone in Exchange for individuals, all users or per database.
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 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