Percentage value get rounded when exporting from excel to csv

Question

I need a script to convert Excel data files to a CSV format. I used the following snippet of code. The code works however one of the columns in the data is a percentage and the resulting csv file has a rounded value of the percentages (4.55914% -> 4.56%).

  $extension = ((Split-Path $FilePath -Leaf).Split('.'))[1]
  if($extension -eq "xls" -or $extension -eq "xlsx") {
        Write-Host "Converting to CSV: $FilePath" -ForegroundColor Yellow
        $Excel = New-Object -ComObject Excel.Application
        $wb = $Excel.Workbooks.Open($FilePath, $false, $true)
        
        #$wb.WorkSheets(1).Columns.Item(3).NumberFormat = "Text"
        Write-Host (( $wb.WorkSheets(1).Columns.Item(3).NumberFormat )| Format-List | Out-String)
        
        $fileName = Split-Path $FilePath -Leaf
        $tempFile = Join-Path $tempFilesDir $fileName 
        $FilePath = $tempFile -replace $extension, "csv"
        
        $ws = $wb.Worksheets.Item(1)
        $Excel.DisplayAlerts = $false
        
        $ws.SaveAs($FilePath, 6)
        Write-Host "CSV File Saved: $FilePath" -ForegroundColor Green
        $Excel.Quit()
    }

I tried updating the column's number format first $wb.WorkSheets(1).Columns.Item(3).NumberFormat = "@#.########%" However that threw this error Unable to set the NumberFormat property of the Range class. I tried to solve this error to by adding

$wb.WorkSheets(1).Protect('',0,1,0,0,1,1,1)
$wb.WorkSheets(1).Columns.Item(3).NumberFormat = "@##.######%"

This did not work and gave me the same error. Next, I tried $wb.WorkSheets(1).Columns.Item(3).NumberFormat = "Text" This did not give me an error but instead converted all of my values to T1900xt I have no idea why this would happen.

Are there any other solutions I can try before resorting to looping through the file?

Unrelated side note. `Split-Path` has a `-Extension` parameter. And you can use `$extension -in ('.xls','.xlsx')` — Chad Baldwin, May 23 '23 at 16:30
@BigBen "@0.000000%" gave me to same 'Unable to set the NumberFormat property of the Range class error' — Ben, May 23 '23 at 16:32
@BigBen, you are correct. without the '@' worked. I was under the impression that '@' denoted a formatting string. Thank you — Ben, May 23 '23 at 16:36
`@` is the number format for Text. It doesn't belong in numeric formats. — BigBen, May 23 '23 at 16:36

score 1 · Accepted Answer · answered May 23 '23 at 16:39

1

Your number format is incorrect:

.NumberFormat = "@##.######%"

The @ denotes the Text format string and does not belong in a numeric format string.

Use:

.NumberFormat = "0.000000%"

You could see this thread for other format options.

answered May 23 '23 at 16:39

BigBen

46,229
7
24
40

Percentage value get rounded when exporting from excel to csv

1 Answers1