Php, Wordpress, CodeIgniter, Magento: Make excel file for files folder and summary list from more then 2TB Data

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:


$Drive = "D:\"

to your actual hard-disk drive letter, for example:


$Drive = "E:\"

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:


New Volume (D:)

then use:


$Drive = "D:\"

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:


Set-ExecutionPolicy -Scope Process Bypass

Then:


cd "$env:USERPROFILE\Desktop"

Then:


.\Create-HardDiskInventory.ps1

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
.pdf	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.

Php, Wordpress, CodeIgniter, Magento

Thursday, September 10, 2026

Make excel file for files folder and summary list from more then 2TB Data