Solved powershell, compare 2 csv, replace if there is a match

July 29, 2016 at 02:35:42
Specs: Windows 10
Hello,

I have 2 csv files, 1.csv is with all data, 2.csv is with a parth of that data (fewer columns). The headers are the same.

I want to compare both, and if there is a match in both, I want to replace/update the data from 2.csv to 1.csv

layout of 1.csv:

NR;AM;PROF;LG;SLG;L1;L2;R1;R2
101;1;K40*40;500;0;-60;0;0;0
102;5;BD-EA31;550;0;-60;0;0;5
103;10;AB-QC35;120;0;0;30;30;0
104;4;ISOLATOR;110;0;0;30;0;60
105;2;K60*40*2;100;0;0;30;-10;1
106;3;H240;450;0;30;30;10;10
107;3;H240;450;0;60;30;10;1

layout of 2.csv:

NR;LG;SLG;L1;L2
101;270;275;-50;0
102;350;355;-60;10
105;110;105;0;30
107;100;120;60;-30

Result should be:
1.csv

NR;AM;PROF;LG;SLG;L1;L2;R1;R2
101;1;K40*40;270;275;-50;0;0;0
102;5;BD-EA31;350;355;-60;10;0;5
103;10;AB-QC35;120;0;0;30;30;0
104;4;ISOLATOR;110;0;0;30;0;60
105;2;K60*40*2;110;105;0;30;-10;1
106;3;H240;450;0;30;30;10;10
107;3;H240;100;120;60;30;10;1

I was trying this but the column is not updated (I tried to update 1 column first):

ForEach ( $Record in $1.csv ){
$Record.'SLG' = $2.csv | Select-Object SLG | ForEach-Object{$_.SLG} -match $_. NR }

What am I doing wrong ?


See More: powershell, compare 2 csv, replace if there is a match

Reply ↓  Report •


#1
July 29, 2016 at 07:29:07
What's the corelation between the two .csvs? NR? Is that column guaranteed to be unique?

How To Ask Questions The Smart Way


Reply ↓  Report •

#2
July 29, 2016 at 08:02:26
1.csv is made by a program, revisions on that list (2.csv) are delivered by someone else, the column NR is unique: the numbers refer to the same element, only the values are meant to be updated.

All the columns that are in 2.csv should be present in 1.csv, otherwise there is a faulty 1.csv delivered by the program. This is the same for the NR numbers, if they are in 2.csv, they should be in 1.csv

Maybe it is easier to create a third list as result then override 1.csv at the end ?


Reply ↓  Report •

#3
July 29, 2016 at 09:21:04
✔ Best Answer
$mainCsv = @{}
Import-Csv 1.csv -Delimiter ';' | 
 ForEach-Object { $mainCsv.Add($_.NR, $_) }

Import-Csv 2.csv -Delimiter ";" |
 ForEach-Object { 
   $record = $mainCsv[$_.NR]
   foreach ($f in (gm -in $_ -mem NoteProperty)) {
     $record.$($f.Name) = $_.$($f.Name)
   }
 }

$mainCsv.GetEnumerator() | 
 Sort-Object Name | 
 Select-Object -ExpandProperty Value | 
 ConvertTo-Csv -Delimiter ';' -NoTypeInformation |
 ForEach-Object { $_ -replace '";"', ';' -replace '^"|"$', '' } |
 Out-File 1.csv

How To Ask Questions The Smart Way


Reply ↓  Report •

Related Solutions

#4
July 29, 2016 at 13:09:27
Awesome ! Works like a charm !!

Thank you very much Mr Razor 2.3


Reply ↓  Report •

#5
September 22, 2016 at 12:23:50
Hmm, something has changed, although I see no differences in the delivered files It will not work anymore when there is a difference in the amount of present NR's.

I get this error message:

The property 'NR' cannot be found on this object. Verify that the property exists and can be set.
At C:\xxx\csv_export.ps1:66 char:32
+ $record.$($f.Name) = $_.$($f.Name)
+ ~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound

This error is repeated for all headers.

It is there, but is looks to me Powershell can't read it ?? Strange thing is, when the amount of NR's is equal in 1.csv and 2.csv, I do not get this error message en it updates normally.

Any hints ? Thanks


Reply ↓  Report •

#6
September 22, 2016 at 17:26:41
I'd have to look at the troubled .csv's to make any comment that isn't a random shot in the dark. Best guess is 2.csv has NR entries not in 1.csv.

How To Ask Questions The Smart Way


Reply ↓  Report •

#7
September 23, 2016 at 00:54:49
Ha, I ran through the lines of the deliverd csv's and you are right. To add new functionalities, the list I get delivered contains more NR's then the previous ones, so they told me. They will not turn back to the old style of csv.

If I remove several 100's of NR's that are in 2.csv and not in 1.csv it runs correct. So your code is still correct !

Is it possible to include this in this code ? I mean to update only if the NR match ?

addind -match here does not compare the match of NR

Import-Csv $namedfolder2 -Delimiter ";" |
ForEach-Object {
$record = $mainCsv[$_.POSNR]
foreach ($f in (gm -in -match $_ -mem NoteProperty)) {
$record.$($f.Name) = $_.$($f.Name)
}
}


Reply ↓  Report •

#8
September 23, 2016 at 04:26:29
Sure, we can add a check. I didn't bother 'cause initially it was a state that required further investigation. Easiest way would be to do add a Where-Object filter, like this:
Import-Csv $namedfolder2 -Delimiter ";" |
 Where-Object { $mainCsv.Contains($_.POSNR) } |
 ForEach-Object { 

How To Ask Questions The Smart Way

message edited by Razor2.3


Reply ↓  Report •

#9
September 23, 2016 at 05:00:02
Hello, thanks for thinking along with me !

You are correct, the extra check was not mandatory in my original question.

When I do this:

Import-Csv $namedfolder2 -Delimiter ";" |
Where-Object { $record.Contains($_.POSNR) } |
ForEach-Object {
$record = $mainCsv[$_.POSNR]
foreach ( $f in (gm -in $_ -mem NoteProperty)) {
$record.$($f.Name) = $_.$($f.Name)
}
}

The list is not updated and this is the returned error:

Method invocation failed because [Selected.System.Collections.DictionaryEntry] does not contain a method named 'Contains'.
At C:\xxx\update csv 2.ps1:16 char:16
+ Where-Object { $record.Contains($_.POSNR) } |
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (Contains:String) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound


Reply ↓  Report •

#10
September 23, 2016 at 05:04:52
Doing this updates the list, but still gives errors for the POSNR's that are not in the list.

Import-Csv $namedfolder2 -Delimiter ";" |
ForEach-Object {
$record = $mainCsv[$_.POSNR]
foreach ( $f in (gm -in $_ -mem NoteProperty)) {
If($_ -eq $mainCsv[$_.POSNR] ) {continue}
$record.$($f.Name) = $_.$($f.Name)
}
}


Reply ↓  Report •

#11
September 23, 2016 at 05:11:29
My mistake, it should be $mainCsv.Contains

How To Ask Questions The Smart Way


Reply ↓  Report •

#12
September 23, 2016 at 05:18:30
This update works correctly.

Succes :)

Thanks


Reply ↓  Report •


Ask Question