Wednesday, September 17, 2014

Write directory listing to CSV in Windows PowerShell

This one-line command will invoke Windows PowerShell to write a directory listing to a CSV file, which is easy to use in spreadsheets and database programs. It recurses subfolders, and it includes the following information: full file name, creation time, last modified time, file size, and owner (last modified by).

To use it, simply modify the two paths: directory to scan and path to the CSV.

powershell "Get-ChildItem -Recurse c:\directory\to\scan\ | ForEach-Object {$_ | add-member -name "Owner" -membertype noteproperty -value (get-acl $_.fullname).owner -passthru} | Sort-Object fullname | Select FullName,CreationTime,LastWriteTime,Length,Owner | Export-Csv -Force -NoTypeInformation c:\folder\to\directory.csv"

Tested on Windows 7 Enterprise.

I use it as part of an automatic process to archive a folder with Git.

29 comments:

  1. Is there a way to just get the folder paths, because when I ran the command it also gave me contents of the folder, I just want to be able to get a list of the folder paths. Thanks,

    ReplyDelete
  2. Matthew: to limit to folders add "| ?{ $_.PSIsContainer }" before "| ForEach-Object" (no quotation marks)

    ReplyDelete
  3. Is there a way prompt the user for the folder to scan and prompt where to output the file? If not, could you pick up the path where they run the Powershell from and output the file at the same level?

    ReplyDelete
  4. Darrell: I am not sure about an interactive prompt, but this version uses relative paths


    powershell "Get-ChildItem -Recurse . | ?{ $_.PSIsContainer } | ForEach-Object {$_ | add-member -name "Owner" -membertype noteproperty -value (get-acl $_.fullname).owner -passthru} | Sort-Object fullname | Select FullName,CreationTime,LastWriteTime,Length,Owner | Export-Csv -Force -NoTypeInformation .\directory.csv"

    (code not tested)

    ReplyDelete
  5. Any idea why I'm getting a "an empty pipe element is not allowed" error when I try to run it in Windows 2008 R2 Powershell v1.0?

    ReplyDelete
  6. Brainwashed: as a guess, make sure the directory exists and is not empty.

    ReplyDelete
  7. Completely delighted with this solution! Saves a lot of work. Thank you!

    ReplyDelete
  8. Extremely grateful finding this. Helped so much! Thank you!

    ReplyDelete
  9. @Brainwashed, if you are already in a powershell window you don't need powershell " at the start or the end "

    Oddly this runs much slower than dir c:\directory\to\scan\ > c:\folder\to\directory.txt from a regular dos prompt

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Can anyone please provide an amended version to include other metadata? I amended it to include "contributingartists" but this only produced a column header and no data. Thank you.

    ReplyDelete
    Replies
    1. I need this as well, need to list "Authors", but only getting an empty column...

      Delete
  12. is there a way to list folder only?

    thanks

    ReplyDelete
  13. Charlesbuzz: This minimal example shows a filter
    Get-ChildItem -Recurse | ?{ $_.PSIsContainer }

    To help you read this, PowerShell gives the name "container" to what you call a folder, so you are filtering to child items that are containers.

    You can add this filter to the code in the article.

    ReplyDelete
  14. Hi Andrew, how about scanning all directories of a PC for csv, txt,tav, xlsx,xls files?

    ReplyDelete
  15. Hi Andrew, how about scanning all directories of PC for csv, txt, tab, xls, xlsx files?

    ReplyDelete
  16. roidive: Add '-Include *.csv' after the directory name but before the ForEach-Object.

    ReplyDelete
  17. This is exactly what I've been looking for, thank you! So far working great, with one exception. If any file or directory names have the [ character in it, the script throws an error. Is there anyway to have the script recognize special characters so I can ensure the file list I get is completely accurate? Thanks!

    ReplyDelete
  18. how can I filter folder depth to only 3?

    ReplyDelete
  19. VCPCAD: As of PowerShell version 5 there is a -Depth parameter, so you could add "-depth 3"

    ReplyDelete
  20. What is the syntax to also return file-type? (as in file extension)

    ReplyDelete
  21. Thank you for this post, it is a big help. Can I also limit the data returned to files with todays date only?

    ReplyDelete
  22. When i run it using PS7, i get a blank CSV

    ReplyDelete
  23. Does anyone know where i can read up on the Select options? I'm scanning video files (avi mkv etc) and I want Name (FullName), Filesize (Length) and the actual length of the video, which windows is capable of knowing in explorer, but I cant find documentation to figure out the right syntax here.

    ReplyDelete
    Replies
    1. I use the difference between CreationTime and LastWriteTime to obtain the video duration.

      Delete
  24. Cannot edit previous comment. This code does what I described

    $Directory = "f:\movies\"
    $Shell = New-Object -ComObject Shell.Application
    Get-ChildItem -Path $Directory -Recurse -Force | ForEach {
    $Folder = $Shell.Namespace($_.DirectoryName)
    $File = $Folder.ParseName($_.Name)
    $Duration = $Folder.GetDetailsOf($File, 27)
    [PSCustomObject]@{
    Name = $_.Name
    Size = "$([int]($_.length / 1mb)) MB"
    Duration = $Duration
    }
    } | Export-Csv -Path "f:\movies.csv" -NoTypeInformation

    ReplyDelete

I bought a dashcam from Temu for $6.31 January 2024, and here is sample footage that includes three scenes: daytime, dusk, and daytime. ...