query mysql db by file names in a certain folder

PowerShell Help
1

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

https://www.cogmotive.com/blog/powershell/querying-mysql-from-powershell

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.

2

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.

3

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)’"

4

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.

5

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

6 
"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.

7

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