Compare todays CSV file to yesterdays file

PowerShell Help
1

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.
Thanks!

2

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 PowerShell.org <---- Click :point_up_2:t4: :wink:

Have you tried using …

?? :wink:

3

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.

4

:+1:t4:

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.

5

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?

6

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:

7

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!

8

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].psobject.properties.name
$TrimmedHeaderList = $OriginalHeaderList |
    ForEach-Object {
        $_.trim('][')
    }

Now you have the $OriginalHeaderList …

$OriginalHeaderList

… and the $TrimmedHeaderList

$TrimmedHeaderList
9

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

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