×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Import-Excel and column Format

Import-Excel and column Format

Import-Excel and column Format

(OP)
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:

CODE --> powershell

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

RE: Import-Excel and column Format

Hi,

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Import-Excel and column Format

(OP)
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?

RE: Import-Excel and column Format

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Import-Excel and column Format

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Import-Excel and column Format

Try something like

CODE -->

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

#OK, now manipulate the destination workbook
$excel = Open-ExcelPackage -Path $outputExcelFile

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

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


or the fractionally shorter

CODE -->

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

#OK, now manipulate the destination workbook
$excel = Open-ExcelPackage -Path $outputExcelFile

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

$workSheet.DeleteRow($startRow, $countRows) 
                                 
Close-ExcelPackage -ExcelPackage $excel 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close