Sunday, August 24, 2014

Convert Structured TEXT to CSV format

Hi Readers,

This is the second part of the Blog Article

http://msexchange.me/2014/08/19/search-and-delete-messages-from-user-mailboxes-exchange-2010/

In the above link, We got the output as structured text:



So we want to convert it in CSV format , below script has been written to do this job:

Just take care of values of $i as this is structured text & has 9 fields so added $i+9 and some fields have $i+2 , for example DisplayName is at number 2.(calculated from 0)

Extract the zip file from below link & run batch file

http://gallery.technet.microsoft.com/scriptcenter/Convert-Structured-TEXT-to-a8fc6065



 

Enter the Log.txt as parameter



Now two files will be created, one is output1 (this is created after removing spaces) & output.csv(this is actualcsv file that we wanted)



 



Script Code:


################################################################ 
# Author: Vikas Sukhija 
# Date: 08/20/2014 
# Description: Conver the structured text to csv 
# example:- exchange search and delete logs 
################################################################ 
 
Param
 
$textlogfile = $(Read-Host "enter the name of structured text file"
 

 
$collection=@() 
#########trim the white spaces lines from the structured text######## 
 
(gc $textlogfile| ? {$_.trim() -ne "" } | set-content .\output1.txt 
 
$content = Get-Content .\output1.txt 
 
######Calculate the number of structured fields, in this case its 9, so $i=$i+9 
 
for($i=0;$i -lt $content.length;$i=$i+9) 
 

 
$DisplayName = $content[$i+2] 
$Disp=$DisplayName.split(":")[1].trim() 
 
$Success = $content[$i+5] 
$Succep=$Success.split(":")[1].trim() 
 
$ResultItemsCount = $content[$i+7] 
$Resc=$ResultItemsCount.split(":")[1].trim() 
 
$ResultItemsSize = $content[$i+8] 
$Resirep=$ResultItemsSize.split(":")[1].trim() 
 
$coll = "" | select DisplayName,Success,ResultItemsCount,ResultItemsSize 
 
$coll.DisplayName = $Disp 
$coll.Success = $Succep 
$coll.ResultItemsCount = $Resc 
$coll.ResultItemsSize = $Resirep 
$collection +=$coll 
 

 
$collection | export-csv .\output.csv -notypeinformation 
 
################################################################




Regards

Sukhija Vikas

http://msexchange.me

1 comment: