Powershell - amend date format to values in CSV column

Question

I'm looking for a PowerShell script for the following ...

I have a CSV spread sheet which automatically downloads from a website, the 2nd column of the spread sheet is a date value in the UK format dd/mm/yyyy. I'd like a script that will change the date format of all values in column 2 to the US date format yyyy/mm/dd.

I'm importing the CSV into a MySQL database using LOAD DATA INFILE and at present the dates are going in 2017/02/15 when they should be 2015/02/17.

An example of the CSV file format is as follows ...

Col1,Col2,Col3,Col4
Value1,17/02/15,Value3,Value4
Value1,18/02/15,Value3,Value4
Value1,19/02/15,Value3,Value4

I need it to become ...

Col1,Col2,Col3,Col4
Value1,2015-02-17,Value3,Value4
Value1,2015-02-18,Value3,Value4
Value1,2015-02-19,Value3,Value4

Loïc MICHEL · Accepted Answer · 2015-02-19 12:55:31Z

1

one way could be (will replace all date in UK format to US format, regardless of the column):

(get-content c:\file.csv) -replace "(\d{2})\/(\d{2})\/(\d{4})", '$3/$2/$1'

if you want to save the result pipe to set-content :

(get-content c:\file.csv) -replace "(\d{2})\/(\d{2})\/(\d{4})", '$3/$2/$1'  |sc c:\file.csv

answered Feb 19, 2015 at 12:55

Loïc MICHEL

26.3k10 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

James Over a year ago

I tried running the script against the CSV, it appears to run but doesn't change the date format - the CSV still shows the date as 17/02/15 as apposed to 2015-02-17.

Loïc MICHEL Over a year ago

did you save the file using set-content ?

James Over a year ago

Yes - I've copied your code from the 2nd example and tried it with 'set-content' also. It saves the file to a new CSV but has no change to the date format.

James Over a year ago

Having reviewed your post - I believe I stumbled across this possible 'way' in another post but all it seemed to do then was change the date to the following 17/02/2015 - hence 2 2 4, rather than actually reverse the year and day values.

Loïc MICHEL Over a year ago

please update your question and put some lines of your csv please, this is working on my win7 x64....

|

wallybh · Accepted Answer · 2015-02-19 13:04:10Z

1

I like to use TryParseExact. You can convert many formats and you can find invalid values.

Example:

$dateString = "17/02/2015"

$format = "dd/MM/yyyy"
[ref]$parsedDate = get-date
$parsed = [DateTime]::TryParseExact($dateString, $format,[System.Globalization.CultureInfo]::InvariantCulture,[System.Globalization.DateTimeStyles]::None,$parseddate)

$parsedDate.Value.ToString("yyyy/MM/dd")

answered Feb 19, 2015 at 13:04

wallybh

9621 gold badge11 silver badges31 bronze badges

4 Comments

James Over a year ago

This code is .NET - I'm looking for something in PowerShell ideally.

Matt Over a year ago

@james If that is a requirement you need to put that in the question. This is a perfectly good answer.

James Over a year ago

I agree the answer is good, the title starts 'Powershell' - apologies if I have not made it more clear.

wallybh Over a year ago

@james this is powershell. You can call all .net methods inside powershell. I tested in powershell ise. Just run this code and you'll see.

Collectives™ on Stack Overflow

Powershell - amend date format to values in CSV column

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related