Forum Discussion

FigureItOut's avatar
FigureItOut
Copper Contributor
Apr 16, 2024

Extracting Excel Free Text Using PowerShell

Greetings,

We are trying to extract server names from our event logs that we exported to Excel. The following is an example of a cell which contains free text  

"Domain Controller Blocked Audit: Audit NTLM authentication to this domain controller.
Secure Channel name: SERVERNAME
User name: email address removed for privacy reasons
Domain name: NULL
Workstation name: Contosodesktop
Secure Channel type: 2"

 

We are trying to extract the values (server names) after the "Secure Channel Name:". I tried the following powershell script and it does not extract anything. Does anyone have thoughts? 

 

# Import the ImportExcel module
Import-Module ImportExcel

# Path to your Excel file
$excelFilePath = "C:\temp\contoso.csv"

# Output text file path
$outputFilePath = "C:\temp\extracted_server_names.txt"

# Regular expression pattern to match the server name
$pattern = "Secure Channel name:\s*(.*)"

# Initialize an empty array to store extracted server names
$serverNames = @()

# Read the Excel file
$data = Import-Excel -Path $excelFilePath

# Iterate through each cell in the Excel file
foreach ($row in $data) {
foreach ($cell in $row.Values) {
Write-Host "Processing cell: $cell"
# Check if the cell is a string and contains the desired text
if ($cell -is [string] -and $cell -match $pattern) {
# Extract the server name using regular expression
$serverName = $Matches[1].Trim()
# Append the extracted server name to the list
$serverNames += $serverName
Write-Host "Extracted server name: $serverName"
}
}
}

# Write the extracted server names to a text file
$serverNames | Out-File -FilePath $outputFilePath -Encoding utf8

# Display message indicating where the results are saved
Write-Host "Extracted server names saved to: $outputFilePath"

Resources