Comparing csv files with -like in Powershell

Question

I have two csv files, each that contain a PATH column. For example:

CSV1.csv

PATH,Data,NF
\\server1\folderA,1,1
\\server1\folderB,1,1
\\server2\folderA,1,1
\\server2\folderB,1,1

CSV2.csv

PATH,User,Access,Size
\\server1\folderA\file1,don,1
\\server1\folderA\file2,don,1
\\server1\folderA\file3,sue,1
\\server2\folderB\file1,don,1

What I'm attempting to do is create a script that will result in separate csv exports based on the paths in CSV1 such that the new files contain file values from CSV2 that match. For example, from the above, I'd end up with 2 results:

result1.csv

\\server1\folderA\file1,don,1
\\server1\folderA\file2,don,1
\\server1\folderA\file3,sue,1

result2.csv

\\server2\folderB\file1,don,1

Previously I've used a script lime this when the two values are exact:

$reportfile = import-csv $apireportoutputfile -delimiter ';' -encoding unicode
$masterlist = import-csv $pathlistfile

foreach ($record in $masterlist)
    {
    $path=$record.Path
    $filename = $path -replace '\\','_'
    $filename = '.\Working\sharefiles\' + $filename + '.csv'

    $reportfile | where-object {$_.path -eq $path} | select FilePath,UserName,LastAccessDate,LogicalSize | export-csv -path $filename
    write-host "     Creating files list for $path"  -foregroundcolor red -backgroundcolor white
    }

however since the two path values are not the same, it returns nothing. I found a -like operator but am not sure how to use it in this code to get the results I want. where-object is a filter while -like ends up returning a true/false. Am I on the right track? Any ideas for a solution?

mjolinor · Accepted Answer · 2014-09-11 21:35:58Z

0

Something like this, maybe?

$ht = @{}
Import-Csv csv1.csv |
  foreach { $ht[$_.path] = New-Object collections.arraylist }

Import-Csv csv2.csv |
  foreach { 
            $path = $_.path | Split-Path -Parent
            $ht[$path].Add($_) > $null
          }

$i=1
$ht.Values |
 foreach { if ($_.count)
            {
             $_ | Export-Csv "result$i.csv" -NoTypeInformation
             $i++
            }
          }

answered Sep 11, 2014 at 21:35

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ritehere · Accepted Answer · 2014-09-12 03:35:35Z

0

My suggestion:

$1=ipcsv .\csv1.CSV
$2=ipcsv .\csv2.CSV
$equal = diff ($2|select @{n='PATH';e={Split-Path $_.PATH}}) $1 -Property PATH -IncludeEqual -ExcludeDifferent -PassThru
0..(-1 + $equal.Count) | %{%{$i = $_}{
     $2 | ?{ (Split-Path $_.PATH) -eq $equal[$i].PATH } | epcsv ".\Result$i.CSV"
}}

edited Sep 12, 2014 at 3:35

answered Sep 12, 2014 at 3:01

ritehere

12 bronze badges

Collectives™ on Stack Overflow

Comparing csv files with -like in Powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related