CODEWITHSUNDEEP
regexpowershellcsv
Filippo38
Filippo38

Reputation: 3

Powershell CSV Parse then move string of data from "Data" column into appropriate comma separated congruent columns

I need to parse the data column and move the appropriate string into the appropriate columns as indicated below. I need your help to do this. Regex anyone?

The CSV file:

"Data","Application","Event Date","Heartbeat Events","Audit Events","Action"
"Application One 05-MAR-2018 12 8 Follow-up required ",,,,,
"Application Two UK 05-MAR-2018 19 164 Follow-up required    ",,,,,
"Application South Africa Three 05-MAR-2018 17 41 Follow-up required ",,,,,

So each row finds itself in the Data column. There are 36 applications but they don't all throw alerts every day. The application string should be parsed into the "Application" column, the date string into the "Event Date" column, the next number belongs in the "Heartbeat Events" column, etc.

It has taken me weeks to get this far. This was a broken VB Script project that I undertook to bring to Powershell. This data I scraped from a local .htm file (Chrome). The original csv file was enormous with a lot of stuff in it. As you can see, I managed to filter it down to three or four lines.

Eventually, I will need to read a text file with the application names and application owner email addresses, and the script will send a message to those app owners that show "Follow-Up required" The script runs daily, seven days a week.

Upvotes: 0

Views: 221

Answers (1)

user6811411
user6811411

Reputation: 

## See the regex on https://regex101.com/r/gapKWC/1

$CSV = import-csv .\test.csv
$RE=[regex]"^(.*?) (\d{2}-[A-Z]{3}-\d{4}) (\d+) (\d+) (.+)$"

ForEach($Row in $CSV){
    If ($Row.Data -Match $RE){
        $Row.Application        = $Matches[1]
        $Row.'Event Date'       = $Matches[2]
        $Row.'Heartbeat Events' = $Matches[3]
        $Row.'Audit Events'     = $Matches[4]
        $Row.Action             = $Matches[5]
    }
}

$CSV

Sample output:

Data             : Application One 05-MAR-2018 12 8 Follow-up required
Application      : Application One
Event Date       : 05-MAR-2018
Heartbeat Events : 12
Audit Events     : 8
Action           : Follow-up required

Data             : Application Two UK 05-MAR-2018 19 164 Follow-up required
Application      : Application Two UK
Event Date       : 05-MAR-2018
Heartbeat Events : 19
Audit Events     : 164
Action           : Follow-up required

Data             : Application South Africa Three 05-MAR-2018 17 41 Follow-up required
Application      : Application South Africa Three
Event Date       : 05-MAR-2018
Heartbeat Events : 17
Audit Events     : 41
Action           : Follow-up required

Upvotes: 1

Related Questions