Powershell: Search CSV for string and output modified results

Question

Good morning everyone. Thank you for any help you might provide. I am new to powershell, so I'm learning as I go.

I'm looking for a script that would search a specific string, find the number associated with that string, and provide a modified result.

Example Input.csv:

Location Code Results
Virginia 7F3HU 33.5
North Carolina 5J5H4 28.8
Texas UG9T3 54.1
Virginia TF9CB 0.1
New York MT2L0 47.2
California 4CL3X 35.7

Search for specific string within Code (for example F3H, G9T and CL3) and output Results in whole number.

Example Output.csv:

Location Code Results
Virginia 7F3HU 34
Texas UG9T3 54
California 4CL3X 36

Thank you for any help.

Answer 1

As RichMatheisen said, It is way less complex to work with CSV files.
This was for the sake of trying something new. Now that I've done it, I'm sure I'll keep converting xlsx to csv.
It is possible to output in xlsx too, but I couldn't see any added value over csv

$codes = "F3H", "G9T", "CL3"  
$ExcelObj = New-Object -ComObject Excel.Application  
$ExcelObj.Visible = $false  
$ExcelFile = $ExcelObj.Workbooks.Open("C:\samples\Inputs.xlsx")  
$ExcelSheet = $ExcelFile.Sheets.Item("Sheet1")  
  
$Inputs = foreach ($row in $ExcelSheet.UsedRange.Rows){  
    [PSCustomObject]@{  
        Location = $row.Value2.split(',')[0]  
        Code = $row.Value2.split(',')[1]  
        Size = $row.Value2.split(',')[2]  
        In = $row.Value2.split(',')[3].Trim('%')  
    }  
}  
  
$Outputs = foreach ($line in $Inputs){  
    $lookup = $line.Code   
        foreach ($code in $codes){  
            if ($lookup -like "*$code*"){  
                [PSCustomObject]@{  
                    Location = $line.Location  
                    Code = $line.Code  
                    Size = $line.Size  
                    Total = [System.Math]::Round($line.In).ToString() + "%"  
                }  
            }          
        }  
}  
  
$Outputs | Export-CSV "c:\samples\Outputs.csv" -NoTypeInformation  

Answer 2

Thank you everyone for your assistance.
I hope pictures can really explain better than words.
I've included screenshots of the input (csv file) and desired output (csv or excel file).

Answer 3

this should do it:

$codes = "F3H", "G9T", "CL3"  
$Inputs = Import-Csv input.txt -Delimiter " "  
  
    $Outputs = foreach ($line in $Inputs)  {  
        $lookup = $line.Code   
            foreach ($code in $codes){  
                if ($lookup -like "*$code*"){  
                    [PSCustomObject]@{  
                        Location = $line.Location  
                        Code = $line.Code  
                        Results = [System.Math]::Round($line.Results)  
                    }  
                }                  
            }  
         }  
  
$Outputs | Export-CSV CodeResult.csv -NoTypeInformation  

Also, the csv should be sanitized before run or use other separator than space, as locations with space in the name will break the results

edit: sorry, had the question open and didn't refresh before posting. Last answer by RichMatheisen-8856 will do.

Answer 4

Incomplete descriptions yield incomplete results.

The script retains pretty much the same structure:

$codes = "F3H", "G9T", "CL3"  
Import-Csv c:\junk\locations.txt -Delimiter " " |  
    ForEach-Object {  
        $row = $_  
        $codes |  
            ForEach-Object {  
                if ($row.Code -match "$_") {  
                    [PSCustomObject]@{  
                        Location = $row.Location  
                        Code     = $row.Code  
                        Results  = [math]::Round($row.Results)  
                    }  
                }  
            }  
        } | Export-Csv c:\junk\CodeResult.csv -NoTypeInformation  

When a number contains a decimal point, the part to the left of the decimal point is referred to as the "characteristic" and the part to the right of the decimal point is referred to as the "manissa".

Answer 5

If the data within the "Code" is always the 2nd, 3rd, and 4th character of the string, and the "whole number" means the "characteristic" and that you want to ignore the "mantissa", then this should work:

$codes = "F3H", "G9T", "CL3"  
Import-Csv c:\junk\locations.txt -Delimiter " " |  
    ForEach-Object {  
        $code = $_.Code.Substring(1,3)  
        if ($codes -contains $code){  
            [PSCustomObject]@{  
                Location = $_.Location  
                Code = $_.Code  
                Results = [int]$_.Results  
            }  
        }  
    } | Export-CSV c:\junk\CodeResult.csv -NoTypeInformation