Strict Pattern Matching

PowerShell Help
1

Hello,

I am pattern matching with regex using Select-String -Pattern ‘expression’. I am matching within a csv.

My issue is, the expression matches both MAC Addresses (desired) and date/time (not desired). I only wish to match the MAC address for a -replace within the csv.

Here is the code…

$csv = Get-Content E:\path.csv | Select-String -Pattern '([0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2})' | ForEach-Object {$_-replace ':',''} | Out-File -FilePath E:\path.csv -Encoding ascii

I doubled up on the expression in hopes that it would provide a strict match, but the colons are still being removed from the date/time column, and I want to retain those.

Is the a Powershell way to only select the MAC Address column, remove the colons, but still Outfile the full data csv?

Thank you!

2

Try processing just the column you want versus trying to parse a row of data:

$csv = Get-Content E:\path.csv

$newCsv = foreach ($line in $csv) {
    $line.MACAddress = $line.MACAddress -replace ":", ""
}

$newCsv | Export-CSV -FilePath E:\updated_mac.csv -Encoding ascii

or

#Create another column with updated mac
$csv | 
Select *, @{Name="UpdatedMACAddress";Expression={$_.MacAddress -replace ":", ""}}


$newCsv | Export-CSV -FilePath E:\updated_mac.csv -Encoding ascii
3

Hi Rob,

Thank you for you answer. When I do Get-Content mycsv, it shows the MAC Address column header as ,“MAC Address”,.

But using Foreach ($line in $mycsv) {$line.“MAC Address” …}, or {$line.‘“MAC Address”’…} both tell me that, The property “MAC Address” cannot be found in this object.

4

Sorry, was on the first cup of coffee. You need to do Import-CSV, not Get-Content:

$csv = Import-CSV -Path E:\path.csv
5

Hi Rob!

Now I end up with an empty csv. Here is the script…

$csv = Import-CSV path\file.csv

$replace = foreach ($line in $csv) {
    $line."MAC Address" = $line."MAC Address" -replace ":",""
    }

$replace | Export-Csv path\newfile.csv -Encoding ascii

No errors, and just running $csv after the import looks like a bunch of separate objects, with each column as a property. This is what I would expect.

Why the blank csv on Export?

6

I’m not sure if that is an example, but that is not a valid path provided to Import-CSV.

7

Ha, no, it is ‘paraphrasing’. Sorry.

The paths are fine as the csv does import and it does export. It is just empty on the export. This should look better…

$csv = Import-CSV E:\folder\file.csv

$replace = foreach ($line in $csv) {
    $line."MAC Address" = $line."MAC Address" -replace ":",""
    }

$replace | Export-Csv E:\folder\file.csv -Encoding ascii -NoTypeInformation
8

The foreach is updating the line in the CSV, so this should work:

$csv = Import-CSV E:\folder\file.csv

foreach ($line in $csv) {
    $line."MAC Address" = $line."MAC Address" -replace ":",""
}

$csv | Export-Csv E:\folder\file.csv -Encoding ascii -NoTypeInformation
9

That did it, thank you very much!

That makes sense. I appreciate your help. From your answers and viewing the results of Get-Content vs Import-CSV, I learned:

Get-Content will dump all of the text and the entire contents will be scanned for a match.

Import-CSV will create objects with properties out of each row, and each property can be ‘selected’ or ‘worked’.

Also learned how to use foreach more effectively, no need to load it into a variable in this case.

Thanks again!