Powershell List Excel Files and Copy

PowerShell Help
1

Hello - I apologize for the naivety of this post, please forgive my newness.

I have approximately 20,000 network files to filter through and copy certain ones to a local drive.

File List Requirements:

  • Excel files of various type (.xls, .xlsx, .xlsm)
  • Only files modified after 4/1/2022
  • Only files that contain “2022” in the filename

If the file meets those requirements then:

  • Copy the file to a local folder (original folder path structure doesn’t matter, all files can go in one folder)
  • Output the original path and filename to a txt file

I have created the following code, which successfully obtains all excel files and creates the filename list

Get-ChildItem "D:\network_folder\" -Filter *.xls -Recurse | Select-Object -Property FullName, LastWriteTime |
    Export-Csv -Path "C:\local_folder\file_list.csv" -Force -NoTypeInformation

However I cannot figure out the following issues:

  • how and where to filter for the lastwritetime
  • how and where to filter for the “2022” in the name
  • how and where to copy the files to the local folder
  • right now I’m just putting this all in the command line, do I need to make some file to run this process?

Thank you for any assistance you can provide

2

blimbert,
Welcome to the forum. :wave:t4:

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

There!

… it should be

-Filter *2022*.xls

With a

Instead of piping it directly to an output file you can use a

loop where you treat every file as needed and either collect the log information in a variable or output it directly to your output file.

That’s totally up to you. If you run this task more than once it might be a good idea to save the code in a file. :wink:

Regardless of all that -

I’d recommend to run this task locally on the server where the files are saved if possible. That will speed up this task quite a lot. :wink:

3

When you crosspost the same question at the same time to different forums you should at least post links to the other forums along with your question to avoid people willing to help you making their work twice or more.

Thanks

4

Olaf, thank you for the multiple responses and the etiquette feedback.

Questions:

What is the difference between using the -filter switch and the where object statement? Are there benefits to one or the other?

I am completely stumped on this one, could you please be so kind as to show me where this would go in the code process? I’m at a loss

a really naive question, how does one put it in a file and then execute that file?

5

It’s recommended to filter as soon as possible in a pipeline. The less pipeline objects you have to process the faster the command will be. But the parameter -Filter is limitted in its capabilities. So you have to filter with a Where-Object later on. There you can filter for each individual property or any combination of properties you need.

Please always read the help for the cmdlets you’re about to use completely including the examples to learn how to use them. It is beyond the scope of a forum to teach you basics scripting with PowerShell.

You may do a big step back and start with learning the very basics of PowerShell first. This will save you from a lot of wasted time and frustrations. And it will enable you to understand the help you get in forums like this.

And by the way … a robocopy command line would actually do all you need. :wink:

1 Like
6

Thanks for the guidance on the where-object. I have modified the code as follows to filter the date as needed:

Get-ChildItem "C:\Desktop\" -Filter *.xls -Recurse | Where-Object {$_.lastwritetime -gt [DateTime]"4/1/2022"} | Select-Object -Property FullName, LastWriteTime |
    Export-Csv -Path "C:\output\file_list.csv" -Force -NoTypeInformation

That returns the following list of 4 files:

Capture

However, when modify the filter in various ways I am not seeing the expected result.

If I add the 2022 filename filter, it returns no results, while I was expecting to see the 1 “2022” file in the image above.

Get-ChildItem "C:\Desktop\" -Filter *2022*.xls -Recurse | Where-Object {$_.lastwritetime -gt [DateTime]"4/1/2022"} | Select-Object -Property FullName, LastWriteTime |
    Export-Csv -Path "C:\output\file_list.csv" -Force -NoTypeInformation

If I filter for files with “order” in the name, it returns the last two files, but not the first file in the list

Get-ChildItem "C:\Desktop\" -Filter *order*.xls -Recurse | Where-Object {$_.lastwritetime -gt [DateTime]"4/1/2022"} | Select-Object -Property FullName, LastWriteTime |
    Export-Csv -Path "C:\output\file_list.csv" -Force -NoTypeInformation

Thoughts on what I may be doing incorrectly?

7

The screenshot you shared does not show any file fitting the filter *2022*.xls. That’s why you don’t see any ouput when you add the filter. It is a difference if a file is *.xls or *.xlsx. If you want to catch both you may use *.xls*. :wink:

To make you code easier readable you should use line breaks after each pipe symbol.

Here you can reaad more about:

1 Like