7/27/2017 - Powershell, Excel - Get directory listing and size of sub-folders
Today I was instructed to get a list of all Employee $Home directories and sort by size.
For everything Windows I try to leverage powershell to get what I want. Most the times its a challenge because Linux one-liners are very temping, especially since powershell has many versions and multiple methods of achieving a goal when a simple du command would suffice.
Powershell command:
GCI -Path \\servername\home$\*\* | Group-Object -Property Directory | % {New-Object PSObject -Property @{Name=$_.Name;Size=($_.Group | ? {!($_.PSIsContainer)} | Measure-Object Length -sum).Sum}} | Sort-Object -Property Size -Descending | Export-Csv -Path C:\Temp\Export8.csv -Encoding "Unicode" -Delimiter ";"
\\servername\home$ is the share path
\*\* limits the sub-directory to a depth of 1 (If I use -Recurse, it would take a long time as it would try to show every sub-folder inside each users directory, all I want is total per user folder)
C:\Temp\Export8.csv is the folder I want to pipe the data to so that I can open in Excel to format.
-Delimiter ";" sets the delimiter to semicolon
Output example:
PS C:\Windows\system32> GCI -Path \\serername\home$\*\* | Group-Object -Property
Directory | % {New-Object PSObject -Property @{Name=$_.Name;Size=($_.Group | ?
{!($_.PSIsContainer)} | Measure-Object Length -sum).Sum}} | Sort-Object -Propert
y Size -Descending
Name Size
---- ----
\\servername\home$\black 367889029
\\servername\home$\green 320738389
\\servername\home$\orange 172060451
\\servername\home$\yellow 95752866
\\servername\home$\gray 90402207
\\servername\home$\magenta 46088128
\\servername\home$\forest 44746623
\\servername\home$\leaf 29755776
\\servername\home$\tree 28798576
\\servername\home$\rose 26395975
\\servername\home$\lily 22757376
\\servername\home$\violet 17777935
Excel:
In order to make the file sizes resemble KB, MB, GB, or to prettify them so that they are readable to people that don't like counting digits, we format them in Excel.
Create new spreadsheet in Excel
Click on 'Data' tab
Get External Data -> From Text File
Select Delimited, Select My Data has Headers (NEXT)
The delimiter set by the powershell script is semi-colon
(deselect Tab, select Semicolon) (FINISH)
Last Select Okay to accept defaults.
You should now have your folders in one column, and the size in another.
To format the size to be readable:
Select the column needed to format (Size)
Right click column header
Choose "Format Cells"
Choose the "Custom" option under the "Number" tab
Paste this value - [<1000]#,##0.00" KB ";[<1000000]#,##0.00," MB";#,##0.00,," GB"
And click OK
You should have something that look like:
