locked
Powershell Object-comparison for csv files doesn't work with more than 1 column RRS feed

  • Question

  • I'm sitting at such a simple piece of code last couple of hours, and literally don't have any idea what is wrong. Program has to compare 2 columns with different header names in 2 csv files, and write the differences to a txt file. Here is my code:

    Write-Host "Script output will have unique items from file 1"
    $FirstPath = read-host -prompt "Input file location of first .csv file"
    $SecondPath = read-host -prompt "Input file location of second .csv file"
    
    Write-Host "Importing CSV files..."
    $FirstFile = Import-Csv $FirstPath
    $SecondFile = Import-Csv $SecondPath 
    
    Write-Host "Comparing files..."
    $SecondFile | Add-Member -MemberType AliasProperty -Name Url -Value Url2
    Compare-Object -ReferenceObject $FirstFile -DifferenceObject $SecondFile -Property Url -PassThru | Where-Object{$_.SideIndicator -eq "<="}  | Out-file .\result.txt
    
    Write-Host "Done, press any key to continue..."
    $x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

    2 example csv files that I'm comparing:

    Url;Fail 
    test1;failvalue1 
    test2;failvalue2
    
    Url2;Fail2
    test1;failvalue1
    test3;failvalue2

    Output should be only "test1", since I'm comparing Url with Url2 through alias. If i delete Fail and Fail2 columns, leaving only 1 column in each file, everything works fine.

    What i figured out so far:

    Everything works if there is only 1 column in each file. Alias for 2 different header names works, searching works, write to output file also.

    The problem is when I'm trying to search csv files with more than 1 column. If csv file with alias has more than 2 columns, it gives this error:

    The member 'Url2' is not present

    If the first file - ReferenceObject has more than 2 columns, output is a whole first file. It's probably something foundamental and stupid, but I'm new to powershell and can't make it work.


    Wednesday, August 16, 2017 4:53 PM

All replies

  • Hi,
    Based on your situation, you can try to Add-Member for every columns with different alias. For you reference:
    $FirstFile = Import-Csv -Path "File Location of First .csv File"
    $SecondFile = Import-Csv -Path "File Location of Second .csv File"
    $SecondFile | Add-Member -MemberType AliasProperty -Name Url -Value Url2
    $SecondFile | Add-Member -MemberType AliasProperty -Name Fail -Value Fail2
    Compare-Object -ReferenceObject $FirstFile -DifferenceObject $SecondFile -Property Url,Fail | Where-Object{$_.SideIndicator -eq "<="} | Out-File -Path "File Location of Compare .txt File"

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, August 17, 2017 3:08 AM
  • $firstfile = Import-Csv .\file1.csv -Delimiter ';'
    $Secondfile = Import-Csv .\file2.csv -Delimiter ';'
    
    #Equal
    Compare-Object -ReferenceObject $firstfile.url -DifferenceObject $Secondfile.url2 -IncludeEqual | where {$_.SideIndicator -eq '=='}
    
    InputObject SideIndicator
    ----------- -------------
    test1       ==         
    
    
    #Difference
    Compare-Object -ReferenceObject $firstfile.url -DifferenceObject $Secondfile.url2 
    
    InputObject SideIndicator
    ----------- -------------
    test3       =>           
    test2       <=           
    

    Thursday, August 17, 2017 6:25 AM
  • Hi,

    I am checking how the issue is going, if you still have any questions, please feel free to contact us.

    And if the replies as above are helpful, we would appreciate you to mark them as answers, and if you resolve it using your own solution, please share your experience and solution here. It will be greatly helpful to others who have the same question.

    Appreciate for your feedback.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, August 22, 2017 1:57 AM
  • Hi,

    Was your issue resolved? 

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.
    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.
    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, August 25, 2017 9:13 AM