none
Need convert txt to csv using Power shell with headers RRS feed

  • Question

  • Hi All ,

    Can we convert below txt file to csv format added at the end ?? 

    file1 : 

    IP Address: 192.168.1.1

    Hostname: 01-any1TEST 
    Event Message: Ping
    Alert Status: Down at least 3 min
    Event Time: 17:25:14
    Alert Type: :Windows 2012 Server
    -------------------------------------------------------------------------------------
    IP Address: 192.168.1.2
    Hostname: 02-any2TEST  
    Event Message: Ping
    Alert Status: Down at least 4 min
    Event Time: 17:25:40
    Alert Type: :Unix Server
    -------------------------------------------------------------------------------------
    IP Address: 192.168.1.3
    Hostname: 03-any3TEST 
    Event Message: Ping
    Alert Status: Down at least 3 min
    Event Time: 17:26:21
    Alert Type: :windows host 
    -------------------------------------------------------------------------------------

    Csv file output required as below : 

    'IP Address','Hostname','Event Message','Alert Status','Event Time','Alert Type'

    '192.168.1.1','01-any1TEST','Ping','Down at least 3 min','17:26:21','Windows 2012 Server '

    '192.168.1.2','02-any2TEST','Ping','Down at least 3 min','17:26:21','unix host '

    '192.168.1.3','03-any3TEST','Ping','Down at least 3 min','17:26:21','windows host '

    Friday, July 28, 2017 11:35 AM

Answers

  • Here's a way that works in pre-PowerShell 5 (if required):


    $delimeterPattern = '^--'
    $recordPattern = '^([^:]+): (.+)'
    $logContent = Get-Content "testlog.txt"
    # how many lines in a record?
    $recordLines = ($logContent | Select-String $delimeterPattern |
      Select-Object -First 1).LineNumber
    for ( $i = 0; $i -lt $logContent.Count; $i += $recordLines ) {
      $output = New-Object PSObject
      for ( $j = $i; $j -lt $i + $recordLines; $j++ ) {
        $logContent[$j] | Select-String $recordPattern | ForEach-Object {
          $output | Add-Member NoteProperty $_.Matches[0].Groups[1].Value $_.Matches[0].Groups[2].Value
        }
      }
      $output
    }
    

    Duplicate question from here:

    http://stackoverflow.com/q/45372781/


    -- Bill Stewart [Bill_Stewart]


    Monday, July 31, 2017 3:12 PM
    Moderator

All replies

  • Hi All ,

    Can we convert below txt file to csv format added at the end ?? 


    Yes. That's easily possible. But first you should read the following post from the top of the list of this forum:

    This forum is for scripting questions rather than script requests


    Grüße - Best regards

    PS:> (79,108,97,102|%{[char]$_})-join''

    Friday, July 28, 2017 11:51 AM
  • HI , 

    Sorry for earlier incomplete request . I already tried myself need corrections . 

    $input = Get-Content -path "path\in.txt" | Select-String -List 'Alert Type','IP Address','Hostname','Event Message','Alert Status','Event Time'
    $data = $input[1..($input.Length - 1)]
    
    $maxLength = 0
    
    $objects = ForEach($record in $data) {
        $split = $record -split "\s{2,}|\t+"
        If($split.Length -gt $maxLength){
            $maxLength = $split.Length
        }
        $props = @{}
        For($i=0; $i -lt $split.Length; $i++) {
            $props.Add([String]($i+1),$split[$i])
        }
        New-Object -TypeName PSObject -Property $props
    }
    
    $headers = [String[]](0..$maxLength)
    $headers1 = [String[]]('MachineType','EventTime','HostName','IPAddress','AlertMessage','Severity','AlertType','AlertStatus')
    
    
    $objects | 
    Select-Object $headers |
    Export-Csv -NoTypeInformation -Path "path\out.csv"

    Friday, July 28, 2017 12:57 PM
  • So I learned somthing new today from Tobias .... again. Thanks James for the recommendation! ;-)

    You could try it like this:

    $Template = @'
    IP Address: {IPAddress*:192.168.1.1}
    Hostname: {HostName:01-any1TEST} 
    Event Message: {EventMessage:Ping}
    Alert Status: {AlertStatus:Down at least 3 min}
    Event Time: {EventTime:17\:25\:14}
    Alert Type: :{AlertType:Windows 2012 Server}
    -------------------------------------------------------------------------------------
    IP Address: {IPAddress*:192.168.1.2}
    Hostname: {HostName:02-any2TEST}  
    Event Message: {EventMessage:Ping}
    Alert Status: {AlertStatus:Down at least 4 min}
    Event Time: {EventTime:17\:25\:40}
    Alert Type: :{AlertType:Unix Server}
    -------------------------------------------------------------------------------------
    IP Address: {IPAddress*:192.168.1.3}
    Hostname: {HostName:03-any3TEST} 
    Event Message: {EventMessage:Ping}
    Alert Status: {AlertStatus:Down at least 3 min}
    Event Time: {EventTime:17\:26\:21}
    Alert Type: :{AlertType:windows host} 
    -------------------------------------------------------------------------------------
    '@
    
    Get-Content -Path 'path\in.txt' | 
        ConvertFrom-String -TemplateContent $Template | 
            Out-GridView


    Grüße - Best regards

    PS:> (79,108,97,102|%{[char]$_})-join''

    Saturday, July 29, 2017 1:06 AM
  • Here's a way that works in pre-PowerShell 5 (if required):


    $delimeterPattern = '^--'
    $recordPattern = '^([^:]+): (.+)'
    $logContent = Get-Content "testlog.txt"
    # how many lines in a record?
    $recordLines = ($logContent | Select-String $delimeterPattern |
      Select-Object -First 1).LineNumber
    for ( $i = 0; $i -lt $logContent.Count; $i += $recordLines ) {
      $output = New-Object PSObject
      for ( $j = $i; $j -lt $i + $recordLines; $j++ ) {
        $logContent[$j] | Select-String $recordPattern | ForEach-Object {
          $output | Add-Member NoteProperty $_.Matches[0].Groups[1].Value $_.Matches[0].Groups[2].Value
        }
      }
      $output
    }
    

    Duplicate question from here:

    http://stackoverflow.com/q/45372781/


    -- Bill Stewart [Bill_Stewart]


    Monday, July 31, 2017 3:12 PM
    Moderator