PowerShell -- Import csv, reformat date values, export csv

Question

I have a large volume of CSV records that have a date column that needs to be reformatted. The format in the source files is "MM/dd/yyyy hh:mm". The format I need to change to is "yyyy-MM-dd". I've tried a few different things, and I've been able to get the two pieces to work (import and transform, or export), but not both together.

Sample data

Date Created,ID,Email Address,First Name,Last Name
3/15/2019 14:56,some-string,[email protected],first,last
3/15/2019 13:56,some-string,[email protected],first,last

Import and transform (w/ Write-Host to confirm reformat)

Import-Csv .\foo.csv |
    ForEach-Object {
        $_."Date Created" = [datetime]::Parse($_."Date Created").ToString('yyyy-MM-dd')
        Write-Host $_."Date Created"
    }

Import and Export (w/ no transform)

Import-Csv foo.csv |
  Select-Object "Date Created", ID, "Email Address", "First Name", "Last Name" |
  Export-Csv -NoTypeInformation foo-new.csv

I've tried this to combine the two pieces, but I end up with an empty CSV file

Import-Csv foo.csv |
  ForEach-Object {
    $_."Date Created" = [datetime]::Parse($_."Date Created").ToString('yyyy-MM-dd')
  } | 
  Select-Object "Date Created", ID, "Email Address", "First Name", "Last Name" |
  Export-Csv -NoTypeInformation foo-new.csv

Any help to smash these together, so I have my reformat and my successful export, would be greatly appreciated.

Your last code snippet is perfectly fine for updating the CSV, the problem is that your ForEach-Object { ... } is not sending any object through the pipeline hence Select-Object is not receiving anything. — Santiago Squarzon
– Santiago Squarzon, Commented Jan 19, 2022 at 19:41

Theo · Accepted Answer · 2022-01-19 19:03:22Z

2

You need to use a calculated property with Select-Object

Try

Import-Csv foo.csv |
Select-Object @{Name = 'Date Created'; Expression = { '{0:yyyy-MM-dd}' -f [datetime]$_.'Date Created' }},
              * -ExcludeProperty 'Date Created' |
Export-Csv -NoTypeInformation foo-new.csv

answered Jan 19, 2022 at 19:03

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

2 Comments

jarod Over a year ago

This did exactly what I needed it to do. Thank you!

jarod Over a year ago

With your example, I was able to also add a transformation bit for a 2nd column to strip out an extra " " at the end of the string. Thank you!

Abraham Zinala · Accepted Answer · 2022-01-19 19:05:06Z

1

Easiest way to go about this is using a calculated property:

Import-Csv -Path C:\users\Abraham\Desktop\csvDate.csv | 
    Select-Object -Property  @{
        Name = "DateCreated"
        Expression = {
            ([datetime]$_."Date Created").ToString("yyyy-MM-d")
        }
    }, ID, "Email Address", "First Name", "Last Name" # | Export-Csv ...

As for what you tried. . .you're basically de-selecting all your properties when piped to Foreach-Object then trying to select what's not already there; hence why you get an empty csv.

answered Jan 19, 2022 at 19:05

Abraham Zinala

4,7244 gold badges12 silver badges32 bronze badges

1 Comment

jarod Over a year ago

ooooooooh that makes sense. Thank you!

Collectives™ on Stack Overflow

PowerShell -- Import csv, reformat date values, export csv

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related