excelpowershellexport-csv

Powershell Export-csv having problem converting date columns


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


Solution

  • 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)

    enter image description here

    enter image description here

    enter image description here

    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
    }
    

    enter image description here