Extract info from csv based on specific pattern with PowerShell
Hi,
I have an csv file with "Date, Body" column.
In body column I have this pattern for value:
Report Id: ce444534-18de-421e-a9b3-8e
This email was automatically generated by the Generate Incident Report action.
Message Id: <277339844.68.158248444263
Sender: user@domain
Subject: Need info
Recipients: user02@domain
To: user02@domain
To: user03@domain
Severity: Low
Override: No
False Positive: No
Rule Hit: TEST
How can I extract just these informations to csv file with PowerShell?:
Sender: user@domain
Subject: Need info
Recipients: user02@domain
To: user02@domain
To: user03@domain
Thanks
I have an csv file with "Date, Body" column.
In body column I have this pattern for value:
Report Id: ce444534-18de-421e-a9b3-8e
This email was automatically generated by the Generate Incident Report action.
Message Id: <277339844.68.158248444263
Sender: user@domain
Subject: Need info
Recipients: user02@domain
To: user02@domain
To: user03@domain
Severity: Low
Override: No
False Positive: No
Rule Hit: TEST
How can I extract just these informations to csv file with PowerShell?:
Sender: user@domain
Subject: Need info
Recipients: user02@domain
To: user02@domain
To: user03@domain
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK, it works also with your file.
ASKER
Humm good but ideally it's to be able to export to csv file for processing:-)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice : )
Add one parameter for Export-Csv to be better radable by Excel:
} | Export-Csv -NoTypeInformation -Path $outFile -Delimiter ';'ASKER
Wow, I'm not so familiar like you guys but could you please explain what is in bold here below:
Switch -RegEx ($_.Body -split '\r?\n') {
'^(?<Key>Sender|Subject):\
$out.($Matches['Key']) = $Matches['Value']}
Switch -RegEx ($_.Body -split '\r?\n') {
'^(?<Key>Sender|Subject):\
$out.($Matches['Key']) = $Matches['Value']}
$_.Body -split '\r?\n'
This is a RegEx will split the body at either CR (\r) (the ? allows it to not be there) and LF/NewLine (\n).
'^(?<Key>Sender|Subject):\
This matches any line starting with either Sender or Subject. The (?<Name>) is a "named group" that, if matched, can be addressed by its name in $Matches, instead of having to use the numeric index.
$out.($Matches['Key']) = $Matches['Value']}
This sets the output object's properties. If "Subject" was matched in the line, then the property Subject (its name is now in $Matches['Key']) will be set to whatever followed after the ':' (which is in $Matches['Value'])
Welcome to Regular-Expressions.info
https://www.regular-expressions.info/
This is a RegEx will split the body at either CR (\r) (the ? allows it to not be there) and LF/NewLine (\n).
'^(?<Key>Sender|Subject):\
This matches any line starting with either Sender or Subject. The (?<Name>) is a "named group" that, if matched, can be addressed by its name in $Matches, instead of having to use the numeric index.
$out.($Matches['Key']) = $Matches['Value']}
This sets the output object's properties. If "Subject" was matched in the line, then the property Subject (its name is now in $Matches['Key']) will be set to whatever followed after the ':' (which is in $Matches['Value'])
Welcome to Regular-Expressions.info
https://www.regular-expressions.info/
ASKER
Thanks a lot guys for your help!:-)
Post your CSV as attachment.