Ad Clicks : Ad Views : Ad Clicks : Ad Views : Ad Clicks : Ad Views : Ad Clicks : Ad Views : Ad Clicks : Ad Views : Ad Clicks : Ad Views : Ad Clicks : Ad Views : Ad Clicks : Ad Views : Ad Clicks : Ad Views :
TheServerGeeks

IT with everything IT

[How To] Convert txt logs to CSV

/

Have you ever worked on Exchange servers? If so, you might have come across message tracking which is done to troubleshoot mail send and receive related issues. If Yes, this article might be helpful for you.

I have worked on Exchange server related issues for over 3 years and i have traced message logs hundreds of times in my IT career. But recently have traced logs for multiple users and exported using Out-File and to text file.

Later i got a request to export the logs in CSV format. As the request was for multiple users, exporting the logs again will consume more memory and server performance will be reduced. Hence, i wanted to convert the existing logs in text format to CSV and wrote below script which can convert logs from text file and export to csv file. 

Below is Log format which i had earlier in text and the converted CSV file.

Below is the script which I wrote to convert this. Hope it helps you in your daily work. Download and save this as .Ps1 file and run it in PowerShell

Param( 
$textlogfile = "$HOME\Desktop\Logs.txt"
)
$collection=@()
###trim the white spaces lines from the structured text########
$content = (gc $textlogfile ) | ? {$_.trim() -ne "" }
Calculate the number of structured fields, in this case its 9, so $i=$i+9
for($i=0;$i -lt $content.length;$i=$i+5)
{
$Timestamp = $content[$i]
$Time=$Timestamp.split(":")[1].trim()
$Sender = $content[$i+1]
$Send=$Sender.split(":")[1].trim()
$MessageSubject = $content[$i+2]
$MessSub=$MessageSubject.split(":")[1].trim()
$Recipients = $content[$i+3]
$Recip=$Recipients.split(":")[1].trim()
$EventID = $content[$i+4]
$Event=$EventID.split(":")[1].trim()
$coll = "" | select Timestamp,Sender,MessageSubject,Recipients,EventID
$coll.Timestamp = $Time
$coll.Sender = $Send
$coll.MessageSubject = $MessSub
$coll.Recipients = $Recip
$coll.EventID = $Event
$collection +=$coll
}
$collection | export-csv $HOME\Desktop\Converted-Logs.csv -notypeinformation

2 Comments

  1. Today, while I was at work, my cousin stole my iphone and tested to see if it can survive a twenty five foot drop, just so she can be a youtube sensation. My apple ipad is now destroyed and she has 83 views. I know this is totally off topic but I had to share it with someone!

  2. Wow, incredible blog structure! How long have you ever been blogging for?
    you make running a blog look easy. The full glance of your website is magnificent, let alone the content material!

Leave a Comment

Your email address will not be published. Required fields are marked *

This div height required for enabling the sticky sidebar