Import-Excel and column Format 1

[Avaya_Enjoyer]

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:

# 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?

 

[SkipVought]

Hi,

all columns lose their formating

This is because data like DATES in Excel are pure numbers that are viewed through a display filter.

For instance as I am writing it is 11/19/23 10:19 AM in North Texas, but Excel stores this value as 45249.43035.

If you want to EXPORT that value, or for that matter all the values in that column, you will have to instruct Excel that you want your value as
Col A as TEXT to be "dd:mm:yyyy"
Cols F & G as TEXT to be "0.00%"
Cols H # I as TEXT to be "mm:ss"

In fact you need every numeric column converted to TEXT.

I uploaded a brief example showing 2 sheets. Sheet1 represents your current worksheet. The second sheet represents the TEXT conversions required for the columns you disclosed.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Avaya_Enjoyer]

Hi

Thank you so much for your reply! I will have a look, i was kind of afraid this will be the general consensus. The challange here is that the excel files are generated by a system that nobody knows how exactly it works, we just keep it alive until a new solution will eventually take its place. I guess it's time to have a look at the backend and see if i can modify how the files are generated.

I understand this correctly, you don't see any way to work with the value stored by excel?

 

[SkipVought]

Yes, it all depends on how the data is stored.

For instance, you can store a date in a form that is visible and exactly as you entered it with a day, month and year.

So far everyone is happy.

But now someone else comes along and wants to manipulate that data, that is do something useful, just not simply look pretty.

TILT!!

Now we have problems because all we have is nice looking data that no one can sort or calculate with.

Hence comes systems like Excel that take all the work out of entering dates, for instance.

When you enter data like this in Excel, this is sort of what happens in the USA...

The user enters NUMERIC CHARACTERS, NSs (not numbers) from the keyboard.

The NCs are delimited by either the SLASH or DASH CHARACTER like
11/28 or 11-28
BTW just do this and hit ENTER

You will get different results if you ENTER
11/32 or 13/28

Excel "looks" at the data you have ENTERED and assumes that you intended a DATE in mm/dd, as long as the mm is 1-12 and the dd is within that month's days, converts your mm & dd with the current year to result in a DATE.

Your 13/28 simply remains as that NUMERIC CHARACTERS with the SLASH.

If you actually wanted thirteen twentyeights, then you would need to enter
=13/28
in which case Excel converts your characters to NUMBERS and does the math, stores the resulting NUMBER and CONVERTS that NUMBER to NUMERIC CHARACTERS so that you can actually SEE the result.

I belabor this explanation in order to emphasize that what's visible on the sheet needs to be analyzed in order to answer your question correctly.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

You could make a copy of your Excel source data with only the sheet(s) that apply and delete most rows and scrub confidential data.

Actually only need to see representative data from those numeric fields that you consider a problem.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[strongm]

Try something like

[COLOR=green]# Source Excel file[/color]
$inputExcelFile = "d:\downloads\deleteme\source.xlsx"
[COLOR=green]#Destination Excel file[/color]
$outputExcelFile = "d:\downloads\deleteme\dest.xlsx"
Copy-excelworksheet -SourceWorkBook $inputExcelFile -DestinationWorkBook $outputExcelFile 

[COLOR=green]#OK, now manipulate the destination workbook[/color]
$excel = Open-ExcelPackage -Path $outputExcelFile

[COLOR=green]# Get all the rows within used range in a worksheet[/color]
$workSheet = $excel.Workbook.Worksheets[1] [COLOR=green]#First sheet in workbook[/color]
$startRow = 5 [COLOR=green]#Row after Header[/color]
$endRow = $workSheet.Dimension.End.Row -1

[COLOR=green]# Loop backwards through the rows and delete them except the last row and the header row[/color]
for ($i = $endRow; $i -ge $startRow; $i--) {  
    $workSheet.DeleteRow($i)                              
}
Close-ExcelPackage -ExcelPackage $excel

or the fractionally shorter

[COLOR=blue][COLOR=green]# Source Excel file[/color]
$inputExcelFile = "d:\downloads\deleteme\source.xlsx"
[COLOR=green]#Destination Excel file[/color]
$outputExcelFile = "d:\downloads\deleteme\dest.xlsx"
Copy-excelworksheet -SourceWorkBook $inputExcelFile -DestinationWorkBook $outputExcelFile 

[COLOR=green]#OK, now manipulate the destination workbook[/color]
$excel = Open-ExcelPackage -Path $outputExcelFile

[COLOR=green]# Get all the rows within used range in a worksheet[/color]
$workSheet = $excel.Workbook.Worksheets[1] [COLOR=green]#First sheet in workbook[/color]
$startRow = 5 [COLOR=green]#Row after Header[/color]
$countRows= $workSheet.Dimension.End.Row  - $startrow

$workSheet.DeleteRow($startRow, $countRows) 
                                 
Close-ExcelPackage -ExcelPackage $excel[/color]