none
Compare Two csv files to get output in 3rd csv file RRS feed

  • Question

  • Hi folks,

    I need a script which could compare two csv and generate csv file for difference.

    eg.

    URL,Date,Count----- csv 1

    URL,Date,Count------ CSV 2

    in both the csv URL would be same but the date and count values are different. I need to create a csv which will use csv1 as reference and compare with csv2 and then generate csv with

    URL, Date, Count, Date2, Count2--- CSV 3

    Any help is appreciated

    Wednesday, January 21, 2015 9:20 AM

Answers

  • Hi Sam

    Thanks for the reply but this is not the output which i needed. I have created the script using if else loop.

    Anyways Thanks for your help.

    Regards

    Amol 

    • Marked as answer by Amol Pawar Friday, January 23, 2015 10:57 AM
    Wednesday, January 21, 2015 2:11 PM

All replies

  • I need a script which could compare two csv and generate csv file for difference.

    Let's have a look at the script you have written so far.
    Wednesday, January 21, 2015 9:46 AM
  • Hi Frederik,

    $ff=Import-Csv"D:\Amol\Compare 2 CSV\CountofCEWP_STG.csv"

    $sf = Import-Csv"D:\Amol\Compare 2 CSV\CountofCEWP_PRD.csv"

    compare $ff $sf -property($ff.PageUrl -eq$sf.PageURL)

    I am able to compare the URL but want the PageURL and count column of both the csv as output

     




    • Edited by Amol Pawar Wednesday, January 21, 2015 9:53 AM
    Wednesday, January 21, 2015 9:49 AM
  • $ff = Import-Csv 'D:\Amol\Compare 2 CSV\CountofCEWP_STG.csv'
    $sf = Import-Csv 'D:\Amol\Compare 2 CSV\CountofCEWP_PRD.csv'
    
    $PageUrlsf = (compare $ff $sf -Property PageUrl | Where { $_.SideIndicator -eq '=>' }).PageUrl
    if ($PageUrlsf) {"'$($PageUrlsf.Count)' PageUrl's unique to '$sf'"; $PageUrlsf}
    
    $PageUrlff = (compare $ff $sf -Property PageUrl | Where { $_.SideIndicator -eq '<=' }).PageUrl
    if ($PageUrlff) {"'$($PageUrlff.Count)' PageUrl's unique to '$ff'"; $PageUrlff}


    Sam Boutros, Senior Consultant, Software Logic, KOP, PA http://superwidgets.wordpress.com (Please take a moment to Vote as Helpful and/or Mark as Answer, where applicable) _________________________________________________________________________________ Powershell: Learn it before it's an emergency http://technet.microsoft.com/en-us/scriptcenter/powershell.aspx http://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx

    Wednesday, January 21, 2015 11:44 AM
  • Hi Sam,

    Thanks for the script but the script only compares does not provide the count column from both the csv files.

    The URL from csv1 should search for equal url in CSV2 and then the third csv should be

    URL which is common, Date, Count, Count2 from csv2 , Date from csv2

    Regards,

    Amol

    Wednesday, January 21, 2015 11:55 AM
  • #file1.csv
    #URL,Date,Count
    #1,2,3
    #2,2,3
    #3,2,3

    #file2.csv
    #URL,Date,Count
    #3,2,3
    #2,2,4
    #1,2,3

    #file3.csv

    URL,Date,Count,Date2,Count2

    1,2,3,2,3

    2,2,3,2,4

    3,2,3,2,3

    Wednesday, January 21, 2015 12:11 PM
  • #Requires -Version 4
    
    # Objective: consolidate data from 2 CSV files based on a common 'URL' column
    
    # Input
    $File1 = Import-Csv '.\file1.csv' | sort URL
    $File2 = Import-Csv '.\file2.csv' | sort URL
    
    # Processing
    $Result = @()
    $File1 | % {
        $URL = $_.URL
        $Props = [ordered]@{
            URL   = $URL 
            Date  = @($_.Date ,($File2 | Where { $_.URL -eq $URL }).Date)
            Count = @($_.Count,($File2 | Where { $_.URL -eq $URL }).Count)
        }
        $Result += New-Object -TypeName psobject -Property $Props
    }
    
    # To sum the count's:
    $Result | % { $_ | Add-Member CountSum $(($_.Count | measure -Sum).Sum) }
    
    # Output:
    $Result | FT -a 
    $Result | Out-GridView
    $Result | Export-Clixml '.\file3.xml' 

    You should save this as XML not CSV. This is the proper data structure, not to have multiple date and multiple count columns. 


    Sam Boutros, Senior Consultant, Software Logic, KOP, PA http://superwidgets.wordpress.com (Please take a moment to Vote as Helpful and/or Mark as Answer, where applicable) _________________________________________________________________________________ Powershell: Learn it before it's an emergency http://technet.microsoft.com/en-us/scriptcenter/powershell.aspx http://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx




    • Edited by Sam Boutros Wednesday, January 21, 2015 1:29 PM
    Wednesday, January 21, 2015 1:26 PM
  • Hi Sam

    Thanks for the reply but this is not the output which i needed. I have created the script using if else loop.

    Anyways Thanks for your help.

    Regards

    Amol 

    • Marked as answer by Amol Pawar Friday, January 23, 2015 10:57 AM
    Wednesday, January 21, 2015 2:11 PM