CODEWITHSUNDEEP
powershell
Patrik Živčak
Patrik Živčak

Reputation: 15

How cut off all value in column under -Delimiter -Header 'xxx'

My important part of code (this works):

$getFile = Get-Content $file |
           ConvertFrom-Csv -Delimiter ";" -Header "Printer","Model","MAC","IP_adresa","ID_terminalu" |
           Sort-Object -Property MAC, IP_adresa |
           Group-Object MAC |
           ForEach {$_.Group | Select-Object -First 1} |
           ConvertTo-Csv -NoTypeInformation |
           % {$_.Replace('"','')} |
           Select-Object -SkipLast 1

My header in the CSV is: "Printer", "Model", "MAC", "IP_adresa", "ID_terminalu". Problem is that in some specific rows the field "MAC" contains two MAC adresses separated by ,, for example:

MAC
00-1B-A9-85-88-1A,00-22-58-5E-7D-31

After using the script I get output from these specific rows like this:

Printer      Model        MAC                 IP_adresa        ID_terminalu
TCZ176100    Brother_MFC  00-1B-A9-85-88-1A   00-22-58-5E-7D-31 192.168.x.x

In this specific row the IP adress is shifted by one position and MAC is here 2x and it's bad.

I need to cut off all rows only for MAC header on 17 characters == length one MAC adress. I try really many different ways but NONE WORKED. I don't know how I get values under specific header (when headers have 5 attributes) and edit one specific from them.

enter image description here

I tried for example this (doesn't work):

$getFile = (Get-Content $file | ConvertFrom-Csv -Delimiter ";" -Header 'Tiskarna','Model','MAC','IP_adresa','ID_terminalu') |
           select Tiskarna, Model, @{Name="MAC".Substring(0,16);Expression={$_."MAC"}}, IP_adresa, ID_terminalu |
           Sort-Object -Property MAC, IP_adresa |
           Group-Object MAC |
           ForEach {$_.Group | Select-Object -First 1} |
           ConvertTo-Csv -NoTypeInformation |
           % {$_.Replace('"','')} |
           Select-Object -SkipLast 1

Upvotes: 1

Views: 253

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200393

Assuming you want only the first of the MAC addresses you should do something like this:

$headers = 'Printer', 'Model', 'MAC', 'IP_adresa', 'ID_terminalu'

$csv = Import-Csv -Delimiter ';' -Header $headers

foreach ($row in $csv) {
    if ($row.MAC.Contains(',')) {
        $row.MAC = $row.MAC.Split(',')[0]
    }
}

$csv | Sort-Object MAC, IP_adresa | ...

Upvotes: 1

Related Questions