I have a hard drive full of backups that I need to detail on a spreadsheet quickly and in the following specific way. I'm hoping there's an easy way to do what i need via some sort of .bat or .cmd file but i don't know where to start.

All i need is the first two folder levels in the hierarchy to go to csv:

Folder Structure

And this is the data i need to export from the drive:

Data for Excel

I have attempted to use the following code in powershell:

cd\ c: <--- where my test folder resides


cd\ test <--- contains my test folder structures to output

then this which i found before posting my question.

Get-ChildItem -Recurse -Directory -Depth 3 |
    Select-Object FullName |
    Export-Csv Test.csv -NoTypeInformation

And i realise that i somehow need to use Select-Object to specify more folder properties to output to the csv but i can't seem to get the above to work in the basic way it should.

Instead i get the following error thrown back at me:

Get-ChildItem : A parameter cannot be found that matches parameter name 'Directory'.
At line:1 char:34
+ Get-ChildItem -Recurse -Directory <<<<  -Depth 3 |
    + CategoryInfo          : InvalidArgument: (:) [Get-ChildItem], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

I have also tried importing my test folder into Notepad++ and i'm able to see it as a Workspace i can browse but unable to export to csv with the information i need. The only options i can see there is what you see in the screenshot when i right click the directories and no option to output (I'm no expert in this).


The other thing i tried was creating a test.cmd file with the following inside it:

dir >> C:\TEST\test.csv

After running this, it appears to be bringing me a tad closer to what i'm looking for but i'm still not seeing the information i need as specified in my original excel screenshot at the top of this post:

output.csv from .cmd file

As you can see i'm lacking the proper directory path and all i see is <DIR> instead, i also can't see the second level in the folder structure containing the sub-folders. The file count and size for each folder and the sorting into columns is also not correct.

Can anyone with the knowledge of command based database creation help me? My question was put on hold because it wasn't detailed enough but i've now put in the maximum effort to explain everything i've tried and with over 2 petabytes of storage backups to spreadsheet from our Nearline storage I need to be able to do this quickly and efficiently.

Many thanks in advance.

  • What is the number in Column D ("Folder count")? The number of sub folders under each of Folder A, Folder B, and Folder C? – Michael Harvey Nov 30 '18 at 19:54
  • Might be easier to just run WinDirStat instead of trying to write the code yourself. – Bill_Stewart Nov 30 '18 at 19:56
  • @Bill_Stewart, thanks for your input. We've been looking at an app called Folder Size made by MindGems. We may have to resort to purchasing a license for it. I'm still pursuing the command/powershell road for now but can't seem to get it to work. Thanks for your feedback. – Myles Dec 4 '18 at 11:39
  • Are you open to the option of installing Cygwin, Windows Subsystem for Linux, or some other Unix-like software,  and using a shell script? – Scott Dec 5 '18 at 21:15

I agree with Bill_Stuart on the WinDirStat deal... (though I've have not needed to use it in years) Yet, if you say you must, then maybe something like the below, of course change the calculation to whatever you choose for the size thing.

Get-ChildItem -Path 'd:\temp' -Recurse -Directory -Depth 3 | 
Select-Object Name,FullName,
@{Name='FolderCount';Expression = {(Get-ChildItem -Path $_.FullName -Directory -Recurse | Measure-Object).Count}},
@{Name='FileCount';Expression = {(Get-ChildItem -Path $_.FullName -File -Recurse| Measure-Object).Count}},
@{Name='SizeMB';Expression = {(Get-ChildItem $_.FullName -Recurse | Measure-Object -Property Length -Sum).Sum / 1MB}} `
| Format-Table -AutoSize

# Results

Name           FullName                               FolderCount FileCount               SizeMB
----           --------                               ----------- ---------               ------
abcpath0       D:\temp\abcpath0                                 2         5    0.005889892578125
abcpath1       D:\temp\abcpath1                                 2         5    0.005889892578125
abcpath2       D:\temp\abcpath2                                 2         5    0.005889892578125
Duplicates     D:\temp\Duplicates                              24     12677     88.9826745986938
EmptyFolder    D:\temp\EmptyFolder                              0         0                    0
NewFiles       D:\temp\NewFiles                                 0         4 0.000568389892578125
  • Hi @Postanote, i appreciate your feedback on this. We've been looking into a piece of software called Folder Size made by MindGems. We might have to resort purchasing a license for it if we can't get what we need through manual labours. Thanks for all your input. – Myles Dec 4 '18 at 11:33

Try working with the below in Powershell.

$dir = "C:\"

$results = @()

gci $dir -Recurse -Depth 1 | % { 

    $temp = [ordered]@{
        NAME            = $_
        SIZE            = "{0:N2} MB" -f ((gci $_.Fullname -Recurse | measure -Property Length -Sum -ErrorAction SilentlyContinue).Sum / 1MB)
        FILE_COUNT      = (gci -File $_.FullName -Recurse | measure | select -ExpandProperty Count)
        FOLDER_COUNT    = (gci -Directory $_.FullName -Recurse | measure | select -ExpandProperty Count)
        DIRECTORY_PATH  = $_.Fullname

    $results += New-Object PSObject -Property $temp


$results | export-csv -Path "C:\output.csv" -NoTypeInformation

enter image description here

Calculating directory sizes

Temporary array to CSV

  • Hi root. Thanks for your answer. I tried to pursue your suggestion through powershell because your screenshot there seems to display what we need. Unfortunately i ran into the following issue where it flags to me that Depth 1 is invalid – Myles Dec 4 '18 at 11:30
  • Get-ChildItem : A parameter cannot be found that matches parameter name 'Depth'. At line:1 char:25 + gci $dir -Recurse -Depth <<<< 1 | % { + CategoryInfo : InvalidArgument: (:) [Get-ChildItem], ParameterBindingException + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand – Myles Dec 4 '18 at 11:31
  • @Myles The Depth parameter was introduced in Powershell 5. I assume you are on an older version. You can update to Powershell 5.1 with the Windows Management Framework 5.1 (docs.microsoft.com/en-us/powershell/wmf/overview). Alternatively you could try just removing "-Depth 1" from the command and seeing how that works for you? – Patrick Dec 4 '18 at 14:05
  • @Myles More, the reason -Directory is an invalid parameter as mentioned in your original post is this flag was introduced in PS v3. You are running either version 1 or 2, which can be verified by running $PSVersionTable.PSVersion. I recommend following Patrick's suggestion for updating Powershell to be able to leverage full current functionality. – root Dec 4 '18 at 16:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.