locked
Search pattern in directory and extract string from files using PowerShell RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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/

    Thursday, January 24, 2019 8:58 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    You'll have better chance to get reply from the PowerShell forum.
    Friday, January 25, 2019 6:09 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

     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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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}


    https://regex101.com/

    • Edited by JS2010 Monday, January 28, 2019 5:40 PM
    Saturday, January 26, 2019 5:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote 
    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}

    https://regex101.com/

    Much better.  Excellent.

    \_(ツ)_/

    Saturday, January 26, 2019 6:54 PM