Find out Text data in CSV File Numeric Columns in Powershell

[BTrees]

Hell Everyone

I am very new in powershell. I have a requirement to code some tests in Powershell.
I am trying to validate a CSV file by finding out if there is any text value in my numeric fields.

This is my source data like this
ColA, ColB , ColC , ColD,
23 , 23 , ff , 100,
2.30E+01, 34, 2.40E+01, 23
df , 33 , ss , df
34 , 35 , 36 , 37

Required out put
ColA, ColC, ColD
2.30E+01, ff, df
df, 2.40E+01
, ss

I have tried
cls

function Is-Numeric ($Value) {
return $Value -match "^[\d\.]+$"
}

$arrResult = @()
$arraycol = @()

$FileCol = @("ColA","ColB","ColC","ColD") .. These are some of the specific columns that are numeric in file

$dif_file_path = "C:\Users\$env:username\desktop\f2.csv"

#Importing CSVs

$dif_file = Import-Csv -Path $dif_file_path -Delimiter ","

############## Test Datatype (Is-Numeric)##########

foreach($col in $FileCol)
{
foreach ($line in $dif_file) {

$val = $line.$col

$isnum = Is-Numeric($val)

if ($isnum -eq $false) {
$arrResult += $line.$col
$arraycol += $col

}
}
}
[pscustomobject]@{$arraycol = "$arrResult"}| out-file "C:\Users\$env:username\Desktop\Errors1.csv"
####################

I am not getting proper output as required with this file.
Any help in this regard?
Thanks

 

[fredericofonseca]

few things.

first your headers do not match the names you are using - by having it on the CSV with spaces between the commas the names no longer match.
e.g. "ColA" <> "ColA " -- notice the space. so doing a $line.$col will fail to find it

Second - assuming you don't use my function below you need to trim the values when checking for numeric - otherwise your code will return false where the columns contain a space - "123 " is false

third - exponential numbers are, as name says, numeric - e.g. 2.30E+01 is a valid numeric value (23 in this case)

as for getting the output exactly as you mention - you can loop through the array and output it - this you should be able to google or figure out yourself.

below the code I would use - it does deal with exponential numbers correctly (which your code does not)

function Is-Numeric2 ($Value) 
{
    if ($Value -eq $null)
    {
        return $false
    }
    else
    {
        try
        {
           $v=[double]$Value
           return $true
        }
        catch
        {
           return $false
        }
    }
}

$arrResult = @()
$arraycol = @()

$FileCol = @("ColA","ColB","ColC","ColD") #.. These are some of the specific columns that are numeric in file

$dif_file_path = "C:\temp\f2.csv"

#Importing CSVs

$dif_file = Import-Csv -Path $dif_file_path -Delimiter ","

############## Test Datatype (Is-Numeric)##########

foreach($col in $FileCol)
{
    foreach ($line in $dif_file) {

        $isnum = Is-Numeric2($line.$col)
        if ($isnum)
        {
            # output values just to show which ones were considered numerics and its conversion
            Write-Host "$val = $([double](([string]($val)).Trim()))"
        }

        if (!$isnum)
        {
            $arrResult += $line.$col
            $arraycol += $col
        }
    }
}

[pscustomobject]@{$arraycol = "$arrResult"}| out-file "C:\temp\Errors2.csv"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[BTrees]

Thank you very much Frederico for the solution. I got Ideas , so far the following works for me

$arrResult = @()
$columns = "ColA","ColB","ColC","ColD"
$dif_file_path = "C:\Users\$env:username\desktop\f1.csv"
$dif_file = Import-Csv -Path $dif_file_path -Delimiter "," |select $columns
$columns = $dif_file | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name'
foreach($row in $dif_file) {
foreach ($col in $columns) {
$val = $row.$col
$isnum = Is-Numeric($val)
if ($isnum -eq $false) {
$arrResult += $col+ " " +$row.$col
}}}
$arrResult | out-file "C:\Users\$env:username\desktop\Errordata.csv"

It gives me correct result as required. I need to exclude scientific notation if found.
Though correct result but out put is not in proper format likd
ColA ss
ColB 5.74E+03
ColA ss
ColC rrr
ColB 3.54E+03
ColD ss
ColB 8.31E+03
ColD cc
any idea to improve format? thanks