Script to list files by type and size, export to Excel

Instructions for using a Windows PowerShell script to compile computer file data and export the results to Excel as a tab-delimited CSV.

For the uninitiated, Windows PowerShell is a DOS-like task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language built on the .NET Framework. It looks a lot like Windows DOS and can work similarly.

As one of its many functions, PowerShell can be used to collect information about the files on a computer and export that information to Excel as a tab-delimited comma-separated values file (CSV). I recently had reason to gather information about the files on my computer so that I could manage disk space. In order to quickly identify the largest files on the system, I put PowerShell to work and exported the results to Excel so that I could identify the largest files by size.

Starting PowerShell

All Windows 7 and 8 machines have PowerShell installed. Instructions are available for Windows 8 and earlier versions.

Listing all files in a directory (and its subdirectories)

In this first example, I want to create an Excel comma-separated values (CSV) table of every file located within c:\Users\AdamDi~1\Images\Holidays (and its subfolders) by listing each file’s full path, the time it was last edited and its size (in bytes).

The code for this is:

get-childitem c:\Users\AdamDi~1\Images\Holidays -Recurse | where {!$_.PSIsContainer} | select-object FullName, LastWriteTime, Length | export-csv -notypeinformation -path files.csv | % {$_.Replace('"','')}

This is a recursive search, which means that PowerShell will search c:\Users\AdamDi~1\Images\Holidays plus any files that exist within any subfolders within that location. This is indicated by -Recurse.

Within select-object, I have indicated that I want the output to include the file’s full path (FullName), the time it was last edited (LastWriteTime) and its size (Length). The information is to be exported as a CSV file (export-csv) and the file is to be called files.csv. This file will be placed into the directory indicated within PowerShell’s command prompt (typically defaults to c:\Users\YourName).

Each property is to be tab-separated in the CSV as per {$_.Replace('"','')}.

Once the above script is run, the files.csv file should be written. Open it in Excel to check that it contains the correct information (ie. the script is working as it should).

Listing files of one specific type

I can amend the above code so that only files of a certain type will be listed in the CSV file. In the following example, I only want a list of .jpg files.

get-childitem -path c:\Users\AdamDi~1\Images\Holidays -Filter *.jpg -Recurse | where {!$_.PSIsContainer} | select-object FullName, LastWriteTime, Length | export-csv -notypeinformation -path files.csv | % {$_.Replace('"','')}

The -Filter command is used to select only .jpg files (this could be changed to any file type).

The output file would look something like that shown below.

Screen capture of Excel showing a table of file data — Example of an output CSV table showing file data collected via a PowerShell script.

Listing files of multiple specific types

Unfortunately, -Filter does not deal with multiple file types. For instance, I may want to list .jpg and .png files, but nothing else. To do this, I will use the following code:

get-childitem -path c:\Users\AdamDi~1\Images\Holidays -Include *.jpg,*.png -Recurse | where {!$_.PSIsContainer} | select-object FullName, LastWriteTime, Length | export-csv -notypeinformation -path files.csv | % {$_.Replace('"','')}

In this case, I used -Include to list types of files that I did want (ie, .jpg and .png) separated by a comma. You can list as many as you like. Alternatively, -Exclude can be used instead for the opposite purpose.

Other Options

If you don’t wish to conduct a recursive search, remove -Recurse and only the named folder will be searched.

If you want to list all known properties for each file, change select-object FullName, LastWriteTime, Length to select-object *.

If you want to specify where the CSV file is to be written, add a directory path before the file name, for example:

export-csv -notypeinformation -path c:\Users\wherever\files.csv

Comments

12 responses to “Script to list files by type and size, export to Excel”

On 9 December 2014, Best wrote:

Hi there, great tutorial by the way, was very helpful, i was just wondering how to do it by just showing only the folders instead of showing the file in the folder as well..
Thanks

On 1 April 2015, Dan wrote:

Thanx Bro I was so close :)
Get-ChildItem -recurse “%Path%” | where {$_.name -eq “normal.dot”} | Select -Expand Fullname | select Fullname,Length,LastWriteTime

For some reason I added “Select -Expand Fullname” and did not try without :)

your script help me.

On 24 December 2015, Aaron wrote:

Thank you very much. I learnt this from you.

Greeting from Hong Kong !

Merry Christmas !

On 15 February 2018, Mark Arnott wrote:

Thanks Adam

Very useful
Well explained, easy to understand.

On 20 March 2018, Philip wrote:

Great answer to what sounds like an easy task. This provided exactly what I needed. Thanks for posting.

On 23 October 2018, Nitz wrote:

Hi Adam

Thanks for the script very useful. However want to ask if we can add “username” and what files they are accessing, under the path?

Thanks

On 25 January 2019, Muhammad idreee wrote:

Hi,

I have two questions,

1. Its not showing all property e.g. size

2. The length is pure digits while i was expecting 01:20:50 instead

Can u guide me with that please?

Thanks and great work.

On 28 March 2019, Ken Davies wrote:

I had the same question. It appears that the “length” is actually just the size (in bytes), not the length of time to play (as in .mp3 files). I tried the code above, but even removing the restrictions on the fields included, there appears to be no length (of time) included. This, in spite of the fact I can set the Windows File Explorer to display it. I would like to get a list of my .mp3 files along with the playing time (“length” is the heading in Windows File Explorer), not just the size of the files.

Any way to do that?

On 6 April 2019, Nicolas wrote:

Lenght is the size in bytes ! hope it helps you :)

Another option for this is to export into a HTML report. you will have to work wtih the results to create your HTM code , like a table, then you can send it via email in HTML format, or even export your HTML table full of results into a web server container to show results in a browser ( make available for the users, department or management ) for audit purposes if you are looking for unsafe files or unproper files like music, movies, etc.

On 11 January 2024, Tommy wrote:

Hello there, is there any possibility to list files (by PS script) in specific folder and create let’s say CSV or TXT file, on every 1mln rows/file names the script finds? I have for instance a folder with 6 mln *.txt files, and as excel has it’s limitation for number of rows it can show, i want script to save 1mln results each time it gets this number while it’s searching through Get-ChildItem. So in this case the goal would be to get 6 CSV files.

On 11 January 2024, Adam Dimech wrote:

I think the sensible option in this case would be to use software that can handle bigger CSVs, for example, R.