How can we transform Date in CSV file using PowerShell scripts

Question

I have a csv file having a column - Date. Currently its format is 7/30/2019. I want to make Date transformations using PowerShell. The required format would be: 2019-30-07. How can we do that?

I used following code to do this. But it is only doing partial for some records only. And not for other records.

Code I am using:

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

load the file via Import-CSV, iterate thru that collection of objects & convert the date STRING to a date object ... and then use the .ToString() method of that object to make a new date string in the format you prefer. [grin] then, finally, save to a new CSV file with Export-CSV. — Lee_Dailey
– Lee_Dailey, Commented Jun 10, 2019 at 14:42

Bill_Stewart · Accepted Answer · 2019-06-10 20:54:22Z

2

One way to do this is to cast as [DateTime] and then output in the format you want. Example:

Import-Csv ImportData.csv | Select-Object LastName,FirstName,
  @{Name = "Date"; Expression = {"{0:yyyy-MM-dd}" -f ($_.Date -as [DateTime])}}
} | Export-Csv New.csv -NoTypeInformation

This example selects the columns and uses a calculated property to cast the date to the [DateTime] type and then output in the desired string format.

edited Jun 10, 2019 at 20:54

answered Jun 10, 2019 at 14:45

Bill_Stewart

24.9k5 gold badges55 silver badges70 bronze badges

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

2 Comments

Nishant Fukate Over a year ago

Tried it. But it is exporting the date with blank column.

Bill_Stewart Over a year ago

Update your question with a sample row from your input file. (Don't forget to include the header row with the column names.)

user6811411 · Accepted Answer · 2019-06-10 20:44:13Z

To have less trouble modifying a csv file,
without knowing or altering the order of columns,
you can user the following script.

It 1st reads in the original header
after replacing the Date column with the reformatted one with a calculated property
(which would otherwise be the last column)
it reestablishes the previous order with an additional Select-Object

## Q:\Test\2019\06\10\SO_56528528.ps1

$CsvIN  = '.\Sample.csv'
$CsvOut = '.\NewSample.csv'
$Header = (Get-Content $CsvIn -Head 1).split(',')

Import-Csv $CsvIn |
  Select-Object *,@{n='Date';e={(Get-Date $_.Date).ToString('yyyy\-MM\-dd')}} -ExcludeProperty Date | 
    Select-Object $Header | 
      Export-Csv $CsvOut -NoTypeInformation

> Get-Content .\Sample.csv
foo,bar,Date,baz
abc,def,7/30/2019,ghi

> Get-Content .\NewSample.csv
"foo","bar","Date","baz"
"abc","def","2019-07-30","ghi"

Collectives™ on Stack Overflow

How can we transform Date in CSV file using PowerShell scripts

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related