Powershell: Parse a structured text file and save to .CSV

Question

I'm very new to Powershell. Only have been using it for about 2 weeks.

I have a file that is structured like this:

Service name: WSDL 
Service ID: 14234321885 
Service resolution path: /gman/wsdlUpdte 
Serivce endpoints: 
-------------------------------------------------------------------------------- 
Service name: DataService 
Service ID: 419434324305 
Service resolution path: /widgetDate_serv/WidgetDateServ 
Serivce endpoints:  
http://servername.company.com:1012/widgetDate_serv/WidgetDateServ
-------------------------------------------------------------------------------- 
Service name: SearchService 
Service ID: 393234543546 
Service resolution path: /ProxyServices/SearchService 
Serivce endpoints:  
http://servername.company.com:13010/Services/SearchService_5_0
http://servername2.company.com:13010/Services/SearchService_5_0
-------------------------------------------------------------------------------- 
Service name: Worker 
Service ID: 14187898547 
Service resolution path: /ProxyServices/Worker 
Serivce endpoints:  
http://servername.company.com:131009/Services/Worker/v9
--------------------------------------------------------------------------------

I'd like to parse the file and have Service name, Service ID, Service Resolution Path and Service Endpoints (which sometimes contain multiple or no values) in individual columms (CSV).

Beyond using Get-Content and looping through the file, I have no idea even where to start.

Any help will be appreciated. Thanks

Esperento57 · Accepted Answer · 2016-11-13 14:31:26Z

3

with PowerShell 5 you can use the fabulous command 'convertfrom-string'

$template=@'
Service name: {ServiceName*:SearchService} 
Service ID: {serviceID:393234543546} 
Service resolution path: {ServicePath:/ProxyServices/SearchService} 
Serivce endpoints:
http://{ServiceEP*:servername.company.com:13010/Services/SearchService_5_0}
http://{ServiceEP*:servername2.tcompany.tcom:13011/testServices/SearchService_45_0}
--------------------------------------------------------------------------------
Service name: {ServiceName*:Worker} 
Service ID: {serviceID:14187898547} 
Service resolution path: {ServicePath:/ProxyServices/Worker} 
Serivce endpoints:
http://{ServiceEP*:servername3.company.com:13010/Services/SearchService}
--------------------------------------------------------------------------------
Service name: {ServiceName*:WSDL} 
Service ID: {serviceID:14234321885} 
Service resolution path: {ServicePath:/gman/wsdlUpdte} 
Serivce endpoints:
http://{ServiceEP*:servername4.company.com:13010/Services/SearchService_5_0}
--------------------------------------------------------------------------------
'@


#explode file with template
$listexploded=Get-Content -Path "c:\temp\file1.txt" | ConvertFrom-String -TemplateContent $template

#export csv 
$listexploded |select *, @{N="ServiceEP";E={$_.ServiceEP.Value -join ","}} -ExcludeProperty ServiceEP | Export-Csv -Path "C:\temp\res.csv" -NoTypeInformation

answered Nov 13, 2016 at 14:31

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Dawson Over a year ago

Will this work with any number of ServiceEP? Or would I need to create a template for one with more than 2 entries?

Ahmed Kotb · Accepted Answer · 2014-03-18 23:20:38Z

1

Give this a try:

Read the file content as one string
Split it by 81 hyphens
Split each splited item on the colon char and take the last array item

Create new object for each item

$pattern = '-'*81  
$content = Get-Content D:\Scripts\Temp\p.txt | Out-String
$content.Split($pattern,[System.StringSplitOptions]::RemoveEmptyEntries) | Where-Object {$_ -match '\S'} | ForEach-Object {

$item = $_ -split "\s+`n" | Where-Object {$_}

    New-Object PSobject -Property @{
        Name=$item[0].Split(':')[-1].Trim()
        Id = $item[1].Split(':')[-1].Trim()
        ResolutionPath=$item[2].Split(':')[-1].Trim()
        Endpoints=$item[4..($item.Count)]
    } | Select-Object Name,Id,ResolutionPath,Endpoints
}

