Powershell - Cell Format an Excel Column to Decimal Places

Question

I have a powershell script to convert an excel spreadsheet into a csv file.

One of the columns within the spreadsheet does not show decimals so in the final CSV output it will say 15, where the actual value is 14.55.

I need to convert the column during script runtime to show 4 decimal places.

I've tried this line but it isn't working for me:

$sheet.columns("H").numberformat = "0.0000"

Any ideas if my line is not right or other ways to do this, thanks.

full script is here:

# Set Folder Paths
$processFolder = "C:\Powershell\Excel\Process\"
$outFolder = "C:\Powershell\In\"
$archiveFolder = "C:\Powershell\Excel\Archive\"
$errorFolder = "C:\Powershell\Excel\Error\"

# Get all .xlsx files in Process folder and loop
$ens = Get-ChildItem $processFolder -filter *.xlsx
foreach($e in $ens)
{
& {
    $sourceFile = $processFolder + $e.Basename + ".xlsx"
    $outFile = $outFolder + $e.Basename + "_" + [DateTime]::Now.ToString("yyyyMMdd-HHmmss") + ".csv"
    $archiveFile = $archiveFolder + $e.Basename + "_processed_" + [DateTime]::Now.ToString("yyyyMMdd-HHmmss") + ".xlsx"
    $errorFile = $errorFolder + $e.Basename + "_error_" + [DateTime]::Now.ToString("yyyyMMdd-HHmmss") + ".xlsx"

    $excelApplication = New-Object -ComObject Excel.Application

    try {
        $excelApplication.visible = $false;
        $excelApplication.DisplayAlerts = $false

       $workbook = $excelApplication.Workbooks.Open($sourceFile)
       $sheet = $workbook.Sheets.Item("SUMMARY") # Activate the worksheet
   
       $sheet.columns("H").numberformat = "0.0000" # change formatting of column to show decimals so they appear in CSV file
   
       [void]$sheet.Cells.Item(1, 1).EntireColumn.Delete() # Delete the first column

       for ($i=1; $i -le 12; $i++) # Delete the first 12 rows as this is header data
       {
        [void]$sheet.Cells.Item(1, 1).EntireRow.Delete()
       }

       $workbook.WorkSheets.item("SUMMARY").Activate()
       $workbook.SaveAs($outFile, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV) # save file to csv format
       $workbook.Close()

Move-Item -Path $sourceFile -Destination $archiveFile # Archive the Excel File with processing datetime stamp
    }
    catch   {
Copy-Item -Path $sourceFile -Destination $errorFile  # Move to Error folder the Excel File with error datetime stamp
   }
    finally {
$excelApplication.Quit()
        }
  }
}

Take a look at the Number Format Codes to see if they are helpful. Maybe try #.0000? — Ash
– Ash, Commented Oct 9, 2021 at 8:41
Thanks Ash, I couldn't get the number format working but have now simply changed to use "'@" so it is text and that works and also end product is csv file so OK to do this — Roger Clerkwell
– Roger Clerkwell, Commented Oct 9, 2021 at 9:09
I think the first question to ask yourself is: whether this is a PowerShell scripting issue or an Excel issue. In other words: what happens when you save this to a csv file nanually? — iRon
– iRon, Commented Oct 9, 2021 at 9:14

4b0 · Accepted Answer · 2022-06-03 02:41:45Z

1

Instead of the letter, excel reads the item reference as a number with column H being the 8th item.

Example:

$sheet.Columns.Item(8).NumberFormat="0.0000"

edited Jun 3, 2022 at 2:41

4b0

22.4k30 gold badges97 silver badges143 bronze badges

answered Jun 3, 2022 at 2:09

Guest1

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2022-09-09 21:28:52Z

0

I added 0,0 to add a comma to the number

$worksheetObject.Cells.Item($index,10).NumberFormat="0,0.00"

answered Sep 9, 2022 at 21:28

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

Collectives™ on Stack Overflow

Powershell - Cell Format an Excel Column to Decimal Places

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related