Skip to content

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

G1st July 2014

C6 Comments

TPowerShell, Windows

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:

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

  • Written by Best on 9 December 2014:

    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

  • Written by Dan on 1 April 2015:

    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.

  • Written by Aaron on 24 December 2015:

    Thank you very much. I learnt this from you.

    Greeting from Hong Kong !

    Merry Christmas !

  • Written by Mark Arnott on 15 February 2018:

    Thanks Adam

    Very useful
    Well explained, easy to understand.

  • Written by Philip on 20 March 2018:

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

  • Written by Nitz on 23 October 2018:

    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

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.