Asked by:
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?
Rakesh Jayaram http://blogs.msdn.com/b/rakesh_ramblings/
- Moved by Wendy ZangMicrosoft contingent staff Friday, January 25, 2019 6:20 AM
Thursday, January 24, 2019 8:58 PM
All replies
-
You'll have better chance to get reply from the PowerShell forum.Friday, January 25, 2019 6:09 AM
-
Hi Rakesh M J,
Thank you for posting here.
Since your question is more related to PoweShell, I will move it to PowerShell forum for suitable support.
https://social.technet.microsoft.com/Forums/windows/en-US/home?forum=winserverpowershell
The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.
Best Regards,
Wendy
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Friday, January 25, 2019 6:15 AM -
Select-String -Path *.sql -pattern 'mydb\.' | select -ExpandProperty Line
Pattern is regex pattern and "." is a special character and must be escaped.
Select-String does the same thing as Get-Content.
help Select-String -online
\_(ツ)_/
Friday, January 25, 2019 6:32 AM -
get-childitem -recurse -filter *.sql |
select-string -pattern 'mydb[^ ]*' -allmatches |
select-object -property path, @{n='Matches.value'; e={$_.matches.value}}
Path Matches.value
---- -------------
C:\Users\admin\mydb\file1.sql mydb.ops1_tbl
C:\Users\admin\mydb\file2.sql mydb.ops2_tbl
C:\Users\admin\mydb\file3.sql {mydb.ops3_tbl, mydb.ops4_tbl}
- Edited by JS2010 Monday, January 28, 2019 5:40 PM
Saturday, January 26, 2019 5:58 PM -
Isn't that what this does?
Select-String -Path *.sql -pattern 'mydb\.' | select -ExpandProperty Line
If you don't escape the"." then any character will match which is not what the user wants … but maybe it is.
Here is the whole command:
Select-String -Path *.sql -pattern 'mydb\.'| select Filename,@{n='Line';e={$_.Line}}
Using a loop with a custom object also works but it is clumsy for PowerShell.
\_(ツ)_/
Saturday, January 26, 2019 6:53 PM -
https://regex101.com/get-childitem -recurse -filter *.sql |select-string -pattern 'mydb[^ ]*' -allmatches |select path, @{n='matches'; e={$_.matches.value}}
Path matches---- -------/Users/js/mydb/file1.sql mydb.ops1_tbl/Users/js/mydb/file2.sql mydb.ops2_tbl/Users/js/mydb/file3.sql {mydb.ops3_tbl, mydb.ops4_tbl}
Much better. Excellent.\_(ツ)_/
Saturday, January 26, 2019 6:54 PM