I need to change commas to dot in an excel file using Powershell

Fusionized 21 Reputation points
2020-09-03T11:45:32.857+00:00

I need someone to help me solve this problem. I need to write a powershell script which changes the commas of the excel file to dots. The commas are between the numbers.
The table is built up like this:

user name user surname count count etc
example example 13,00 5,00 etc

I don't have any idea how to do it. I hope someone can help me.

Thank you in advance

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
4,619 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 18,351 Reputation points Microsoft Employee
    2020-09-04T03:32:04.793+00:00

    Hi,
    The script could be like this

    $File = "D:\test.xlsx"
    $Excel = New-Object -ComObject Excel.Application
    $Workbook = $Excel.Workbooks.Open($File)
    $Worksheet = $Workbook.Worksheets.Item(1)
    [void]$Worksheet.Cells.Replace(",",".")
    
    $Workbook.Save()
    $Workbook.Close()
    $Excel.Quit()
    [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel)
    

    This article is helpful.
    https://www.zacoding.com/en/post/powershell-excel-replace-text/

    Best Regards,

    Ian

    Please remember to "Accept Answer" and upvote if the reply is helpful.


  2. Rich Matheisen 34,951 Reputation points
    2020-09-04T15:03:20.757+00:00

    Add this to the code given by IanXue-MSFT:

    $worksheet.Columns("YourColumnIDHere").NumberFormat = "General"
    
    No comments

  3. Rich Matheisen 34,951 Reputation points
    2020-09-03T17:54:17.083+00:00

    If you're asking about how Excel represents decimal numbers, or how you can change that, see here: change_the_decimal_point_to_a_comma_or_vice_versa.html

    No comments