Convert XLSX to CSV (SaveAs) changes date format

user_11963196 0
Viewed 405

I see this type of question asked and answered many times but not in Powershell and couldn't find the answer, so...

I'm converting an XLSX file to CSV using the following code :

$sheet = "SHEET1"
$xls = "C:\temp\XLSX.xlsx"
$csv = "C:\temp\CSV.csv"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False
$objExcel.DisplayAlerts = $False
$WorkBook = $objExcel.Workbooks.Open($xls)
$WorkSheet = $WorkBook.sheets.item("$Sheet")
$xlCSV = 6
$WorkSheet.SaveAs($csv,$xlCSV)
$objExcel.quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)

That conversion works fine except for dates. The XLSX file contains dates with format dd/MM/yyyy but after the above code saves my sheet to CSV those dates end up having the format M/d/yyyy. This of course causes problems later when I import my CSV and execute the rest of the script. Many dates also become "not recognized as a valid DateTime".

Can I add something to the code so those dates will remain in the same format ?

powershell
1 Answers

Save yourself the headache and get the ImportExcel module which makes it really easy. Also note, The ImportExcel module doesn't require Excel to run. This makes it great for server-side work where it may not be possible to install Excel etc.

I just wrote a quick test spreadsheet with rows like

number date
------ ----
     1 8/13/2021
     2 8/14/2021
     3 8/15/2021

One line to convert to csv:

Import-Excel C:\temp\TestDates.xlsx | 
Export-Csv C:\temp\TestDates.csv -NoTypeInformation

Since the cells were proper dates the Import-Excel command returned them and subsequently exported them like:

number date
------ ----
     1 8/13/2021 12:00:00 AM
     2 8/14/2021 12:00:00 AM
     3 8/15/2021 12:00:00 AM

Note: Import-Csv is not going to preserve the date typing. You can easily handle that on import, storing it as a string formatted as needed.

Import-Excel C:\temp\TestDates.xlsx | 
Select-Object Number,@{Name = 'Date'; Expression = { $_.Date.ToString('mm/dd/yyyy')}} | 
Export-Csv C:\temp\TestDates.csv -NoTypeInformation

Given your description there may be some malformed cells in the mix. You may have to check to see if they are convertible to a date on import, using the [DateTime]::Parse() or ::ParseExact() static methods.

user_47492640
Related