Compare todays CSV file to yesterdays file

We need to import about 30K articles into our ERP daily and that is a really time consuming proces.
If I can determine the changes in todays file compared to yesterdays file that would leave only the changes to be imported. a snippet of the file looks like this:

[Product code];[Omschrijving logistiek];[Omschrijving commercieel];[Zoeknaam];[Eenheid];[BTW groep];[Art.Grp Financieel];[Barcode];[Art.Grp.Verkoop];[Korting.Grp];[Verkprijs];[Vrk.prijs vanaf];[Bestelcode];[Inkoopprijs];[Ink.prijs vanaf];[Prijs per];[Magazijn locatie]
010003;Notitieboek Atlanta 330x205 192blz ;Notitieboek Atlanta 330x205 192blz lijn blauw;Notitieb;Stuk;H;K;8710968189113;501;BRUTOK;15,57;30-01-2023;010003;8,14;30-01-2023;5;NB
010009;Notitieboek Atlanta 330x205 192blz ;Notitieboek Atlanta 330x205 192blz lijn grijs;Notitieb;Stuk;H;K;8710968187812;501;BRUTOK;15,57;30-01-2023;010009;8,14;30-01-2023;5;NB
010103;REGISTER BREEDFOLIO A1042-126 LIJN ;REGISTER BREEDFOLIO A1042-126 LIJN 48BLAD BLAUW;REGISTER;Stuk;H;K;8710968184613;501;BRUTOK;10,76;30-01-2023;010103;5,55;30-01-2023;5;NB
010119;Notitieboek Atlanta 330x205 288blz ;Notitieboek Atlanta 330x205 288blz lijn grijs;Notitieb;Stuk;H;K;8710968189212;501;BRUTOK;18,43;30-01-2023;010119;9,67;30-01-2023;5;NB
010400;Envelop Lalo bank C6 114x162 geverg;Envelop Lalo bank C6 114x162 gevergeerd wit;Envelop ;PAK;H;K;3140290214002;540;BRUTOK;9,37;30-01-2023;010400;4,45;30-01-2023;1;NB
010401;Envelop Lalo bank DL 110x220 geverg;Envelop Lalo bank DL 110x220 gevergeerd wit;Envelop ;PAK;H;K;3140290227002;540;BRUTOK;12,18;30-01-2023;010401;5,84;30-01-2023;1;NB
010402;Register Piqure dagontvangsten 320x;Register Piqure dagontvangsten 320x250mm 80vel bl;Register;Stuk;H;K;3130632145507;502;BRUTOK;30,50;30-01-2023;010402;15,79;30-01-2023;1;NB

