Powershell Export-csv having problem converting date columns

Question

I have excel file which has 3 columns and the data is like below

Business_Key	Business_Type	Start_Date
1000	Service	1/1/2007
1001	Other	12/1/2008

I am trying to convert this to csv file using the import-excel and export-csv functions, when the conversion is done, i see that the start_date is getting converted to number as below

Business_Key	Business_Type	Start_Date
1000	Service	39083
1001	Other	39783

I have used the below powershell script, this works fine except the date column is getting converted to number:

$ens = Get-ChildItem "Z:\Informatica\IICS\ProjectFolders\Dev\Commerce\CenterIC\" -filter Customer*.xlsx
$OutFile= (Get-ChildItem "Z:\Informatica\IICS\ProjectFolders\Dev\Commerce\CenterIC\Customer*.xlsx").BaseName
foreach($file in $ens)
{
   Import-Excel "Z:\Informatica\IICS\ProjectFolders\Dev\Commerce\CenterIC\$file" -Startrow 13 -HeaderName 'Business_Key','Business_Type','Start_Date' | Select-Object Business_Key,Business_Type,Start_Date | Export-Csv Z:\Informatica\IICS\ProjectFolders\Dev\Commerce\CenterIC\$file.csv -noTypeInformation -Encoding utf8 -UseCulture -verbose}

How do i maintain the same format as excel into the csv? i am new to powershell.

Image with the answer provided

Answer 1

I assume you are opening your newly saved .csv file in Excel as Excel stores dates as numbers and you are seeing a numeric representation of the dates - each date is expressed as the number of days after 1st January 1900

If I type 39083 into an Excel cell and format that cell as a date, I will see 1st Jan 2007 (formatted in whatever way I chose)

So I think you are hitting a formatting issue.The following slightly tweaked version of your code reads back in the saved CSV file and confirms the Start_date column is in fact a date:

$files = Get-ChildItem "C:\test" -filter Book1*.xlsx

foreach($file in $files)
{
    $newCSVFileName = "C:\test\$($file.BaseName).csv"

    Import-Excel $file.FullName -Startrow 2 -HeaderName 'Business_Key','Business_Type','Start_Date' | 
        Select-Object Business_Key,Business_Type,Start_Date | 
            Export-Csv $newCSVFileName -noTypeInformation -Encoding utf8 -UseCulture -verbose

    # read in the CSV using powershell and view the start dates
    Import-Csv -Path $newCSVFileName | Select-Object -Property Start_Date
}