query mysql db by file names in a certain folder

Tagged: 

This topic contains 6 replies, has 3 voices, and was last updated by  Curtis Smith 2 years ago.

  • Author
    Posts
  • #30043

    Fred La Plante
    Participant

    I am trying to write a mysql select statement using the results from a get-childitem cmdlet of a certain folder. I am able to get the results from that cmdlet but do not know how to get each item of the array to be put into the query's "WHERE" section. The entire query is a string. How do I pass each item of the array into the query's string in the WHERE section of the query?

    I first get the filenames from the directory

    $files = Get-ChildItem -Path 'filepath\*.ext' | select -Property name
    

    Here is my code for the query:

     
    $sqlresult = path_to_script\MySQL.ps1 `
    -Query "SELECT exp.ExpenseID,exp.ExpenseUserID,exp.ExpenseDate,exp.BudgetEntity,exp.ActivityCode,exp.Amount,User.UserLastName,User.UserFirstName,exp.ReceiptPath
    FROM Expenses as exp
    JOIN User on userid = exp.expenseuserid
    WHERE exp.ReceiptPath ='filepath/filename'"
    

    I'm not sure how to bring the two sections of code together? I am using the MySQL.ps1 script from

    and am connecting successfully. I can run each section of code successfully, the query if I enter an exact name in the WHERE section. Any help would be greatly appreciated.

  • #30044

    Fred La Plante
    Participant

    One last item I forgot to mention. I am piping the results to a csv file:

    Export-Csv folder\test.csv -NoTypeInformation
    

    I am creating an index file for another program to import the files.

  • #30050

    Curtis Smith
    Participant

    Are you expecting multiple files in the results of your get-childitem command or just a single file? If a single file you should be able to get the Name property from the value in your $files variable like this

    WHERE exp.ReceiptPath ='$($files.name)'"

  • #30057

    Fred La Plante
    Participant

    Thanks for your help. I had tried just $files. I had forgotten to use the () in the code. There will be multiple items in the results of the get-childitem command most of the time. It is possible that there may be no data on occasions. At least I got some data back. I just need to figure out how to loop through the entire array from get-childitem.

  • #30058

    Curtis Smith
    Participant

    You can use the -join operator

    "WHERE exp.ReceiptPath = $((Get-ChildItem -Path *.ext) -join " OR exp.ReceiptPath = ")"
    

    Results in:

    WHERE exp.ReceiptPath = F:\file1.ext OR exp.ReceiptPath = F:\file2.ext OR exp.ReceiptPath = F:\file3.ext OR exp.ReceiptPath = F:\file4.ext

  • #30066

    Brian B
    Participant
    "WHERE exp.ReceiptPath = $((Get-ChildItem -Path *.ext | select -expand FullName) -join " OR exp.ReceiptPath = ")"
    

    I would think you'd need to do as you stated, only expanding the FullName property so you get the full path including the file name, otherwise I think you're right.

  • #30080

    Curtis Smith
    Participant

    You can, but the results are the same.


    PS F:\> "WHERE exp.ReceiptPath = $((Get-ChildItem -Path *.ext) -join " OR exp.ReceiptPath = ")"
    WHERE exp.ReceiptPath = F:\file1.ext OR exp.ReceiptPath = F:\file2.ext OR exp.ReceiptPath = F:\file3.ext OR exp.ReceiptPath = F:\file4.ext


    PS F:\> "WHERE exp.ReceiptPath = $((Get-ChildItem -Path *.ext | select -expand FullName) -join " OR exp.ReceiptPath = ")
    "
    WHERE exp.ReceiptPath = F:\file1.ext OR exp.ReceiptPath = F:\file2.ext OR exp.ReceiptPath = F:\file3.ext OR exp.ReceiptPath = F:\file4.ext

You must be logged in to reply to this topic.