Each line is an article. I would like to create a new file with the same structure that includes

  • all articles that do not exist in yesterdays file (based on [Product code])
  • all articles that have a changed price (based on [Verkprijs]

There is a datefield in the data, but I cannot use that since it just the date of export, so it is always todays date.

I would be very pleased if someone can point me in the right direction.

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in <---- Click :point_up_2:t4: :wink:

Have you tried using …

?? :wink:

Thanks Olaf, I was searching for that, but did not think it would be under the gear symbol, so I choose the next best thing :wink: I corrected that.

Yes I have been looking into that, but it not quite clear to me how to check specific fields.


Please read the help COMPLETELY including the examples to learn how to use it. You simply specify the property you want to compare?! :man_shrugging:t4:

How do you receive the new articles every day? By a CSV file? Then you import both CSV files - the current one and the one from yesterday and then you compare them with Compare-Object specifying the properties you want to compare against.

Thank you, but I must be doing something wrong

$today = Import-CSV "Products_doublequoted_today.csv" -Delimiter ";"
$yesterday = Import-CSV "Products_doublequoted_yesterday.csv" -Delimiter ";"

$result = Compare-Object -ReferenceObject $today -DifferenceObject $yesterday -Property "[Product code]","[Verkprijs]" -IncludeEqual -PassThru |
Where-Object {$_.SideIndicator -like "<="}
$result | Set-Content difference.csv

I receive an error like this:

Compare-Object : Wildcard characters are not allowed in “[Product code]”.

But [ i no wildcard character, is it?

PowerShell seems to treat it as a regex pattern. Try using single quotes or escape the potential special regex characters.

[regex]::Escape("[Product code]")

If that does not help you may have to rename the columns for the comaprison. :man_shrugging:t4:

Your initial suggestions concerning the [ and ] did not work, as you already feared.
So I had to rename the colums indeed. I also restore them at the end, because else it would interfere with the import proces which would have to be redefined. I have been copying and pasting a lot from this and other places on the internet, and think I “Lego’d” a working script :wink:

At the risc of getting burned for stupid thinking in this script, would you have quick look at it?
I am eager to learn from your comments!

cd C:\Temp\CompareProducts

$oldheader = "[Product code];[Omschrijving logistiek];[Omschrijving commercieel];[Zoeknaam];[Eenheid];[BTW groep];[Art.Grp Financieel];[Barcode];[Art.Grp.Verkoop];[Korting.Grp];[Verkprijs];[Vrk.prijs vanaf];[Bestelcode];[Inkoopprijs];[Ink.prijs vanaf];[Prijs per];[Magazijn locatie]"
$newheader = "Product code;Omschrijving logistiek;Omschrijving commercieel;Zoeknaam;Eenheid;BTW groep;Art.Grp Financieel;Barcode;Art.Grp.Verkoop;Korting.Grp;Verkprijs;Vrk.prijs vanaf;Bestelcode;Inkoopprijs;Ink.prijs vanaf;Prijs per;Magazijn locatie"
$selectionparam = "Product code","Omschrijving logistiek","Omschrijving commercieel","Zoeknaam","Eenheid","BTW groep","Art.Grp Financieel","Barcode","Art.Grp.Verkoop","Korting.Grp","Verkprijs","Vrk.prijs vanaf","Bestelcode","Inkoopprijs","Ink.prijs vanaf","Prijs per","Magazijn locatie"

$todayfile = ".\Products_doublequoted_today.csv"
$yesterdayfile = ".\Products_doublequoted_yesterday.csv"
$deltafile = ".\Products_delta.csv"

# strip header line from [ and ]
# it will be restored later, but must be removed because Compare-Object can't handle that

$headerchange = Get-Content -Path $todayfile
$headerchange[0] = $newheader
$headerchange | Out-File -FilePath $todayfile

$headerchange = Get-Content -Path $yesterdayfile
$headerchange[0] = $newheader
$headerchange | Out-File -FilePath $yesterdayfile

# import the CSV files
$today = Import-CSV $todayfile -Delimiter ";"
$yesterday = Import-CSV $yesterdayfile -Delimiter ";"

# compare the files and generate result
$result = Compare-Object -ReferenceObject $today -DifferenceObject $yesterday -Property "Product code","Verkprijs" -IncludeEqual -PassThru |
Where-Object {$_.SideIndicator -like "<="}

# Write result to file
$result | Select $selectionparam | 
Export-CSV -Path $deltafile -NoTypeInformation -Delimiter ";"

# Strip unnecesary quotes (";" and " at beginning and end of line)
# Due to the Export-CSV command introducing those
(Get-Content $deltafile) -replace '^.|.$' | Set-Content $deltafile
(Get-Content $deltafile) -replace '";"',';' | Set-Content $deltafile

# restore header line with [ and ]
$headerchange = Get-Content -Path $todayfile
$headerchange[0] = $oldheader
$headerchange | Out-File -FilePath $todayfile

$headerchange = Get-Content -Path $yesterdayfile
$headerchange[0] = $oldheader
$headerchange | Out-File -FilePath $yesterdayfile

$headerchange = Get-Content -Path $deltafile
$headerchange[0] = $oldheader
$headerchange | Out-File -FilePath $deltafile

Thanks for your patience!

I don’t have time at the moment to digg deep into this code … just a short tip:

You don’t need to create the headers by hand …

$InputCSV = Import-Csv -Path D:\sample\sample.csv -Delimiter ';'
$OriginalHeaderList = $InputCSV[0]
$TrimmedHeaderList = $OriginalHeaderList |
    ForEach-Object {

Now you have the $OriginalHeaderList


… and the $TrimmedHeaderList


Thanks, I’ll Lego that into my script :wink:

This worked nicely, I just "-join"ed the resulting array to a single line.