Compare input.csv and output.csv and describes the result in a coumn

Hi,

I have a input.csv and output.csv files. I am trying to write a powershell script which will search in output.csv based on input.csv and write a column in input.csv which describes matching or not.

example:

input.csv looks like the below:

Profilervariable      URL
ABC                          http://abc.com                   |
DEF                          http://def.com                    |
GHJ                          http://ghj.com                    |
YUI                          http://yui.com                    |
MNB                  http://mnb.com                 |

Output.csv file looks like below

Profilervariable      URL
ABC                          http://abc.com
DEF                          http://def.com
GHJ                          http://ghj.com
YUI                          http://abc.com
YTU                          http://ytu.com
RES                          http://r.com

I need to generate a file which compares and describes the result as shown below;
Profilervariable      URL                               Result
ABC                          http://abc.com                 Matched
DEF                          http://def.com                 Not Matched
GHJ                          http://ghj.com                 Matched
YUI                          http://yui.com                 Matched
MNB                  http://mnb.com              Not Present
karkou12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nadav SolomonCommented:
The following will also tell you where it's not present (in input or output file)


$input = "C:\Users\NAD\Desktop\Scripts\input.csv"
$output = "C:\Users\NAD\Desktop\Scripts\output.csv"
$results = "C:\Users\NAD\Desktop\Scripts\results.csv"
##################################################################
Set-Content -Path $results -value "Profilervariable,URL,Result"

$impData = @{}
import-csv $input | %{
    $impData.add($_.Profilervariable,$_.URL)
}
import-csv $output | %{
    if($impData.ContainsKey($_.Profilervariable))
    {
        if($_.URL -eq $impData[$_.Profilervariable])
        {
            Add-Content -Path $results -value "$($_.Profilervariable),$($_.URL),Matched"
        }
        else
        {
            Add-Content -Path $results -value "$($_.Profilervariable),$($_.URL),Not Matched"
        }
        $impData.Remove($_.Profilervariable)
    }
    else
    {
        Add-Content -Path $results -value "$($_.Profilervariable),$($_.URL),Not Present (IN INPUT)"
    }
}
$impData.GetEnumerator() | %{
    Add-Content -Path $results -value "$($_.key),$($_.value),Not Present (IN OUTPUT)"
}

Open in new window


Enjoy.
Jeremy WeisingerSenior Network Consultant / EngineerCommented:
How about the Compare-Object cmdlet?
Also, what do you want to compare? The Profilevariable or the URL?

This compares the Profilevariable but can easily be changed to URL:
$inputcsv = Import-Csv D:\test\input.csv
$outputcsv = Import-Csv D:\test\output.csv
$resultscsvpath = 'D:\test\Results.csv'

$results = compare $inputcsv $outputcsv -Property Profilervariable -PassThru | Select Profilervariable,URL,@{name='Result';E={If($_.Sideindicator -eq '=>'){"Not Present"}else{"Not Matched"}}} 
$results
$results | Export-Csv $resultscsvpath -NoTypeInformation

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karkou12Author Commented:
This code works good. Thank you very much.

But I observed  a particular Variable, in both output & input file is like below

ProfilerVariable           URL
ABC                               https://abc.com:7444
--------------------------------------------------------
But in the result file it is showing as below and putting it as Not Matched, do not understand how 44 is missing.

ProfilerVariable           URL
ABC                               https://abc.com:74

Also,
1)  How can we make this case in-sensitive.
2)  Instead of updating a new file with all the data of output, can we make use of input file only and just add a column namely Result where we put a Matched/Not Matched value according to the result of comparison.
----------------------------------------------------------------------------------------------------------------------------

I also tried something like the below. But something is missing here, I am not getting proper result.

