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.
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
}