0

My csv files contain percentage values with percent signs (yuck). I want to pull the %'s out and multiply the values by 0.01 so I can store it as a proper decimal.

At first I thought I'd split it up and do it in two steps:

  • remove the %'s
  • do math on the remaining values

But that's needlessly complicated, right? It gets me into trouble with values that weren't percents to begin with but look like them after removing the %'s (starting to think like a regex guy). I'm sure can do this in one regex replace.

I puzzled out a regex to find the values, but now I'm trying to do math on my replacement value and I'm running into trouble with using $1. I can't seem to get it to multiply by 0.01. I've tried a bunch of parentheses and [decimal] declarations, but can't get it to work.

Example of existing file:

1.9500%, 123.23, 0.0123%, "textvalue", 2.1450%, 2.89

Example of desired file (percent signs removed and math complete). The leading/trailing zeroes are included for clarity, but I don't technically need them:

0.019500, 123.23, 0.000123, "textvalue", 0.021450, 2.89

Here's my code:

$Files = Get-ChildItem *input.csv
foreach ($file in $Files)
    {
        $file |
        Get-Content | #assume that I can't use -raw
        % {$_ -replace '(\d+\.\d+)%', ("`$1" * 0.01)} | #THIS is my problem
        out-file output.csv -append -encoding ascii
    }
astraljack
  • 75
  • 5
  • Using what @briantist referenced... $a = @" 1.9500%, 123.23, 0.0123%, "textvalue", 2.1450%, 2.89 "@ # the replace here is because only the outer match is passed (as $args[0] and $args[1] does not exist) $callback = {0.01 * "$($args[0] -replace '(\d+\.\d+)%', '$1')" } [regex]::Replace($a,'(\d+\.\d+)%',$callback) – stevlars Jul 05 '16 at 22:44
  • Thanks @stevlars! I formatted your code and it works great. I hadn't used $args before and had to do some reading to make sense of it. – astraljack Jul 06 '16 at 15:10

0 Answers0