Converting the Excel properties to a Number Format

Question

I have this automation script that will take a .htm file and generate a custom object to be generated into a spreadsheet.

Unfortunately, one of the items in my object is a very long number, so when it does get exported to a .xlsx, it looks like this:

1.04511E+11

I understand that just changing the format to a number with no decimals is possible within Excel, but I wanted to know if there is a way I can change the format within my script; especially since this is intended to be an automated process.

Here is the segment of my script:

## Build custom object which allows for modification of the spreadsheet that will be generated
$xls = ForEach ($item in $spreadsheet | Where-Object {$PSItem.P4 -match '\w+'}) {
    [pscustomobject]@{
        ## Define the colums for the CSV file
        'MnsTransTy'      = $item.P1.TrimStart()
        'Del. Date'       = $item.P2
        'Time'            = $item.P3
        'Name 1'          = $item.P4
        'Purch.Doc.'      = $item.P5
        ## Remove white space
        'Cases'           = $item.P6.TrimStart()
        ## Remove white space
        'line'            = $item.P7.TrimStart()
        'Name'            = $item.P8
        ## Remove white space
        'Tot Pallet'      = $Item.P9.TrimStart()
    }
}

The item in question is P5. I am using the ImportExcel Module which is found here: https://github.com/dfinke/ImportExcel.

Any help on this would be greatly appreciated! Thanks in advance!

There may be several significant digits lost in the E number expression. This probably needs to be done in Excel before exporting. Or, possibly use a COM interface. — lit, Aug 31 '18 at 17:36
@lit Thanks for that! I also read that if I just put a ' character infront of the number, that the issue will also be resolved. I've tried that, but I get errors in the script. Maybe I am just typing it in wrong, but that is also another possibility. — Huddles18, Aug 31 '18 at 17:49
So you're say you want the values imported into Excel as text? What "automated process" is getting the data next, that you don't want to use a number? (since `1.04511E+11` is a valid number.) — ashleedawg, Aug 31 '18 at 18:53
I just know that adding a ' to it would show the full number. Unfortunately, I don't know the next steps! After my segment, it goes on to a developer team, which I know they could just import the number and remove the single quote. I was thinking of just creating a separate script to modify the spreadsheet. — Huddles18, Sep 05 '18 at 16:14

score 0 · Answer 1 · answered Sep 01 '18 at 02:33

This is probably happens because you are getting values from cells as string data type. You could try to specify explicitly the data type(Double in your case). Like so:

[pscustomobject]@{
        ## Define the colums for the CSV file
        'MnsTransTy'      = $item.P1.TrimStart()
        'Del. Date'       = $item.P2
        'Time'            = $item.P3
        'Name 1'          = $item.P4
        'Purch.Doc.'      = [Double]$item.P5
        ## Remove white space
        'Cases'           = $item.P6.TrimStart()
        ## Remove white space
        'line'            = $item.P7.TrimStart()
        'Name'            = $item.P8
        ## Remove white space
        'Tot Pallet'      = $Item.P9.TrimStart()
    }

Thank you for this! I just tried it out, and the spreadsheet has no change. I was wondering if maybe I can create a separate script that would run after this one, and be able to change it there? I don't know. Just throwing ideas out at this point. — Huddles18, Sep 05 '18 at 15:37

Converting the Excel properties to a Number Format

1 Answers1