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
– Chad Baldwin, Commented May 23, 2023 at 16:30
@BigBen "@0.000000%" gave me to same 'Unable to set the NumberFormat property of the Range class error' — Ben
– Ben, Commented May 23, 2023 at 16:32
@ is the number format for Text. It doesn't belong in numeric formats. — BigBen
– BigBen, Commented May 23, 2023 at 16:36

BigBen · Accepted Answer · 2023-05-23 16:39:07Z

2

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, 2023 at 16:39

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Percentage value get rounded when exporting from excel to csv

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related