Solved

PowerShell Loop, Compare and Output

Posted on 2011-02-28
8
939 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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.
A procedure for exporting installed hotfix details of remote computers using powershell
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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 …

803 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