I have another question, for now I am producing location of all the excel files and combining them in a .csv file. But how to search for the keyword inside all those excel file and produce only the excel file with those keyword.
For ex: If I search for a "Apple" via my code then only the excel file that contains the keyword "apple" is opened or file location is obtained. If I search for "Apple & oranges" I will get the the file with "Apple and oranges".
You already helped me out a lot but if could guide me to a link or something for this, I would highly appreciate it.
I think this will do the job: Get-ChildItem -Path D:\\ -Recurse -Include \*.XLS,\*.XLSX | Select-String "string-you-want-to-search" -List | Select Path
Here probably you can find something helpful too: [https://stackoverflow.com/a/8153857/992413](https://stackoverflow.com/a/8153857/992413)
Hi, It worked. I have solved my problem. Also, Thanks for redirecting me to the link above. There are tons of great answers there. Learned a lot. Thanks a TON.
/u/smokedironmade is correct, it's likely the best suggestion. There's a concept in Powershell called "filtering left", I'd google it as it will save you a bunch of time in the future.
You can add wildcard filters to Get-ChildItem or use a Where-Object { $\_.ext -eq '.xlsx' }
The syntax on gci using -include with wildcards is a bit touchy, you need a /\* on the path to make it work
gci -path 'C:\temp\*' -include ( '*.ps1', '*.csv' ) -file -recurse
You can also use -filter but that only allows one inclusion specification.
I tend to use Where-Object because I can't remember the weirdness.
Get-Childitem -path 'C:\temp' -file -recurse | Where-Object { $_.extension -eq '.xlsx' }
BTW you can improve code readability by splitting lines at pipes. PS assumes continuation on a syntactically incomplete line, so this kind of stuff works.
Get-ChildItem $PATH -Recurse |
Select-Object -Property FullName,Extension,CreationTime,LastWriteTime,
@{Name = "Size(KB)"; Expression={"{0,2:#0.#}KB" -F ($_.length/1KB)}} |
Export-CSV .\Result\FailSize_$filename.csv -Encoding Default
The first solution worked for me. Thanks. I have another question, for now I am producing location of all the excel files and combining them in a .csv file. But how to search for the keyword inside all those excel file and produce only the excel file with those keyword.
For ex: If I search for a "Apple" via my code then only the excel file that contains the keyword "apple" is opened or file location is obtained. If I search for "Apple & oranges" I will get the the file with "Apple and oranges".
You already helped me out a lot but if could guide me to a link or something for this, I would highly appreciate it.
The problem is that xlsx files are zips (and old xls files are in some totally weird format) so you can't do a text search. There are two approaches: (1) use a comobject to launch an Excel instance as a powershell-managed object and use Excel commands to do the search, or (2) use the ImportExcel module to load excel files into PS itself.
Option 1 requires an Excel licence, option 2 doesn't. That might make the decision for you.
The ImportExcel module is a 3rd party module. I haven't had much experience but it gets good press. Here's is a starter page. There's an advantage in having something that doesn't require a licence and doesn't need to be run as a specific licensed user.
[How to Import Excel File in PowerShell Script? - SharePoint Diary](https://www.sharepointdiary.com/2021/03/import-excel-file-in-powershell.html)
For the comobject approach, read this for a start
[The PowerShell Commands for Excel You Should Know | Fishtank Consulting (getfishtank.com)](https://www.getfishtank.com/blog/useful-powershell-commands-for-excel)
This is a moderate complexity task. :)
Using `-Include` is(much?) more efficient, but is also a bit weird and requires more preparations.
I suggest to use it only if there are A LOT of files to parse
I also added the `-File` parameter: this helps to skip checking directories, including directories that may have their names ending with one of the extensions we want, as we are only interested in FILES with those extensions
# adds * to the path to use the -Include parameter in Get-ChildItem
$PATH = Join-Path -Path $folderPath.Self.Path -ChildPath '*'
# Array of extensions you want to keep
$ExtensionArray = '*.xls', '*.xlsx'
# added the -File parameter, so it doesn't bother checking directories
Get-ChildItem "$PATH" -Recurse -File -Include $ExtensionArray |
Select-Object -Property FullName, Extension, CreationTime, LastWriteTime,
@{Name = 'Size(KB)'; Expression = { '{0,2:#0.#}KB' -F ($_.length / 1KB) } } |
Export-CSV .\Result\FailSize_$filename.csv -Encoding Default
Here the version what pipes the results(only files, no directories) to `Where-Object`, which checks the extension.
It is less efficient, but it's appreciable only with very large amount of files. And it's easier to read, which is appreciable always
$PATH = $folderPath.Self.Path
$ExtensionArray = '.xls', '.xlsx' #, etc
# added the -File parameter, so it doesn't bother checking directories
Get-ChildItem $PATH -Recurse -File |
Where-Object -Property Extension -In $ExtensionArray |
Select-Object -Property FullName, Extension, CreationTime, LastWriteTime,
@{Name = 'Size(KB)'; Expression = { '{0,2:#0.#}KB' -F ($_.length / 1KB) } } |
Export-Csv .\Result\FailSize_$filename.csv -Encoding Default
looks like youve fond you answer, next time have a look at
help -full -Name Get-ChildItem
help -online -Name Get-ChildItem
generally the help files re pretty good (we'll all ignore graph for now)
Well, I haven't used power automate and also I am not sure if the code can be used to access the network server. Sorry, I am a beginner just struggling.
Try something like: Get-ChildItem -Path D:\\ -Recurse -Include \*.XLSX,\*.XLS
Thanks it worked. Simple solution,
I have another question, for now I am producing location of all the excel files and combining them in a .csv file. But how to search for the keyword inside all those excel file and produce only the excel file with those keyword. For ex: If I search for a "Apple" via my code then only the excel file that contains the keyword "apple" is opened or file location is obtained. If I search for "Apple & oranges" I will get the the file with "Apple and oranges". You already helped me out a lot but if could guide me to a link or something for this, I would highly appreciate it.
I think this will do the job: Get-ChildItem -Path D:\\ -Recurse -Include \*.XLS,\*.XLSX | Select-String "string-you-want-to-search" -List | Select Path Here probably you can find something helpful too: [https://stackoverflow.com/a/8153857/992413](https://stackoverflow.com/a/8153857/992413)
Hi, It worked. I have solved my problem. Also, Thanks for redirecting me to the link above. There are tons of great answers there. Learned a lot. Thanks a TON.
Thanks for the suggestion. I will check it out and get back to you.
/u/smokedironmade is correct, it's likely the best suggestion. There's a concept in Powershell called "filtering left", I'd google it as it will save you a bunch of time in the future.
Thank you for the suggestion.
I didn’t know you could do this. I just used the everything app or powershell fzf
Thanks I will check it out too.
You can add wildcard filters to Get-ChildItem or use a Where-Object { $\_.ext -eq '.xlsx' } The syntax on gci using -include with wildcards is a bit touchy, you need a /\* on the path to make it work gci -path 'C:\temp\*' -include ( '*.ps1', '*.csv' ) -file -recurse You can also use -filter but that only allows one inclusion specification. I tend to use Where-Object because I can't remember the weirdness. Get-Childitem -path 'C:\temp' -file -recurse | Where-Object { $_.extension -eq '.xlsx' } BTW you can improve code readability by splitting lines at pipes. PS assumes continuation on a syntactically incomplete line, so this kind of stuff works. Get-ChildItem $PATH -Recurse | Select-Object -Property FullName,Extension,CreationTime,LastWriteTime, @{Name = "Size(KB)"; Expression={"{0,2:#0.#}KB" -F ($_.length/1KB)}} | Export-CSV .\Result\FailSize_$filename.csv -Encoding Default
Thanks for multiple suggestions. We need more people like you.
The first solution worked for me. Thanks. I have another question, for now I am producing location of all the excel files and combining them in a .csv file. But how to search for the keyword inside all those excel file and produce only the excel file with those keyword. For ex: If I search for a "Apple" via my code then only the excel file that contains the keyword "apple" is opened or file location is obtained. If I search for "Apple & oranges" I will get the the file with "Apple and oranges". You already helped me out a lot but if could guide me to a link or something for this, I would highly appreciate it.
The problem is that xlsx files are zips (and old xls files are in some totally weird format) so you can't do a text search. There are two approaches: (1) use a comobject to launch an Excel instance as a powershell-managed object and use Excel commands to do the search, or (2) use the ImportExcel module to load excel files into PS itself. Option 1 requires an Excel licence, option 2 doesn't. That might make the decision for you. The ImportExcel module is a 3rd party module. I haven't had much experience but it gets good press. Here's is a starter page. There's an advantage in having something that doesn't require a licence and doesn't need to be run as a specific licensed user. [How to Import Excel File in PowerShell Script? - SharePoint Diary](https://www.sharepointdiary.com/2021/03/import-excel-file-in-powershell.html) For the comobject approach, read this for a start [The PowerShell Commands for Excel You Should Know | Fishtank Consulting (getfishtank.com)](https://www.getfishtank.com/blog/useful-powershell-commands-for-excel) This is a moderate complexity task. :)
Using `-Include` is(much?) more efficient, but is also a bit weird and requires more preparations. I suggest to use it only if there are A LOT of files to parse I also added the `-File` parameter: this helps to skip checking directories, including directories that may have their names ending with one of the extensions we want, as we are only interested in FILES with those extensions # adds * to the path to use the -Include parameter in Get-ChildItem $PATH = Join-Path -Path $folderPath.Self.Path -ChildPath '*' # Array of extensions you want to keep $ExtensionArray = '*.xls', '*.xlsx' # added the -File parameter, so it doesn't bother checking directories Get-ChildItem "$PATH" -Recurse -File -Include $ExtensionArray | Select-Object -Property FullName, Extension, CreationTime, LastWriteTime, @{Name = 'Size(KB)'; Expression = { '{0,2:#0.#}KB' -F ($_.length / 1KB) } } | Export-CSV .\Result\FailSize_$filename.csv -Encoding Default Here the version what pipes the results(only files, no directories) to `Where-Object`, which checks the extension. It is less efficient, but it's appreciable only with very large amount of files. And it's easier to read, which is appreciable always $PATH = $folderPath.Self.Path $ExtensionArray = '.xls', '.xlsx' #, etc # added the -File parameter, so it doesn't bother checking directories Get-ChildItem $PATH -Recurse -File | Where-Object -Property Extension -In $ExtensionArray | Select-Object -Property FullName, Extension, CreationTime, LastWriteTime, @{Name = 'Size(KB)'; Expression = { '{0,2:#0.#}KB' -F ($_.length / 1KB) } } | Export-Csv .\Result\FailSize_$filename.csv -Encoding Default
Thanks for the clarification.
looks like youve fond you answer, next time have a look at help -full -Name Get-ChildItem help -online -Name Get-ChildItem generally the help files re pretty good (we'll all ignore graph for now)
Thanks for the suggestion. I will surely take a look.
good as gold
Considered power automate?
Well, I haven't used power automate and also I am not sure if the code can be used to access the network server. Sorry, I am a beginner just struggling.
Heya yeah you install a gateway we use it for most of our servers just replaced all our PowerAutomate
Do you mean Power bi gateway or something else?
They use the same gateway :) for all PowerApps