Search pattern in directory and extract string from files using PowerShell

Question

I have almost 400 .sql files where i need to search for a specific pattern and output the results.

e.g

*file1.sql

select * from mydb.ops1_tbl from something1 <other n lines>

*file2.sql

select * from mydb.ops2_tbl from something2 <other n lines>

*file3.sql

select * from mydb.ops3_tbl ,mydb.ops4_tbl where a = b <other n lines>

Expected result

file1.sql mydb.ops1_tbl

file2.sql mydb.ops2_tbl

file3.sql mydb.ops3_tbl mydb.ops4_tbl

Below script in powershell - able to fetch the filename

Get-ChildItem -Recurse -Filter *.sql|Select-String -pattern "mydb."|group path|select name

Below script in powershell - able to fetch the line

Get-ChildItem -Recurse -Filter *.sql | Select-String -pattern "mydb." |select line

I need in the above format, someone has any pointers regarding this?

score 3 · Accepted Answer · 2019-01-24 21:24:20Z

3

you need to escape the dot in a RegEx to match a literal dot with a backslash \.
to get all matches on a line use the parameter -AllMatches
you need a better RegEx to match the mydb string upto the next space
iterate the Select-string results with a ForEach-Object

A one liner:

Get-ChildItem -Recurse -Filter *.sql|Select-String -pattern "mydb\.[^ ]+" -Allmatches|%{$_.path+" "+($_.Matches|%{$_.value})}

broken up

Get-ChildItem -Recurse -Filter *.sql|
    Select-String -Pattern "mydb\.[^ ]+" -Allmatches | ForEach-Object{
        $_.path+" "+($_.Matches|ForEach-Object{$_.value})
    }

Sample output:

Q:\Test\2019\01\24\file1.sql mydb.ops1_tbl
Q:\Test\2019\01\24\file2.sql mydb.ops2_tbl
Q:\Test\2019\01\24\file3.sql mydb.ops3_tbl mydb.ops4_tbl

If you don't want the full path (despite you are recursing) like your Expected result,
replace $_.path with (Split-Path $_.path -Leaf)

edited Jan 24, 2019 at 21:24

answered Jan 24, 2019 at 21:14

user6811411

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

Comments

wp78de · Accepted Answer · 2019-01-24 20:57:08Z

1

First, fetch the result of your file query into an array, then iterate over it and extract the file contents using regex matching:

$files = Get-ChildItem -Recurse -Filter *.sql|Select-String -pattern "mydb."|group path|select name
foreach ($file in $files)
{
    $str = Get-Content -Path $file.Name
    $matches = ($str | select-string -pattern "mydb\.\w+" -AllMatches).Matches.Value

    [console]::writeline("{0:C} {1:C}", $file.Name, [string]::Join(' ', $matches) ) 
}

I used the .NET WriteLine function to output the result for demonstration purpose only.

answered Jan 24, 2019 at 20:57

wp78de

19.1k7 gold badges49 silver badges78 bronze badges

Collectives™ on Stack Overflow

Search pattern in directory and extract string from files using PowerShell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related