I am writing a PowerShell script that reads a CSV file in the following format:
A,B,ProgrammeSeller,D,E,F,G,H,I,J,K,L,M,N,O,P,SellerCode,Date,Seller,Currency1,InvoiceAmmount,DiscountAmount,PurchasePrice,TotalsCurrency,TotalInvoiceAmmount,TotalDiscountAmmount,TotalPurchasePrice,Reviewed,AC,Signed
,,PROGRAMME,,,,,,,,,,,,,,380,09/12/2021,SELLER_BE,EUR,2813828.46,17594.22,2796234.24,EUR,0,0,0,Reviewed by:,,Signed:
,,PROGRAMME,,,,,,,,,,,,,,383,09/12/2021,SELLER_DE,EUR,3287812.58,17595.8,3270216.78,EUR,0,0,0,Reviewed by:,,Signed:
,,PROGRAMME,,,,,,,,,,,,,,383,09/12/2021,SELLER_DE,USD,1520725.4,11428.98,1509296.42,USD,0,0,0,Reviewed by:,,Signed:
,,PROGRAMME,,,,,,,,,,,,,,381,09/12/2021,SELLER_DK,DKK,6047281.25,26163.13,6021118.12,DKK,0,0,0,Reviewed by:,,Signed:
,,PROGRAMME,,,,,,,,,,,,,,381,09/12/2021,SELLER_DK,EUR,11376479.39,39580.28,11336899.11,EUR,0,0,0,Reviewed by:,,Signed:
,,PROGRAMME,,,,,,,,,,,,,,381,09/12/2021,SELLER_DK,USD,12571895.13,71198.51,12500696.62,USD,0,0,0,Reviewed by:,,Signed:
And I want to group and sum 3 of the columns (InvoiceAmmount,DiscountAmount,PurchasePrice), and update the columns (TotalInvoiceAmmount,TotalDiscountAmmount,TotalPurchasePrice) on a new file which is a copy of the original but with the columns (TotalInvoiceAmmount,TotalDiscountAmmount,TotalPurchasePrice) updated with the values of the sums whenever there is a match on Currency value.
I have written the following script:
$csvFile = Import-Csv "C:\OutputFiles\OTC_Purchase_Report_EUProgramme_20220420_TMP.csv"
$csvFinal = "C:\OutputFiles\OTC_Purchase_Report_EUProgramme_20220420.csv"
function WriteTotalsToCSV
{
$totals | ForEach-Object {
$totalCurrency = $_.Currency
$totalInvoiceAmmount = $_.TotalInvoiceAmmount
$totalDiscountAmmount = $_.TotalDiscountAmmount
$totalPurchasePrice = $_.TotalPurchasePrice
$csvFile | ForEach-Object {
if($_.Currency1 -eq $totalCurrency){
$_.TotalsCurrency = $totalCurrency
$_.TotalInvoiceAmmount = $totalInvoiceAmmount
$_.TotalDiscountAmmount = $totalDiscountAmmount
$_.TotalPurchasePrice = $totalPurchasePrice
}
$_ | Export-Csv $csvFinal -NoTypeInformation -Append
}
}
}
function InvoiceAmmountTotals
{
param ($file)
$headers = ($file | Get-Member -MemberType NoteProperty).Name
$totals = $file | Select-Object $headers | Group-Object Currency1
foreach ($total in $totals)
{
$showtotal = New-Object System.Management.Automation.PsObject
$showtotal | Add-Member NoteProperty Currency $total.Name
$showtotal | Add-Member NoteProperty TotalInvoiceAmmount ($total.Group | Measure-Object -Sum InvoiceAmmount).Sum
$showtotal | Add-Member NoteProperty TotalDiscountAmmount ($total.Group | Measure-Object -Sum DiscountAmount).Sum
$showtotal | Add-Member NoteProperty TotalPurchasePrice ($total.Group | Measure-Object -Sum PurchasePrice).Sum
$showtotal
}
}
#execution
$totals = InvoiceAmmountTotals $csvFile
WriteTotalsToCSV
The script is grouping and summing the totals as expected but when it writes the new CSV file it is supposed to update the columns based on a match of column Currency1, but it is only doing this for the first match (in this case EUR) and ignoring the remaining matches i.e.:
"A","B","ProgrammeSeller","D","E","F","G","H","I","J","K","L","M","N","O","P","SellerCode","Date","Seller","Currency1","InvoiceAmmount","DiscountAmount","PurchasePrice","TotalsCurrency","TotalInvoiceAmmount","TotalDiscountAmmount","TotalPurchasePrice","Reviewed","AC","Signed"
"","","PROGRAMME","","","","","","","","","","","","","","380","09/12/2021","SELLER_BE","EUR","2813828.46","17594.22","2796234.24","EUR","153995434.65","797318.07","153198116.58","Reviewed by:","","Signed:"
"","","PROGRAMME","","","","","","","","","","","","","","383","09/12/2021","SELLER_DE","EUR","3287812.58","17595.8","3270216.78","EUR","153995434.65","797318.07","153198116.58","Reviewed by:","","Signed:"
"","","PROGRAMME","","","","","","","","","","","","","","383","09/12/2021","SELLER_DE","USD","1520725.4","11428.98","1509296.42","USD","0","0","0","Reviewed by:","","Signed:"
"","","PROGRAMME","","","","","","","","","","","","","","381","09/12/2021","SELLER_DK","DKK","6047281.25","26163.13","6021118.12","DKK","0","0","0","Reviewed by:","","Signed:"
"","","PROGRAMME","","","","","","","","","","","","","","381","09/12/2021","SELLER_DK","EUR","11376479.39","39580.28","11336899.11","EUR","153995434.65","797318.07","153198116.58","Reviewed by:","","Signed:"
"","","PROGRAMME","","","","","","","","","","","","","","381","09/12/2021","SELLER_DK","USD","12571895.13","71198.51","12500696.62","USD","0","0","0","Reviewed by:","","Signed:"
Note when column Currency1 value is USD the columns (TotalInvoiceAmmount,TotalDiscountAmmount,TotalPurchasePrice) are not being updated.
Any suggestions on where I am going wrong here?
Note: The CSV files are much larger, I have added a small number of entries for example purpose
Thanks
It looks like you are looping through each line of the csv as many times as you have currencies because you are looping through $totals and inside of each of those loops you are looping through each line of the csv causing duplicates.
Loop only once through the csv lines and for each line find the matching currency in your $totals array to update each csv line with. Finally output all at once to Export-Csv
function WriteTotalsToCSV {
# only one loop through csv lines
$csvFile | ForEach-Object {
$line = $_
# find matching currency in your $totals array using Where()
$totalsMatch = $totals.Where({ $line.Currency1 -eq $_.Currency })
$line.TotalsCurrency = $totalsMatch.Currency
$line.TotalInvoiceAmmount = $totalsMatch.TotalInvoiceAmmount
$line.TotalDiscountAmmount = $totalsMatch.TotalDiscountAmmount
$line.TotalPurchasePrice = $totalsMatch.TotalPurchasePrice
$line
# collect all the lines first and then export to csv once at the end
} | Export-Csv -Path $csvFinal -NoTypeInformation
}