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

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.

CodePudding user response：

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

CodePudding user response：

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.