edited Mar 18, 2014 at 23:20

Ahmed Kotb

6,3276 gold badges37 silver badges53 bronze badges

answered Jul 19, 2012 at 11:20

Shay Levy

128k34 gold badges193 silver badges210 bronze badges

5 Comments

JPBlanc Over a year ago

you have to 'hardcode' fields, and don't take care of multiple urls.

Bill Over a year ago

Thanks Shay, I needed to make a couple changes to fix some error messages, but it works great now when outputting to the console. When I add "| export-csv test.csv" The last object (Endpoints) displays as "System.Object[]" in the actual file. I'm assuming that something needs to be done to that object to make it text? I'll start googling, but if you could reply, that would be great.

Bill Over a year ago

Shay, figured it out based on your answer to someone else on another site. Changed the last line to: } | Select-Object Name,Id,ResolutionPath,@{n="Endpoints";e={[string]::join(";",$_.Endpoints)}} Thank you again!

Shay Levy Over a year ago

@Bill Excellent, that's what I was just going to write with one update, in v2 you can use the -join operator: @{n="Endpoints";e={$_.Endpoints -join ';'}}

Shay Levy Over a year ago

@JPBlanc yes, I am hard coding fields but in my tests I had no issues with multiple Urls (endpoints).

user189198 · Accepted Answer · 2012-07-18 21:41:36Z

1

Try this:

Get-Content | ? { $_ -match ': ' } | % { $_ -split ': ' } | Export-Csv Test.csv;

Basically it boils down to:

Get all text content as an array
Filter for lines that contain ': '
For each line left over, split it on ': '
Export object arrays to a CSV file named test.csv

Hope this points you in the right direction.

Note: Code is untested.

answered Jul 18, 2012 at 21:41

user189198

Comments

JPBlanc · Accepted Answer · 2012-07-20 15:23:02Z

Here is a general way parsing files with records and records of records (and so on), it use the powerfull PowerShell switch instruction with regular expressions and the begin(), Process(), end() function template.

Load it, debug it, correct it ...

function Parse-Text
{
  [CmdletBinding()]
  Param
  (
    [Parameter(mandatory=$true,ValueFromPipeline=$true)]
    [string]$ficIn,
    [Parameter(mandatory=$true,ValueFromPipeline=$false)]
    [string]$ficOut
  )

  begin
  {
    $svcNumber = 0
    $urlnum = 0
    $Service = @()
    $Service += @{}
  } 

  Process 
  {
    switch -regex -file $ficIn
    {
      # End of a service
      "^-+"
      {
        $svcNumber +=1
        $urlnum = 0
        $Service += @{}
      }
      # URL, n ones can exist
      "(http://.+)" 
      {
        $urlnum += 1
        $url = $matches[1]
        $Service[$svcNumber]["Url$urlnum"] = $url
      }
      # Fields
      "(.+) (.+): (.+)" 
      {
        $name,$value = $matches[2,3]
        $Service[$svcNumber][$name] = $value
      }
    }
  }

  end 
  {
    #$service[3..0] | % {New-Object -Property $_ -TypeName psobject} | Export-Csv c:\Temp\ws.csv
    # Get all the services except the last one (empty -> the file2Parse is teerminated by ----...----)
    $tmp = $service[0..($service.count-2)] | Sort-Object @{Expression={$_.keys.count };Descending=$true}
    $tmp | % {New-Object -Property $_ -TypeName psobject} | Export-Csv $ficOut
  }
}


Clear-Host
Parse-Text -ficIn "c:\Développements\Pgdvlp_Powershell\Apprentissage\data\Text2Parse.txt" -ficOut "c:\Temp\ws.csc"
cat "c:\Temp\ws.csv"

Collectives™ on Stack Overflow

Powershell: Parse a structured text file and save to .CSV

4 Answers 4

1 Comment

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related