Matching a Repeating Sub Series using a Regular Expression with PowerShell

Question

I have a text file that lists the names of a large number of Excel spreadsheets, and the names of the files that are linked to from the spreadsheets.

In simplified form it looks like this:

"Parent
 File1.xls"

  Link: ChildFileA.xls
  Link: ChildFileB.xls
"ParentFile2.xls"
"ParentFile3.xls"
  Blah
  Link: ChildFileC.xls
  Link: ChildFileD.xls
  More Junk
  Link: ChildFileE.xls
"Parent
 File4.xls"
  Link: ChildFileF.xls

In this example, ParentFile1.xls has embedded links to ChildFileA.xls and ChildFileB.xls, ParentFile2.xls has no embedded links, and ParentFile3.xls has 3 embedded links.

I am trying to write a regular expression in PowerShell that will parse the text file producing output in the following form:

ParentFile1.xls:ChildFileA.xls,ChildFileB.xls
ParentFile3.xls:ChildFileC.xls,ChildFileD.xls,ChildFileE.xls
etc

The task is complicated by the fact that the text file contains a lot of junk between each of the lines, and a parent may not always have a child. Furthermore, a single file name may pass over multiple lines. However, it's not as bad as it sounds, as the parent and child file names are always clearly demarcated (the parent with quotes and the child with a prefix of Link: ).

The PowerShell code I've been using is as follows:

$content = [string]::Join([environment]::NewLine, (Get-Content C:\Temp\text.txt))
$regex = [regex]'(?im)\s*\"(.*)\r?\n?\s*(.*)\"[\s\S]*?Link: (.*)\r?\n?'
$regex.Matches($content) | %{$_.Groups[1].Value + $_.Groups[2].Value + ":" + $_.Groups[3].Value}

Using the example above, it outputs:

ParentFile1.xls:ChildFileA.xls
ParentFile2.xls""ParentFile3.xls:ChildFileC.xls
ParentFile4.xls:ChildFileF.xls

There are two issues. Firstly, the inclusion of the "" instead of a newline whenever a Parent without a Child is processed. And the second issue, which is the most important, is that only a single child is ever shown for each parent. I'm guessing I need to somehow recursively capture and display the multiple child links that exist for each parent, but I'm totally stumped as to how to do this with a regular expression.

Amy help would be greatly appreciated. The file contains 100's of thousands of lines, and manual processing is not an option :)

Answer 1

Personally I would solve only part of that using regex.

First I would join the parent file names like this:

$text = (Get-Content C:\Temp\text.txt) -join "`r`n"
$text = [regex]::replace($text, '(?im)"Parent[^"]+"', { [regex]::replace($args, '(?m)\s*', '')  } )

And then continue with manual processing.

$res = @()
$parent = $null
switch -regex ($text -split "`n") {
    '^"Parent' { if ($parent) { $res += $parent }
                 $parent = new-object PsObject -prop @{Name = $_.Trim('"'); Links=@()}
    }
    '^\s*Link:' { $parent.Links += $_ -replace '^\s*Link:\s*', '' }
}
if ($parent) { $res += $parent }

$res | % { 
 $n = $_.Name
 $links = $_.Links -join ','
 write-host "$n`:$links"
}