Forum Discussion
Extracting Data from Free text
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"
1 Reply
- Patrick2788Silver Contributor
If those are line breaks in the data, you could use this:
=LET( delim, "Secure Channel name: ", line_break, CHAR(10), TEXTBEFORE(TEXTAFTER(A1, delim), line_break) )