Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
column comaprison in CSV file using powershell

Answered column comaprison in CSV file using powershell

  • Friday, March 01, 2013 9:42 PM
     
     
    Sign In to Vote
    0

    Hello,

    I have one CSV file with the following columns,

    Site Title
    SiteURL 
    Site Storage MB 
    Allocated Space MB 
    Primary Site Administrator 
    Last Name 
    First Name 
    Contact Request Access email

    and I have one more CSV file with the following column,

    LastAccessed 
    SiteURL
    Username

    Now What I need is compare SiteURL column in both these CSV file and if it's equal then Output should look like as below

    OutPut

    Site Title
    SiteURL
    Site Storage MB
    Allocated Space MB
    Primary Site Administrator
    Last Name
    First Name
    Contact Request Access email
    LastAccessed
    Username

    Can anyone please help me with the powershell script?

    Thanks,




    • Edited by abc67 Friday, March 01, 2013 9:43 PM
    • Edited by abc67 Friday, March 01, 2013 9:43 PM
    • Edited by abc67 Friday, March 01, 2013 9:45 PM
    •  
     

All Replies

  • Friday, March 01, 2013 9:49 PM
    Moderator
     
     
    Sign In to Vote
    0
    What do you have so far?

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

     
  • Friday, March 01, 2013 10:14 PM
     
     
    Sign In to Vote
    0

    I'm trying something like this,

    $csv1 = Import-CSV E:\CSVImport\csv1Report.csv
    $csv2 = Import-CSV E:\CSVImport\csv2Report.csv

    foreach ($row.SiteURL in $csv1)
    {
    [Array]$sitename1 = $row.SiteURL
      
    foreach ($row.SiteURL in $csv2)
        {
        
      [Array]$siteurl1 = $row.SiteURL
        
    If ($sitename1 -eq $siteurl1)
         {
         write-host working
         }
       
    }
    }

    I'm not getting anything...:(

     
  • Friday, March 01, 2013 10:28 PM
    Moderator
     
     
    Sign In to Vote
    0

    That's....interesting.

    How long have you been working with Powershell?

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

     
  • Friday, March 01, 2013 10:57 PM
     
      Has Code
    Sign In to Vote
    0

    You can do the following:

    Import-CSV csv1Report.csv | %{

 $SiteUrl = $_.SiteUrl
 $JoinWith = Import-CSV csv2Report.csv | ? { $_.SiteUrl -eq $SiteUrl }

 $_ | Select-Object *,
  @{n='LastAccessed';e={ $JoinWith.LastAccessed }},
  @{n='Username';e={ $JoinWith.Username }}

} | Export-CSV csvJoinedReport.csv -NoType

    -We import the first csv file and goes through each object (row).
    -We then import the second csv file, but only the object that match the SiteUrl of the current object we are at.
    -Then we create a new array of objects (table), with everything from the object from the first csv, and LastAccessed and Username from the object from the second csv.
    -We then Export the array of object to a new csv file.

    Best Regards
    Claus Codam
    Consultant, Developer
    Coretech - Blog


    • Edited by Claus Codam Friday, March 01, 2013 10:58 PM
    •  
     
  • Friday, March 01, 2013 11:12 PM
     
      Has Code
    Sign In to Vote
    0
    This uses the same principle - it only imports csv2 once. (Will have an impact if comparing large files.) 
    $csv1=import-csv 'c:\temp\csv1.csv'
    $csv2=import-csv 'c:\temp\csv2.csv'
    & {ForEach($a in $csv2){
        $csv1 | ?{$a.ColumnA -eq $_.ColumnA} | Select *,@{L='ColumnC';E={$a.ColumnC}}
    }} | Export-CSV 'c:\temp\csv3.csv' -notypeinfo

    Inspired by Carlsberg.

     
  • Friday, March 01, 2013 11:14 PM
    Moderator
     
     
    Sign In to Vote
    0
    That foreach construct looks strangely familiar..;).

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

     
  • Friday, March 01, 2013 11:46 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Slightly different tack:

    $ht = @{}
    import-csv csv1.csv|%{$ht[$_.SiteURL] = $_}
    import-csv csv2.csv |% {
    if ($ht[$_.SiteURL]){
     $ht[$_.SiteURL]|select *,@{l='LastAccessed';e={$_.LastAccessed}},@{l='UserName';e={$_.UserName}}}
     } export-csv csv3.csv -notype


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "


     
  • Friday, March 01, 2013 11:48 PM
     
     
    Sign In to Vote
    0
    That foreach construct looks strangely familiar..;).

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "


    Yes - I picked that technique up from a wise old scripter who's name I can't rem... oh, wait... :0

    Inspired by Carlsberg.

     
  • Friday, March 01, 2013 11:51 PM
    Moderator
     
     
    Sign In to Vote
    0
    Looks like it works pretty good!

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

     
  • Friday, March 01, 2013 11:54 PM
     
     
    Sign In to Vote
    0
    And it's the simplest way to pipe the objects out of the ForEach loop.

    Inspired by Carlsberg.