0
  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
Charlie C
  • 1
  • 2
  • What have you tried thus far? Show us you work. – Mark Kram Aug 27 '19 at 19:09
  • I've tried inputting them into an aray, but I have no idea how to go about this. Editing in code. – Charlie C Aug 27 '19 at 19:12
  • @CharlieC can you post the code you have tried so far? – techguy1029 Aug 27 '19 at 19:13
  • You should explain the matching from your point 3 with an example, how do the matches in `$csv1`/`$csv2` relate to the other file? –  Aug 27 '19 at 19:31
  • @LotPings - The explanation is included in point 3. Content in $CSV1 for each row in columns B and K need to be matched to content in ANY row in column B and H in $CSV2. The matched row in $CSV2 needs to have $CSV2.K pulled, and exported to a new CSV clone of CSV1 , per row, in a new column. – Charlie C Aug 27 '19 at 20:07
  • Well, that explanation doesn't match your code. Do $CSV2.B with $CSV1.H (albeit in code it seems to be same name column) together form a unique identifier? And if not which one to select when appending data to new $CSV1? In general I'd build a hash table from both columns as key from $Csv2 and the row as value, then iterate $CSV1 build the same key lookup the the hash and apply the new col value. –  Aug 27 '19 at 20:14
  • Instead of reinventing the wheel, you might try this [Join-Object](https://www.powershellgallery.com/packages/Join) cmdlet that indeed creates a hash table for the concerned columns in `$Csv2` to get better performance. The syntax should be something like: `$Csv1 | Join $Csv2 -On ItemNumber, Sold -Property ItemNumber, Sold, TergetData` – iRon Aug 27 '19 at 20:33
  • Possible duplicate of [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/questions/1848821/in-powershell-whats-the-best-way-to-join-two-tables-into-one) – iRon Aug 27 '19 at 21:13
  • Join-object does not seem to work when matching multiple properties. No luck at all messing with this since 7AM. – Charlie C Aug 28 '19 at 19:02

1 Answers1

0

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
Community
  • 1
  • 1
  • I'll test this first thing in the morning and update you. Calculated properties are beyond me - any chance you'd break it down for me, or provide documentation on function? – Charlie C Aug 28 '19 at 00:55
  • This is what I'm running. It does run, does clone the CSV, but again the TargetData column is empty. 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)" – Charlie C Aug 28 '19 at 14:29
  • Sorry for late response. There are some quoting errors in your code, also the column naming varies, use single quotes ***inside*** a double quoted string. See changed answer. –  Aug 29 '19 at 09:24
  • -Missing closing squiggle on the second hash, missing square bracket on the second hash. Added them, still saying the literal is incomplete. Not seeing the difference between the hash on the first go-round and the second. – Charlie C Aug 29 '19 at 13:58
  • Sorry for that (was taken over from your code) it are two curly brackets missing, should be correct now, the column/property naming is up to you. –  Aug 29 '19 at 14:03
  • I'm thinking something on my system changed, I went back and checked the code from yesterday that WAS working, but putting our no data into the TargetData column, and that does not work now either, even though nothing has changed with my data. Not your problem, let me figure this out hahaha – Charlie C Aug 29 '19 at 14:25
  • Fixed, but still outputting blank data. Had to remove the code, pasted the wrong one. It was reading $_.Sold as null. Fixed that due to a format error on my folder path, and not sure why but it did not like the break after the pipe. The MAIN content of sqlexportcsv is written, but the TargetData is not appended. (It does, however, create the column as expected.) – Charlie C Aug 29 '19 at 15:08
  • I rested with simplified sample data, as long the column names match it works here. –  Aug 29 '19 at 15:09
  • DISREGARD. The accounting guys sent me a sheet with FORMULA-based amounts in those columns, how could I not see that LOL. Testing again. – Charlie C Aug 29 '19 at 15:12