CODEWITHSUNDEEP
powershell
Tim
Tim

Reputation: 31

PowerShell modify date column in csv

I have a series of csv statements from my bank that I would like to combine and pull out the relevant rows. I have managed to get the rows out, but for the date column I would like to reformat the date from DD/MM/YYYY HH:MM to YYYY-MM-DD and I can't Google up the concept of how I should do this? I'm a PS noob.

Can anyone steer me in the right direction please?

Here's what I have so far:

$files = Get-ChildItem -Filter *.csv 

$output = ForEach ( $file in $files ) {
    Import-CSV $file | Where-Object{$_.Description -eq "Funding Bacs" -or $_.Description -eq "Lender Withdrawal Request" } | Select-Object Date,Description,"Paid In", "Paid Out" 
    # foreach of the date fields, take the date object? string? remove the time, and reformat the date to YYYY-MM-DD 
} 

$output | Export-Csv Newtest.csv -NoTypeInformation -encoding "unicode"

Current Output:

Date             Description               Paid In Paid Out
----             -----------               ------- --------
03/04/2012 09:15 Funding Bacs              50.0    0.0
06/04/2013 17:32 Lender Withdrawal Request 0.0     234.5
01/04/2014 05:31 Funding Bacs              125.0   0.0
01/04/2014 05:31 Funding Bacs              10.0    0.0

Desired Output:

Date             Description               Paid In Paid Out
----             -----------               ------- --------
2012-04-03       Funding Bacs              50.0    0.0
2013-04-06       Lender Withdrawal Request 0.0     234.5
2014-04-01       Funding Bacs              125.0   0.0
2014-04-01       Funding Bacs              10.0    0.0

Update: thanks to Martin I'm now getting the below... need to figure out why I'm getting the missed date values.

Date             Description               Paid In Paid Out
----             -----------               ------- --------
2014-03-11 Funding Bacs              10.0    0.0
2014-03-11 Funding Bacs              125.0   0.0
           Lender Withdrawal Request 0.0     521.05
2016-07-11 Lender Withdrawal Request 0.0     188.93
           Lender Withdrawal Request 0.0     185.57
2012-03-10 Lender Withdrawal Request 0.0     148.72
2013-01-10 Funding Bacs              460.0   0.0

Upvotes: 2

Views: 632

Answers (1)

Martin Brandl
Martin Brandl

Reputation: 58931

You could use a calculated property where you format the date. Just change the Select-Object to:

Select-Object @{l="Date"; e={(Get-Date $_.Date).ToString('yyyy-MM-dd')}},Description,"Paid In", "Paid Out"

Upvotes: 1

Related Questions