Powershell. Trying to loop using Import-csv

Question

I am converting code that currently parse a large csv file once for each agency. Below is what one line looks like:

Import-Csv $HostList |  Where-Object {$_."IP Address" -Match "^192.168.532.*"  -or $_Domain -eq     "MYDOMAIN"`
 -and (get-date $_.discovery_timestamp) -gt $PreviousDays} | select-object Hostname","Domain","IP Address","discovery_timestamp","some_version" | `
 Export-Csv -NoTypeInformation -Path $out_data"\OBS_"$Days"_days.txt" 
 write-host "OBS_DONE"

I have about 30 of these. I want to parse the csv file once, possibly using foreach and import.csv. I thought I could do something like:

$HostFile = Import-csv .\HostList.csv 
foreach ($line in $HostFile)
{
Where-Object {$_."IP Address" -Match "^172.31.52.*"} 
write-host $line
#| Export-Csv -NoTypeInformation -Path H:\Case_Scripts\test.csv

I've tried many permutations of the above, and it never is matching on the "Where-Object" like it does on the example functioning script above.

Any guidance and learning opportunities are appreciated.

TheMadTechnician · Accepted Answer · 2014-09-05 14:55:55Z

2

My good man, you need to be introduced to the Switch cmdlet. This will sort for all of your companies at once.

$CompanyA = @()
$CompanyB = @()
$CompanyD = @()
$Unknown = @()    
Switch(Import-CSV .\HostList.csv){
    {(($_."IP Address" -match "^192.168.532.*") -or ($_Domain -eq "CompanyA.com")) -and ((get-date $_.discovery_timestamp) -gt $PreviousDays)} {$CompanyA += $_; Continue}
    {(($_."IP Address" -match "^192.26.19.*") -or ($_Domain -eq "CompanyB.net")) -and ((get-date $_.discovery_timestamp) -gt $PreviousDays)} {$CompanyB += $_; Continue}
    {(($_."IP Address" -match "^94.8.222.*") -or ($_Domain -eq "CompanyC.org")) -and ((get-date $_.discovery_timestamp) -gt $PreviousDays)} {$CompanyC += $_; Continue}
    default {$Unknown += $_}
}
$CompanyA | Export-Csv $out_data"\CompanyA"$Days"_days.txt" -NoType
$CompanyB | Export-Csv $out_data"\CompanyB"$Days"_days.txt" -NoType
$CompanyC | Export-Csv $out_data"\CompanyC"$Days"_days.txt" -NoType
If($Unknown.count -gt 0){Write-Host $Unknown.count + " Entries Did Not Match Any Company" -Fore Red
$Unknown}

That will import the CSV, and for each entry try to match it against the criteria for each of the three lines. If it matches the criteria in the first ScriptBlock, it will perform the action in the second ScriptBlock (add that entry to one of the three arrays I created first). Then it outputs each array to it's own text file in CSV format as you had done in your script. The ;Continue just makes it so it stops trying to match once it finds a valid match, and continue's to the next record. If it can't match any of them it will default to adding it to the Unknown array, and at the end it checks if there are any in there it warns the host and lists the unmatched entries.

Edit: Switch's -RegEx argument... Ok, so the purpose of that is if you want a simple regex match such as:

Switch -regex ($ArrayOfData){
    ".*@.+?\..{2,5}" {"It's an email address"}
    "\d{3}(?:\)|\.|-)\d{3}(?:\.|-)\d{4}" {"It's a phone number"}
    "\d{3}-\d{2}-\d{4}" {"Probably a social security number"}
}

What this doesn't allow for is -and, or -or statements. If you use a scriptblock to match against (like I did in my top example) you can use the -match operator, which performs a regex match by default, so you can still use regex without having to use the -regex argument for Switch.

edited Sep 5, 2014 at 14:55

answered Sep 4, 2014 at 22:45

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bruce Over a year ago

Holy smokes! ..and from a MadTechnican at that! I am off to read about this magic. Thank you.

Bruce Over a year ago

Reading the get-help, I see: PS> switch -Regex ("fourteen") { 1 {"It is one."; Break} 2 {"It is two."; Break}

Bruce Over a year ago

Reading the get-help, I see: PS> switch -Regex ( "fourteen") { 1 {"It is one."; Break} 2 {"It is two."; Break} I want to use a regex, but not getting how to insert it. I want use: $CompanyA = @() {(($_."IP Address" -match "^172\.2[1,2]\.\d{1,3}\.\d{1,3}") And figure I need to add the -RegEx somewhere. I've tried in various places but PS laughs at my attempts.

Bruce Over a year ago

Figured it out: Switch -regex (Import-CSV .\HostList.csv ){ I think I am in business. Thanks Again.

TheMadTechnician Over a year ago

In the context that I used Switch you don't need to use the -RegEx argument. I used a scriptblock (which works like the first part of an If statement) to match against, and in that I used the -match operator that functions as a regex match by default.

tyork · Accepted Answer · 2014-09-04 22:27:38Z

1

Where-Object should have a collection of objects passed to it. In your second script block this isn't happening, so in the filter the $_ variable will be empty.

There are a couple of ways to fix this - the first one that comes to mind is to replace Where-Object with an 'if' statement. For example

if ($line."IP Address" -Match "^172.31.52.*") { write-output $line }

answered Sep 4, 2014 at 22:27

tyork

564 bronze badges

2 Comments

Bruce Over a year ago

Tyork, Thank you for your reply. You guys are great.

Bruce Over a year ago

Thank you for your reply. Indeed this example works. Next, to figure why mine works as shown in the functioning example, but not in my re-write.

Collectives™ on Stack Overflow

Powershell. Trying to loop using Import-csv

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related