powershell function for convert to number

Question

Im using powershell to open two excel workbook and copy data from one into another, the data I'm importing contains both text and numeric data. After it's been imported, everything is treated as text. i have two columns: A and B A has the following: 01234567 should get converted into 1234567 B has the following: 01234567.05 should get converted into 1234567.05

Of course I could manually do the "Convert to Number" function, but this import is done automatically so no manual action need to be performed. (another way to do it manually is to use the Text-to-column)

This is not working, not even manually, if i try to make the conversion using the formating function, its not doing anything Worksheets("Sheet1").Range("A2:A5000").NumberFormat = "0000000"

i have more then 5000 rows, i was hoping i could apply a function like: Worksheets("Sheet1").Range("A2:A5000").ConvertToNumber — Kay Nix
– Kay Nix, Commented Apr 22, 2015 at 11:31

David Brabant · Accepted Answer · 2015-04-22 15:44:04Z

1

$csvData = @"
A,B
01234567, 01234567.05
-123, 00045.06
"@

# Or Import-Csv <yourpath> -delimiter <yourdelimiter> in your case
$csv = ConvertFrom-Csv $csvData -delimiter ','

Write-Host ""
Write-Host "Before conversion"
$csv

Write-Host ""
Write-Host "After conversion"


# Or Import-Csv <yourpath> -delimiter <yourdelimiter> | %{ etc... in your case
$csv = ConvertFrom-Csv $csvData -Delimiter ',' | %{
 new-object PSObject -prop @{
    A = [int] ($_.A);
    B = [double] ($_.B);
  }
}

$csv

edited Apr 22, 2015 at 15:44

answered Apr 22, 2015 at 9:56

David Brabant

44k16 gold badges93 silver badges119 bronze badges

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

Comments

MonkeyDreamzzz · Accepted Answer · 2015-04-22 09:22:44Z

0

In Powershell there are several ways to do conversions to an integer. (Powershell does not have a data type number)

$myInt = [int]"4527427"
$myInt = [int]::Parse("4527427")    
$myInt = [System.Convert]::ToInt32("4527427")

edited Apr 22, 2015 at 9:22

answered Apr 22, 2015 at 8:49

MonkeyDreamzzz

4,4381 gold badge43 silver badges40 bronze badges

Collectives™ on Stack Overflow

powershell function for convert to number

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