Avaya_Enjoyer
Systems Engineer
I will start by disclosing that powershell is usually not in my job's scope, i don't have a lot of experience with it.
I'm trying to write a script to:
1. Import an xlsx file from a folder
3. Convert to csv or save as excel (this is so i can delete all rows but the last one)
4. Upload it to an sftp via winSCP.
All of this works without issues, the only problem i'm having is that once i use the import-excel module to import an xlsx file all columns lose their formating. I am supposed to set columns "A" to (dd:mm:yyyy), "F" and "G" to percentages (0.00%) and "H" and "I" to a custom format (mm:ss).
Here is what i have so far:
While googling i saw a lot of examples which used the Set-Excelrange but there was no way to implement it myself. All examples are vastly more complicated than my use case, i'm sure i got it completely right as i could not fully interpret the various scripts i have looked at. I also tried various arguments such as -NoNumberConversions with no luck. What would your aproach be?
I'm trying to write a script to:
1. Import an xlsx file from a folder
3. Convert to csv or save as excel (this is so i can delete all rows but the last one)
4. Upload it to an sftp via winSCP.
All of this works without issues, the only problem i'm having is that once i use the import-excel module to import an xlsx file all columns lose their formating. I am supposed to set columns "A" to (dd:mm:yyyy), "F" and "G" to percentages (0.00%) and "H" and "I" to a custom format (mm:ss).
Here is what i have so far:
Code:
# Import Excel file
$inputExcelFile = "C:\Test File Structure\Test Destination\FileTest.xlsx"
$data = Import-Excel -HeaderRow 4 -Path $inputExcelFile
$outputCsvFile = "C:\Test File Structure\Test Destination\FileEX3.xlsx"
# Delete all rows except the last one
$data = $data[-1]
#Export as a CSV file
$data | Export-Excel -Path $outputCsvFile
While googling i saw a lot of examples which used the Set-Excelrange but there was no way to implement it myself. All examples are vastly more complicated than my use case, i'm sure i got it completely right as i could not fully interpret the various scripts i have looked at. I also tried various arguments such as -NoNumberConversions with no luck. What would your aproach be?