Extract specific multiple string from lines via regex

Question

I have been trying to extract certain values from multiple lines inside a .txt file with PowerShell. I have a huge file with all the backup and trying to extract all those lines.

Txt file:

Backup-ID:           hostname01
Policy:              VM_weekly
Primary Copy:        23
Expires:             1/5/2024 3:19:13 AM
Type:                4


Copy Number:        2
Fragment Size (KB): 6188832
Expires:            1/5/2024 3:19:13 AM
MediaID:            XXX122
TestID:             1222
Block:              33


Copy Number:        3
Fragment Size (KB): 6188832
Expires:            1/5/2024 3:19:13 AM
MediaID:            XXX134
TestID:             223
Block:              22
Duplicate:          N



Backup-ID:           hostname02
Policy:              VM_weekly2
Primary Copy:        24
Expires:             1/5/2024 3:19:13 AM
Type:                2


Copy Number:        2
Fragment Size (KB): 6188832
Expires:            1/5/2024 3:19:13 AM
MediaID:            XXX244
Comp:               BBB
Block:              45
Duplicate:          N


Copy Number:        3
Fragment Size (KB): 6188832
Expires:            1/5/2024 3:19:13 AM
MediaID:            XXX199
Comp:               AA
Block:              334

Copy Number:        4
Fragment Size (KB): 6188832
Expires:            1/5/2024 3:19:13 AM
MediaID:            XXX177

I have code so far :

Get-Content C:\test.txt | Select-String -Pattern 'Backup-ID: ' ,'Policy: ' ,'Primary Copy: ' ,'Expires:  ' ,'Copy Number: ' , 'Fragment Size ' ,'Expires: ' , 'MediaID:'

This is what I want :

hostname01,VM_weekly,23,6188832,1/5/2024 3:19:13 AM,XXX122,3,6188832,1/5/2024 3:19:13 AM,XXX134
hostname02,VM_weekly2,24,1/5/2024 3:19:13 AM,2,6188832,1/5/2024 3:19:13 AM,XXX244,3,6188832,1/5/2024 3:19:13 AM,XXX199,4,6188832,1/5/2024 3:19:13 AM,XXX177

JosefZ · Accepted Answer · 2019-02-28 18:22:48Z

2

Here's my old-school approach:

$line = ''
Get-Content C:\test.txt | 
    Select-String -Pattern 'Backup-ID: ' ,'Policy: ' ,'Primary Copy: ' ,'Expires:  ' ,'Copy Number: ' , 'Fragment Size ' ,'Expires: ' , 'MediaID:' |
        ForEach-Object {
            $aux = $_  -split ':',2            # only 2 substrings
            if ($aux[0] -eq 'Backup-ID') {
                if ( $line -ne '' ) { $line }  # Write-Output (current line)
                $line = $aux[1].Trim()
            } else {
                $line += ',' + $aux[1].Trim()
            }
        }
        $line                                   # Write-Output (last line)

Output:

D:\PShell\SO\54921319.ps1

hostname01,VM_weekly,23,1/5/2024 3:19:13 AM,2,6188832,1/5/2024 3:19:13 AM,XXX122,3,6188832,1/5/2024 3:19:13 AM,XXX134
hostname02,VM_weekly2,24,1/5/2024 3:19:13 AM,2,6188832,1/5/2024 3:19:13 AM,XXX244,3,6188832,1/5/2024 3:19:13 AM,XXX199,4,6188832,1/5/2024 3:19:13 AM,XXX177

Edit: … I need to export CSV file ….

$xArr = D:\PShell\SO\54921319.ps1
$xCsv = $xArr |  ConvertFrom-Csv -Header $(1..30|%{"a$_"})
$xcsv | Export-Csv -NoTypeInformation -Path c:\temp\result.csv

Of course, you can compute

actual upper limit for -Header $(1..30|%{"a$_"}) instead of estimated 30 e.g. as ($xArr | % {$_.Split(',').Count}|Measure-Object -Maximum).Maximum,
or even compute some human-readable headers (having in mind recurring names of some properties for each Copy Number inside given Backup-ID)

