Author Posts

May 1, 2017 at 1:00 pm

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!

May 1, 2017 at 1:52 pm

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

May 1, 2017 at 6:08 pm

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.

May 1, 2017 at 6:31 pm

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

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

May 1, 2017 at 7:58 pm

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?

May 1, 2017 at 8:52 pm

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

May 1, 2017 at 8:58 pm

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

May 1, 2017 at 9:37 pm

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

May 2, 2017 at 1:01 pm

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!