CODEWITHSUNDEEP
powershellcsvreplace
ThinkLife Pro
ThinkLife Pro

Reputation: 11

Powershell Change Field in a CSV file

Powershell Change Field in a CSV file

The issue: When downloading transactions from my bank transfers only state "Transfer from Checking" and "Transfer to Checking" I'm trying to add some additional information to the Payee field so that my financial software will determine that the two transactions one deposit and one withdrawal is in fact one transfer transaction. Example

  1. Change the Payee field from "Transfer to Checking" to "Transfer to Checking 8869"
  2. Change the Payee field from "Transfer from" to "Transfer from Checking 8828"

My Main.csv:

DATE, CATEGORY, AMOUNT, PAYEE, ACCOUNT#
10/19/2021, Transfer Internal Account Transfer, 34.04, Transfer From Checking, 5197
10/19/2021, Food and Drink Restaurants Fast Food, -15.09, Sonic, 7029
10/21/2021, Transfer Payroll, 700.2, ACH Transaction, 8828
10/21/2021, Food and Drink, -3, POS Debit VENDING, 8828
10/21/2021, Shops Supermarkets and Groceries, -4.2, Walmart, 8828
10/22/2021, Transfer Payroll, 25, PAYCHECK, 3316
10/22/2021, Transfer Payroll, 100, PAYCHECK, 3720
10/22/2021, Transfer Internal Account Transfer, -50, Transfer To Checking, 5197
10/22/2021, Transfer Internal Account Transfer, 25, Transfer From Checking, 8185
10/22/2021, Transfer Internal Account Transfer, -25, Transfer To Checking, 8828
10/22/2021, Transfer Internal Account Transfer, -25, Transfer To Checking, 8828
10/22/2021, Transfer Internal Account Transfer, -325, Transfer To Checking, 8828
10/22/2021, Shops Convenience Stores, -4.05, Circle K, 8869
10/22/2021, Transfer Internal Account Transfer, 325, Transfer From Checking, 8869
10/22/2021, Transfer Internal Account Transfer, 25, Transfer From Checking, 9116
10/24/2021, Travel Gas Stations, -7.01, POS Debit SHELL/SHELL,  8869

This is my initial attempt and I'm fairly new to Powershell.

$Table1 = Import-csv -path $COMBO_CSV -Delimiter "," | sort date -Descending
$Table2 = Import-csv -path $COMBO_CSV -Delimiter "," | sort date -Descending | Where-Object {
        ($_.amount -like "-*") -and
        ($_.payee -like "Transfer to*") } 
$transfer_to=foreach ($t1 in $Table1) {
    :outer
     Foreach($t2 in $Table2) {
       If(($t1.date -eq $t2.date) -and (-$t1.amount -eq $t2.amount)-and (($t1.payee -like "Transfer from*") -and ($t2.payee -like "Transfer to*")))  {
           $t2.payee = "Transfer to $acctno"
           $t2
           break :outer
       } 
    }
}

Upvotes: 1

Views: 52

Answers (1)

Olaf
Olaf

Reputation: 5232

Instead of changing the original data I'd prefer to add an additional cell with the needed information ... like this:

$CSVInputData = @'
DATE, CATEGORY, AMOUNT, PAYEE, ACCOUNT#
10/19/2021, Transfer Internal Account Transfer, 34.04, Transfer From Checking, 5197
10/19/2021, Food and Drink Restaurants Fast Food, -15.09, Sonic, 7029
10/21/2021, Transfer Payroll, 700.2, ACH Transaction, 8828
10/21/2021, Food and Drink, -3, POS Debit VENDING, 8828
10/21/2021, Shops Supermarkets and Groceries, -4.2, Walmart, 8828
10/22/2021, Transfer Payroll, 25, PAYCHECK, 3316
10/22/2021, Transfer Payroll, 100, PAYCHECK, 3720
10/22/2021, Transfer Internal Account Transfer, -50, Transfer To Checking, 5197
10/22/2021, Transfer Internal Account Transfer, 25, Transfer From Checking, 8185
10/22/2021, Transfer Internal Account Transfer, -25, Transfer To Checking, 8828
10/22/2021, Transfer Internal Account Transfer, -25, Transfer To Checking, 8828
10/22/2021, Transfer Internal Account Transfer, -325, Transfer To Checking, 8828
10/22/2021, Shops Convenience Stores, -4.05, Circle K, 8869
10/22/2021, Transfer Internal Account Transfer, 325, Transfer From Checking, 8869
10/22/2021, Transfer Internal Account Transfer, 25, Transfer From Checking, 9116
10/24/2021, Travel Gas Stations, -7.01, POS Debit SHELL/SHELL,  8869
'@ |
ConvertFrom-Csv

$Result= 
foreach ($item in $CSVInputData) {
    $item | 
    Select-Object -Property *, @{
        Name = 'PAYEE_ADV'; Expression = {
            if ($_.PAYEE -match 'Transfer.*Checking') {
                $_.PAYEE + ' ' + $_.'ACCOUNT#'
            }
            else { '' }
        }
    }
}

$Result | 
    Format-Table -AutoSize

Upvotes: 1

Related Questions