edited Feb 28, 2019 at 18:22

answered Feb 28, 2019 at 10:43

JosefZ

30.5k6 gold badges52 silver badges96 bronze badges

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

3 Comments

Arbelac Over a year ago

thanks but I need to export CSV file. I am using $line | export-CSV c:\temp\result.csv but no luck

JosefZ Over a year ago

@Arbelac answer updated even thought this is an additional requirement above the OQ topic.

Arbelac Over a year ago

Hi JosefZ , I hava a question related to the vbscript. have you had a chance take a look?stackoverflow.com/questions/67533523/…

score 2 · Accepted Answer · 2019-02-28 11:12:45Z

2

Using a better Pattern

 $Pattern = '^Backup-ID|^Policy|^Primary Copy|^Expires|^Copy Number|^Fragment Size|^Expires|^MediaID'

and RegEx to split the output at Backup-ID

(Get-Content .\test.txt|Select-String -Pattern $Pattern|Out-String) -split "(?=Backup-ID)"|ForEach-Object {
    (($_ -split "`r?`n" | %{($_ -split ":\s+",2)[1]}) -join ',').Trim(',')
}

hostname01,VM_weekly,23,1/5/2024 3:19:13 AM,2,6188832,1/5/2024 3:19:13 AM,XXX122,3,6188832,1/5/2024 3:19:13 AM,XXX134
hostname02,VM_weekly2,24,1/5/2024 3:19:13 AM,2,6188832,1/5/2024 3:19:13 AM,XXX244,3,6188832,1/5/2024 3:19:13 AM,XXX199,4,6188832,1/5/2024 3:19:13 AM,XXX177

edited Feb 28, 2019 at 11:12

answered Feb 28, 2019 at 10:57

user6811411

4 Comments

Ansgar Wiechers Over a year ago

You don't need multiple ^ if you group the alternation: ^(Backup-ID|Policy|Primary Copy|...).

Arbelac Over a year ago

thanks but I need to export CSV file. I am using $line | export-CSV c:\temp\result.csv but no luck

user6811411 Over a year ago

While the format of the output is similar to a csv it doesn't include a header and is jagged (different number of fileds/columns) so better use Set-Content to save the output.

Arbelac Over a year ago

Now I have tried your solution. But Just I am getting hostname01 and hostname02 so no other columns such as VM_weekly,23,1/5/2024 3:19:13 AM,2,6188832,1/5/2024 3:19:13 AM,XXX122,3,6188832,1/5/2024 3:19:13 AM,XXX134

marsze · Accepted Answer · 2019-02-28 11:19:15Z

1

This maybe?

& {
    $current = $null
    switch -regex -file 'C:\text.txt' {
        '^(Backup-ID|Policy|Primary Copy|Expires|Copy Number|Fragment Size \(KB\)|Expires|MediaID):\s+(.*)' {
            if ($matches[1] -eq "Backup-ID") {
                if ($current) { $current.ToString() }
                $current = [Text.StringBuilder]::new()
                [void]$current.Append($matches[2])
            }
            else {
                [void]$current.Append(",").Append($matches[2])
            }
        }
    }
    $current.ToString()
}

edited Feb 28, 2019 at 11:19

answered Feb 28, 2019 at 10:47

marsze

17.5k6 gold badges59 silver badges67 bronze badges

5 Comments

marsze Over a year ago

@Arbelac If your file is really huge, this solution has the potential to be faster. But you should try it out.

user6811411 Over a year ago

Your pattern won't match the (KB) after Fragment Size, partly OPs fault, otherwise +1

marsze Over a year ago

@LotPings Thx. Fixed. Yes I blame OP too :P

Arbelac Over a year ago

thanks but I need to export CSV file. I am using $line | export-CSV c:\temp\result.csv but no luck

marsze Over a year ago

@Arbelac It's basically already CSV, you could just try | out-file c:\temp\result.csv. If you want to use Export-CSV, you need to work with objects, but that was not the requirement in your original answer.

Collectives™ on Stack Overflow

Extract specific multiple string from lines via regex

3 Answers 3

3 Comments

4 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related