##########################
# NEW ADDITION TO SCRIPT #
##########################

# TreeSize File Path
$excelFilePath = "V:\Data\MediaFileDataFromTreeSize.xlsx"

# Import the Excel module
Import-Module ImportExcel

# Import data from each sheet in the Excel file
$sheets = Get-ExcelSheetInfo -Path $excelFilePath

foreach ($sheet in $sheets) {

    # Import data from Excel sheet to a PowerShell variable and skip first 4 rows
    $data = Import-Excel -Path $excelFilePath -WorksheetName $sheet.Name | Select-Object -Skip 4

    # Map Excel column names to SQL Server column names
    $columnMapping = @{
        'Name' = 'full_path';
        'Folder Path' = 'folder_path';
        'Folders' = 'folders';
        'Files' = 'files';
        'Size' = 'size';
        'Avg. File Size' = 'avg_size';
        'Space free' = 'free_space';
        'Dir Level (Relative)' = 'directory_level';
        'File Extension' = 'file_extension';
        'Last Modified' = 'date_modified';
        'Creation Date' = 'date_created';
        'Current Date' = 'date_imported';
    }

    # Iterate through each row and generate SQL Server INSERT statement with correct column names
    foreach ($row in $data) {

        # Make Sure All Row Data Has a Closing Quotation Mark"
        $quotedValues = $columnMapping.Keys | ForEach-Object { "'$($row.$_ -replace "'", "''")'" }
        $sqlRows = $quotedValues -join ', '
        $insertQuery = "INSERT INTO $tableName ($($columnMapping.Values -join ', ')) VALUES ($sqlRows)"
        Invoke-Sqlcmd -Query $insertQuery -ConnectionString $connectionString
    }
}