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.

21 comments:

  1. This was super helpful. Thanks

    ReplyDelete
  2. 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
  3. Matthew: to limit to folders add "| ?{ $_.PSIsContainer }" before "| ForEach-Object" (no quotation marks)

    ReplyDelete
  4. 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
  5. 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
  6. 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
  7. Brainwashed: as a guess, make sure the directory exists and is not empty.

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

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

    ReplyDelete
  10. @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
  11. This comment has been removed by the author.

    ReplyDelete
  12. 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
  13. is there a way to list folder only?

    thanks

    ReplyDelete
  14. 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
  15. Hi Andrew, how about scanning all directories of a PC for csv, txt,tav, xlsx,xls files?

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

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

    ReplyDelete
  18. 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