Author Posts

September 24, 2015 at 9:23 am

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.

September 24, 2015 at 9:40 am

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.

September 24, 2015 at 10:30 am

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

September 24, 2015 at 11:11 am

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.

September 24, 2015 at 11:31 am

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

September 24, 2015 at 12:13 pm

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

September 24, 2015 at 1:16 pm

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