For a 2 TB hard disk, I recommend making a proper Excel workbook with these sheets:
- Folders — every folder and its path
- Files — every file, path, extension, size, dates
-
File Types —
.jpg,.pdf,.mp4, etc. with file count and total size - Folder Usage — storage used by each top-level folder
- Summary — total files, folders, size, scan date
Because a 2 TB disk can contain more than 1 million files, the script below automatically creates Files_1, Files_2, Files_3, etc. when necessary because Excel has a row limit.
Before running
This version uses the Microsoft Excel installed on your PC to create the .xlsx file. You do not need to install a PowerShell Excel module.
Change this line:
to your actual hard-disk drive letter, for example:
Complete PowerShell script
Save this as:
Create-HardDiskInventory.ps1
# ============================================================ # HARD DISK INVENTORY TO EXCEL # ============================================================ # Creates: # 1. Summary # 2. Folders # 3. Files_1, Files_2, ... # 4. File Types # 5. Folder Usage # # Requires: # - Windows # - Microsoft Excel installed # # ============================================================ # ----------------------------- # CONFIGURATION # ----------------------------- $Drive = "D:\" $OutputFile = Join-Path ` ([Environment]::GetFolderPath("Desktop")) ` "HardDisk_Inventory.xlsx" # Number of data rows per Excel Files sheet $MaxRowsPerSheet = 1000000 # Number of records written to Excel at one time $BatchSize = 5000 # ----------------------------- # CHECK DRIVE # ----------------------------- if (-not (Test-Path $Drive)) { Write-Host "ERROR: Drive not found: $Drive" -ForegroundColor Red exit } Write-Host "" Write-Host "============================================" -ForegroundColor Cyan Write-Host " HARD DISK INVENTORY" -ForegroundColor Cyan Write-Host "============================================" -ForegroundColor Cyan Write-Host "" Write-Host "Drive : $Drive" Write-Host "Output: $OutputFile" Write-Host "" Write-Host "Scanning... Please wait." Write-Host "" # ----------------------------- # DELETE OLD OUTPUT # ----------------------------- if (Test-Path $OutputFile) { Remove-Item $OutputFile -Force } # ----------------------------- # START EXCEL # ----------------------------- try { $Excel = New-Object -ComObject Excel.Application } catch { Write-Host "" Write-Host "Microsoft Excel is not installed or cannot be started." -ForegroundColor Red Write-Host "" exit } $Excel.Visible = $false $Excel.DisplayAlerts = $false $Workbook = $Excel.Workbooks.Add() # ----------------------------- # REMOVE DEFAULT SHEETS # ----------------------------- while ($Workbook.Worksheets.Count -gt 1) { $Workbook.Worksheets.Item($Workbook.Worksheets.Count).Delete() } # ----------------------------- # SUMMARY SHEET # ----------------------------- $SummarySheet = $Workbook.Worksheets.Item(1) $SummarySheet.Name = "Summary" # ----------------------------- # FOLDERS SHEET # ----------------------------- $FoldersSheet = $Workbook.Worksheets.Add() $FoldersSheet.Name = "Folders" $folderHeaders = @( "Folder Name", "Full Path", "Parent Folder", "Created Date", "Modified Date" ) $FoldersSheet.Range("A1:E1").Value2 = ,$folderHeaders # ----------------------------- # FILE TYPE DICTIONARY # ----------------------------- $fileTypes = @{} # ----------------------------- # TOP LEVEL FOLDER DICTIONARY # ----------------------------- $folderUsage = @{} # ----------------------------- # COUNTERS # ----------------------------- $totalFiles = 0 $totalFolders = 0 $totalBytes = [int64]0 $scanStart = Get-Date # ============================================================ # FUNCTION: WRITE BATCH TO EXCEL # ============================================================ function Write-ExcelBatch { param ( [Parameter(Mandatory=$true)] $Worksheet, [Parameter(Mandatory=$true)] [System.Collections.ArrayList]$Rows, [Parameter(Mandatory=$true)] [int]$StartRow, [Parameter(Mandatory=$true)] [int]$ColumnCount ) if ($Rows.Count -eq 0) { return } $rowCount = $Rows.Count $Data = New-Object 'object[,]' $rowCount,$ColumnCount for ($r = 0; $r -lt $rowCount; $r++) { for ($c = 0; $c -lt $ColumnCount; $c++) { $Data[$r,$c] = $Rows[$r][$c] } } $EndRow = $StartRow + $rowCount - 1 $Range = $Worksheet.Range( $Worksheet.Cells($StartRow,1), $Worksheet.Cells($EndRow,$ColumnCount) ) $Range.Value2 = $Data [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Range) | Out-Null $Rows.Clear() } # ============================================================ # FILE SHEET CREATION # ============================================================ $fileSheetNumber = 1 $fileSheetRow = 2 function New-FileSheet { param ( [int]$Number ) $sheetName = "Files_$Number" Write-Host "Creating sheet: $sheetName" $sheet = $Workbook.Worksheets.Add() $sheet.Name = $sheetName $headers = @( "File Name", "Full Path", "Folder", "Extension", "Size (Bytes)", "Size (MB)", "Size (GB)", "Created Date", "Modified Date", "Last Access Date" ) $sheet.Range("A1:J1").Value2 = ,$headers return $sheet } $FileSheet = New-FileSheet -Number $fileSheetNumber # ============================================================ # DATA ARRAYS # ============================================================ $fileRows = New-Object System.Collections.ArrayList $folderRows = New-Object System.Collections.ArrayList # ============================================================ # SCAN FOLDERS AND FILES # ============================================================ try { foreach ($item in [System.IO.Directory]::EnumerateFileSystemEntries( $Drive, "*", [System.IO.SearchOption]::AllDirectories )) { try { $attributes = [System.IO.File]::GetAttributes($item) # ----------------------------------------------- # FILE # ----------------------------------------------- if (($attributes -band [System.IO.FileAttributes]::Directory) -eq 0) { $fileInfo = New-Object System.IO.FileInfo($item) $totalFiles++ $totalBytes += $fileInfo.Length # Extension $extension = $fileInfo.Extension.ToLower() if ([string]::IsNullOrWhiteSpace($extension)) { $extension = "(No Extension)" } # ------------------------------------------- # FILE TYPE STATISTICS # ------------------------------------------- if (-not $fileTypes.ContainsKey($extension)) { $fileTypes[$extension] = @{ Count = [int64]0 Bytes = [int64]0 } } $fileTypes[$extension].Count++ $fileTypes[$extension].Bytes += $fileInfo.Length # ------------------------------------------- # TOP LEVEL FOLDER # ------------------------------------------- $relativePath = $item.Substring($Drive.Length) $parts = $relativePath.Split( [char]'\', [System.StringSplitOptions]::RemoveEmptyEntries ) if ($parts.Count -gt 1) { $topFolder = $parts[0] } else { $topFolder = "(Root)" } if (-not $folderUsage.ContainsKey($topFolder)) { $folderUsage[$topFolder] = @{ Count = [int64]0 Bytes = [int64]0 } } $folderUsage[$topFolder].Count++ $folderUsage[$topFolder].Bytes += $fileInfo.Length # ------------------------------------------- # ADD FILE TO EXCEL BATCH # ------------------------------------------- $row = @( $fileInfo.Name, $fileInfo.FullName, $fileInfo.DirectoryName, $extension, $fileInfo.Length, [math]::Round($fileInfo.Length / 1MB, 2), [math]::Round($fileInfo.Length / 1GB, 2), $fileInfo.CreationTime.ToString("yyyy-MM-dd HH:mm:ss"), $fileInfo.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"), $fileInfo.LastAccessTime.ToString("yyyy-MM-dd HH:mm:ss") ) [void]$fileRows.Add($row) # ------------------------------------------- # WRITE FILE BATCH # ------------------------------------------- if ($fileRows.Count -ge $BatchSize) { Write-ExcelBatch ` -Worksheet $FileSheet ` -Rows $fileRows ` -StartRow $fileSheetRow ` -ColumnCount 10 $fileSheetRow += $BatchSize # --------------------------------------- # EXCEL ROW LIMIT # --------------------------------------- if ($fileSheetRow -gt $MaxRowsPerSheet) { $fileSheetNumber++ $FileSheet = New-FileSheet ` -Number $fileSheetNumber $fileSheetRow = 2 } } # ------------------------------------------- # PROGRESS # ------------------------------------------- if (($totalFiles % 10000) -eq 0) { $sizeGB = [math]::Round( $totalBytes / 1GB, 2 ) Write-Host ` "Files: $totalFiles | Size: $sizeGB GB" ` -ForegroundColor Green } } # ----------------------------------------------- # FOLDER # ----------------------------------------------- else { $dirInfo = New-Object System.IO.DirectoryInfo($item) $totalFolders++ $parent = "" if ($null -ne $dirInfo.Parent) { $parent = $dirInfo.Parent.FullName } $row = @( $dirInfo.Name, $dirInfo.FullName, $parent, $dirInfo.CreationTime.ToString("yyyy-MM-dd HH:mm:ss"), $dirInfo.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss") ) [void]$folderRows.Add($row) if ($folderRows.Count -ge $BatchSize) { Write-ExcelBatch ` -Worksheet $FoldersSheet ` -Rows $folderRows ` -StartRow ($totalFolders - $BatchSize + 2) ` -ColumnCount 5 } } } catch { # Ignore files/folders that Windows refuses to access continue } } } catch { Write-Host "" Write-Host "Some folders could not be scanned." -ForegroundColor Yellow } # ============================================================ # WRITE REMAINING FILES # ============================================================ if ($fileRows.Count -gt 0) { Write-ExcelBatch ` -Worksheet $FileSheet ` -Rows $fileRows ` -StartRow $fileSheetRow ` -ColumnCount 10 } # ============================================================ # WRITE REMAINING FOLDERS # ============================================================ if ($folderRows.Count -gt 0) { $startFolderRow = $totalFolders - $folderRows.Count + 2 Write-ExcelBatch ` -Worksheet $FoldersSheet ` -Rows $folderRows ` -StartRow $startFolderRow ` -ColumnCount 5 } # ============================================================ # FILE TYPES SHEET # ============================================================ $TypeSheet = $Workbook.Worksheets.Add() $TypeSheet.Name = "File Types" $typeHeaders = @( "Extension", "File Count", "Total Size (Bytes)", "Total Size (MB)", "Total Size (GB)" ) $TypeSheet.Range("A1:E1").Value2 = ,$typeHeaders $typeRow = 2 foreach ($ext in ($fileTypes.Keys | Sort-Object)) { $count = $fileTypes[$ext].Count $bytes = $fileTypes[$ext].Bytes $TypeSheet.Cells($typeRow,1).Value2 = $ext $TypeSheet.Cells($typeRow,2).Value2 = $count $TypeSheet.Cells($typeRow,3).Value2 = $bytes $TypeSheet.Cells($typeRow,4).Value2 = [math]::Round($bytes / 1MB, 2) $TypeSheet.Cells($typeRow,5).Value2 = [math]::Round($bytes / 1GB, 2) $typeRow++ } # ============================================================ # FOLDER USAGE SHEET # ============================================================ $UsageSheet = $Workbook.Worksheets.Add() $UsageSheet.Name = "Folder Usage" $usageHeaders = @( "Top Level Folder", "File Count", "Total Size (Bytes)", "Total Size (MB)", "Total Size (GB)" ) $UsageSheet.Range("A1:E1").Value2 = ,$usageHeaders $usageRow = 2 foreach ($folder in ($folderUsage.Keys | Sort-Object)) { $count = $folderUsage[$folder].Count $bytes = $folderUsage[$folder].Bytes $UsageSheet.Cells($usageRow,1).Value2 = $folder $UsageSheet.Cells($usageRow,2).Value2 = $count $UsageSheet.Cells($usageRow,3).Value2 = $bytes $UsageSheet.Cells($usageRow,4).Value2 = [math]::Round($bytes / 1MB, 2) $UsageSheet.Cells($usageRow,5).Value2 = [math]::Round($bytes / 1GB, 2) $usageRow++ } # ============================================================ # SUMMARY # ============================================================ $scanEnd = Get-Date $duration = $scanEnd - $scanStart $SummarySheet.Cells(1,1).Value2 = "HARD DISK INVENTORY" $SummarySheet.Cells(3,1).Value2 = "Drive" $SummarySheet.Cells(3,2).Value2 = $Drive $SummarySheet.Cells(4,1).Value2 = "Scan Started" $SummarySheet.Cells(4,2).Value2 = $scanStart.ToString("yyyy-MM-dd HH:mm:ss") $SummarySheet.Cells(5,1).Value2 = "Scan Completed" $SummarySheet.Cells(5,2).Value2 = $scanEnd.ToString("yyyy-MM-dd HH:mm:ss") $SummarySheet.Cells(6,1).Value2 = "Scan Duration" $SummarySheet.Cells(6,2).Value2 = $duration.ToString() $SummarySheet.Cells(8,1).Value2 = "Total Folders" $SummarySheet.Cells(8,2).Value2 = $totalFolders $SummarySheet.Cells(9,1).Value2 = "Total Files" $SummarySheet.Cells(9,2).Value2 = $totalFiles $SummarySheet.Cells(10,1).Value2 = "Total Size (Bytes)" $SummarySheet.Cells(10,2).Value2 = $totalBytes $SummarySheet.Cells(11,1).Value2 = "Total Size (GB)" $SummarySheet.Cells(11,2).Value2 = [math]::Round($totalBytes / 1GB, 2) $SummarySheet.Cells(12,1).Value2 = "Total Size (TB)" $SummarySheet.Cells(12,2).Value2 = [math]::Round($totalBytes / 1TB, 2) # ============================================================ # FORMAT ALL WORKSHEETS # ============================================================ Write-Host "" Write-Host "Formatting Excel workbook..." -ForegroundColor Cyan foreach ($sheet in $Workbook.Worksheets) { try { $usedRange = $sheet.UsedRange # Header row $headerRange = $sheet.Range( $sheet.Cells(1,1), $sheet.Cells(1,$usedRange.Columns.Count) ) $headerRange.Font.Bold = $true # Autofilter if ($usedRange.Rows.Count -gt 1) { $usedRange.AutoFilter() } # Freeze first row $sheet.Activate() $Excel.ActiveWindow.SplitRow = 1 $Excel.ActiveWindow.FreezePanes = $true # Reasonable column width $usedRange.Columns.AutoFit() # Limit extremely wide columns foreach ($column in $usedRange.Columns) { if ($column.ColumnWidth -gt 60) { $column.ColumnWidth = 60 } } [System.Runtime.InteropServices.Marshal]::ReleaseComObject($usedRange) | Out-Null [System.Runtime.InteropServices.Marshal]::ReleaseComObject($headerRange) | Out-Null } catch { continue } } # ============================================================ # ACTIVATE SUMMARY # ============================================================ $SummarySheet.Activate() # ============================================================ # SAVE XLSX # ============================================================ Write-Host "" Write-Host "Saving Excel file..." -ForegroundColor Cyan $xlOpenXMLWorkbook = 51 $Workbook.SaveAs( $OutputFile, $xlOpenXMLWorkbook ) $Workbook.Close($true) $Excel.Quit() # ============================================================ # CLEANUP COM OBJECTS # ============================================================ [System.Runtime.InteropServices.Marshal]::ReleaseComObject($SummarySheet) | Out-Null [System.Runtime.InteropServices.Marshal]::ReleaseComObject($FoldersSheet) | Out-Null [System.Runtime.InteropServices.Marshal]::ReleaseComObject($TypeSheet) | Out-Null [System.Runtime.InteropServices.Marshal]::ReleaseComObject($UsageSheet) | Out-Null [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Workbook) | Out-Null [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null [GC]::Collect() [GC]::WaitForPendingFinalizers() # ============================================================ # FINISHED # ============================================================ Write-Host "" Write-Host "============================================" -ForegroundColor Green Write-Host " SCAN COMPLETED" -ForegroundColor Green Write-Host "============================================" -ForegroundColor Green Write-Host "" Write-Host "Total Folders : $totalFolders" Write-Host "Total Files : $totalFiles" Write-Host "Total Size : $([math]::Round($totalBytes / 1GB,2)) GB" Write-Host "" Write-Host "Excel File:" Write-Host $OutputFile -ForegroundColor Yellow Write-Host ""
How to run it
1. Find your hard disk drive letter
Open This PC.
For example, if your 2 TB disk is:
then use:
2. Save the script
Open Notepad, paste the complete script and save it as:
Create-HardDiskInventory.ps1
For example:
C:\Users\YourName\Desktop\Create-HardDiskInventory.ps1
3. Open PowerShell
Right-click Start → Windows PowerShell or Terminal.
Run:
Then:
Then:
The resulting Excel file
It will be created on your Desktop:
HardDisk_Inventory.xlsx
The workbook will look approximately like:
HardDisk_Inventory.xlsx │ ├── Summary │ ├── Folders │ ├── Files_1 ├── Files_2 ├── Files_3 │ ... │ ├── File Types │ └── Folder Usage
Example: File Types
| Extension | File Count | Total Size (MB) | Total Size (GB) |
|---|---|---|---|
| .jpg | 125,430 | 84,521 | 82.54 |
| .mp4 | 12,850 | 485,210 | 473.84 |
| 45,210 | 18,430 | 17.99 | |
| .docx | 8,520 | 4,230 | 4.13 |
Example: Folder Usage
| Top Level Folder | File Count | Total Size (GB) |
|---|---|---|
| Documents | 125,420 | 85.42 |
| Photos | 425,210 | 620.51 |
| Videos | 28,520 | 850.32 |
| Backup | 52,120 | 310.25 |
One important warning
For a 2 TB disk with a very large number of files, the Excel workbook itself can become extremely large. The script handles the Excel row limit by splitting files across Files_1, Files_2, etc., but scanning can still take hours depending on the number of files and disk speed.
Also, files/folders where Windows denies access will be skipped rather than stopping the entire scan.
