PowerShell解析CSV：将Data列数据拆分至对应列（求正则方案）

Got it, let's break down how to extract those target fields from your CSV's Data column using regular expressions. First, I'll start with a common pattern I've seen for such entries (if your actual Data format differs a bit, we can tweak the regex easily later).

Step 1: Assume a Typical Data Column Structure #

First, let's use realistic example entries for the Data column to base our regex on:

• "PaymentService (EMEA) - 2024-06-15: Heartbeat Events: 89, Audit Events: 14 - Action: System health check completed"
• "UserAuthAPIs - 2024-06-16: Heartbeat Events: 0, Audit Events: 217 - Action: Failed login attempts logged"
• "InventoryMgmt (APAC) - 2024-06-17: Heartbeat Events: 234, Audit Events: 56 - Action: Stock level updated"

These entries follow a consistent structure: [App Name (optional region)] - [Date]: Heartbeat Events: [number], Audit Events: [number] - Action: [description]

Step 2: The Regex Pattern #

Here's a named-group regex that will capture each field you need:

^(?P<Application>.+?) - (?P<Event_Date>\d{4}-\d{2}-\d{2}): Heartbeat Events: (?P<Heartbeat_Events>\d+), Audit Events: (?P<Audit_Events>\d+) - Action: (?P<Action>.+)$

Let's break down each part:

• (?P<Application>.+?): Non-greedy match to capture the application name (including any region in parentheses) until the first - separator.
• (?P<Event_Date>\d{4}-\d{2}-\d{2}): Explicitly matches dates in YYYY-MM-DD format. Adjust this if your date uses a different separator (e.g., \d{2}/\d{2}/\d{4} for MM/DD/YYYY).
• (?P<Heartbeat_Events>\d+): Captures one or more digits for the heartbeat event count.
• (?P<Audit_Events>\d+): Captures one or more digits for the audit event count.
• (?P<Action>.+): Greedily captures everything after Action: as the action description.

Step 3: Implement with Python (CSV + Regex) #

Here's a practical script to process your CSV file, extract the fields, and write a new CSV with the populated columns:

import csv
import re

# Compile the regex pattern for efficiency
parse_pattern = re.compile(r'^(?P<Application>.+?) - (?P<Event_Date>\d{4}-\d{2}-\d{2}): Heartbeat Events: (?P<Heartbeat_Events>\d+), Audit Events: (?P<Audit_Events>\d+) - Action: (?P<Action>.+)$')

# Input and output file paths
input_csv = "your_input_file.csv"
output_csv = "parsed_output.csv"

with open(input_csv, 'r', encoding='utf-8') as infile, open(output_csv, 'w', newline='', encoding='utf-8') as outfile:
    # Read the original CSV
    reader = csv.DictReader(infile)
    # Define output columns (include your target fields, exclude original Data column)
    output_fields = ['Application', 'Event Date', 'Heartbeat Events', 'Audit Events', 'Action'] + [col for col in reader.fieldnames if col != 'Data']
    writer = csv.DictWriter(outfile, fieldnames=output_fields)
    
    writer.writeheader()
    
    for row_num, row in enumerate(reader, start=1):
        data_text = row['Data'].strip()
        match_result = parse_pattern.match(data_text)
        
        if match_result:
            # Map captured groups to the correct column names
            extracted_data = {
                'Application': match_result.group('Application'),
                'Event Date': match_result.group('Event_Date'),
                'Heartbeat Events': match_result.group('Heartbeat_Events'),
                'Audit Events': match_result.group('Audit_Events'),
                'Action': match_result.group('Action')
            }
            # Merge with original row data (remove the Data column)
            final_row = {**row, **extracted_data}
            del final_row['Data']
            writer.writerow(final_row)
        else:
            # Handle rows that don't match the pattern (log a warning, write empty fields)
            print(f"Row {row_num}: Could not parse Data content: '{data_text}'")
            final_row = {**row}
            # Set empty values for the target fields
            for field in ['Application', 'Event Date', 'Heartbeat Events', 'Audit Events', 'Action']:
                final_row[field] = ''
            del final_row['Data']
            writer.writerow(final_row)

Step 4: Adjustments for Different Formats #

If your Data column has variations, tweak the regex accordingly:

• Different date format: Replace \d{4}-\d{2}-\d{2} with \d{2}/\d{2}/\d{4} (for MM/DD/YYYY) or \d{2}-\d{2}-\d{4} (for DD-MM-YYYY).
• Different separators: If the app name and date are separated by | instead of -, change - to \| in the regex.
• Optional event counts: If counts can be missing (unlikely, but possible), add ? after the digit group (e.g., (?P<Heartbeat_Events>\d+)?).

内容的提问来源于stack exchange，提问作者Filippo38