CODEWITHSUNDEEP
powershell
Charlie C
Charlie C

Reputation: 1

Matching content across CSV and writing matched data

  1. Two CSV's. $CSV1 and $CSV2.
  2. CSV1 has 50 columns, 40,000 rows (And headers.)
    CSV2 has 17 columns, ~900 rows, plus headers also.)
  3. I need to match each row in $CSV1 where $CSV1.B and $CSV1.K
    match ANY ROW in $CSV2 in which $CSV2.B and $CSV2.H match,
    then grab $CSV2.K from the matched row in $CSV2. To clarify, if ItemNumber is 137 and Sold is 15 in $CSV1 - I need to find ANY line in $CSV2 where ItemNumber is 137, and Sold is 15. On that row, I then need to pull TargetData's value, and append to a new copy of CSV1, in a new column on its own row - for every line in CSV1.
  4. I then need to Append the matched $CSV2.K to a NEW copy of $CSV1, with the matched data added to a new column, with a new header.

I'm not overly familiar with CSV work in powershell, any input is greatly appreciated.

Tried putting each CSV into an array, and running a foreach-object loop on Array2 to match $CSV2 WHERE $CSV1.B and $CSV2.K match $CSV2.B and $CSV2.H, then creating a custom PSOBJECT with $CSV2.K. This did not work.

$CSV1 = import-csv C:\Users\blah\sqlexport.csv
$array1=@()

$csv2= Import-Csv -Path  (Get-ChildItem -Path C:\Users\blah\csvset\-Filter '*.csv').FullName
$array2=@()

$csv1 | foreach-object {
     $csv2 | Where-Object { $CSV1.ItemNumber -eq $_.ItemNumber -and $CSV1.Sold -eq $_.Sold }
    [PSCustomObject]@{
        Value1 = $CSV1.ItemNumber
        Value2 = $CSV1.Sold
        Value3 = $CSV2.TargetData
    }
} | export-csv -path C:\Users\blah\test.csv -NoTypeInformation -Append

Upvotes: 0

Views: 73

Answers (1)

user6811411
user6811411

Reputation:

Provided the given column names ItemNumber,Sold,TargetData do exist,
this quick&dirty script using a calculated property should do,
just replace the .CSV path\file names.

$hash=@{}
Import-Csv .\CSV2.csv | ForEach-Object{$hash["$($_.ItemNumber),$($_.Sold)"]=$_.TargetData}

Import-Csv .\CSV1.csv |
  Select-Object -Property *,@{n='TargetData';e={$hash["$($_.ItemNumber),$($_.Sold)"]}} |
    Export-Csv .\New-CSV1.csv -NoTypeInformation

Based on your comment, try this:

Import-Csv (Get-ChildItem -Path C:\Users\blah\bunchofcsvs\ -Filter '*.csv').FullName | 
    ForEach-Object{$hash["$($_.'Item Number'),$($_.Sold)"]=$_.TargetData} 
 
Import-Csv C:\Users\blah\sqlexportcsv\csv.csv | 
    Select-Object -Property *,@{n='TargetData';e={$hash["$($_.ItemNumber),$($_.SoldQty)"]}} | 
        Export-Csv New.csv -NoTypenformation

Upvotes: 0

Related Questions