$Res_user = Import-Csv -Path "C:\Temp\out.csv"
$Res_op = Import-Csv -Path "C:\Temp\Input.csv"
$UserOutput = @()

    ForEach ($Entry in $Res_op)
    {

    
        $userMatch = $Res_user | where {$_.ProfilerVariable -eq $Entry.ProfilerVariable}
        If($userMatch)
        
        {
        
#           if($Entry.URL -eq $Res_user.URL)
           if(($Entry.URL -eq $Res_user.URL) -eq 0)
           
           {
             Write-Host "Matched"
            $obj = new-object PSObject
            $obj | add-member -membertype NoteProperty -name "ProfilerVariable" -value $Entry.ProfilerVariable
            $obj | add-member -membertype NoteProperty -name "URL" -value $Entry.URL
            $obj | add-member -membertype NoteProperty -name "Result" -value "Matched"
           }               
                 elseif(!($Entry.URL -eq $Res_user.URL) -eq 0)
          
            {
           Write-Host "Not Matched"
            $obj = new-object PSObject
            $obj | add-member -membertype NoteProperty -name "ProfilerVariable" -value $Entry.ProfilerVariable
            $obj | add-member -membertype NoteProperty -name "URL" -value $Entry.URL
            $obj | add-member -membertype NoteProperty -name "Result" -value "Not Matched"       
            
            
            }
           
            
            $UserOutput +=$obj
        
        }
            
    }
    $CsvFinalOutput = "C:\Temp\Finaloutput.csv"
    $UserOutput | export-csv $CsvFinalOutput -noType 

Open in new window

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

karkou12Author Commented:
My above comment is for the code given by Nadav Solomon
Nadav SolomonCommented:
1) .toUpper() (or .toLower()) will solve the case sensitive. (check the code)
2) the only way to "update" the original file is to overwrite it after everything is in the memory in hope it's not locked for reading (depending on powershell's bugs or what ever, I would write the result to a defferent file anyway. (though it is possible but bare in mind you are not really updating the file but over writing it).
regarding 7444 and 74 i didnt understand what you mean.

$input = "C:\Users\NAD\Desktop\Scripts\input.csv"
$output = "C:\Users\NAD\Desktop\Scripts\output.csv"
$results = "C:\Users\NAD\Desktop\Scripts\results.csv"
##################################################################
Set-Content -Path $results -value "Profilervariable,URL,Result"

$impData = @{}
import-csv $input | %{
    $impData.add(($_.Profilervariable).toUpper(),$_.URL)
}
import-csv $output | %{
    if($impData.ContainsKey(($_.Profilervariable).toUpper()))
    {
        if($_.URL -eq $impData[$_.Profilervariable])
        {
            Add-Content -Path $results -value "$($_.Profilervariable),$($_.URL),Matched"
        }
        else
        {
            Add-Content -Path $results -value "$($_.Profilervariable),$($_.URL),Not Matched"
        }
        $impData.Remove($_.Profilervariable)
    }
    else
    {
        Add-Content -Path $results -value "$($_.Profilervariable),$($_.URL),Not Present (IN INPUT)"
    }
}
$impData.GetEnumerator() | %{
    Add-Content -Path $results -value "$($_.key),$($_.value),Not Present (IN OUTPUT)"
}

Open in new window

footechCommented:
Your example result (from the original question) doesn't match up with your example input and output .CSV data.  Here's an expected result.
Profilervariable                        URL                                    Result                                
----------------                        ---                                    ------                                
ABC                                     http://abc.com                         Matched                               
DEF                                     http://def.com                         Matched                               
GHJ                                     http://ghj.com                         Matched                               
YUI                                     http://abc.com                         Not matched                           
MNB                                     http://mnb.com                         Not present  

Open in new window

 

So, assuming it was just a mistake, and that you don't care if there's an entry in the output .CSV that doesn't have a match (as you've indicated in your last comment), this will work.
$Res_user = Import-Csv -Path "C:\Temp\out.csv"
$Res_op = Import-Csv -Path "C:\Temp\Input.csv"
Compare-Object $Res_op $Res_user -Property profilervariable,url -IncludeEqual | Group Profilervariable | ForEach `
{
    If ( $_.Count -gt 1 )
    {
        $result = "Not matched"
    }
    ElseIf ( $_.Group.SideIndicator -eq "==" )
    {
        $result = "Matched"
    }
    ElseIf ( $_.Group.SideIndicator -eq "<=" )
    {
        $result = "Not present"
    }
    If ( $result )
    {
        New-Object PsObject -Property ([ordered]@{
                            Profilervariable = @($_.Group.Profilervariable)[0]
                            URL = @($_.Group.URL)[0]
                            Result = $result
                            })
        Remove-Variable result
    }
}

Open in new window


As soon as you've verified that it works, you can append the following to the last line to write over the input .CSV file.
| Export-CSV "C:\Temp\Input.csv" -notype

Open in new window

karkou12Author Commented:
Thank You ALL EXPERTS.

Your Solutions did a great work for me.
Nadav SolomonCommented:
Glad I could help, thanks for the feedback.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.