Import-Excel: Expand imported Excel columns to become individual row entries

Can someone help me figure out how to unpivot Import-Excel data so the columns can be expanded as individual row entries? This is really more of a question of how to re-render the custom PSObjects that go into the array.

More details:

I am pulling data out of several Excel spreadsheets using Import-Excel and Powershell. These are bimonthly timesheets by employee, and the columns are the dates within the month in question. I want to unpivot the columns so I can write these to SQL as transactions per date, per project (by the employee on the timesheet). I'm having trouble visualizing my next step.

The format of each spreadsheet looks something like this (for November 2024, first half):

If this header starts at (e.g.) row 7 of the spreadsheet, I can pull the core project data as follows: $data = Import-Excel -Path $excelFilePath -Sheet "sheet1" -StartRow 7

At this point if I have the SQLServer module, I could write the entries as is to SQL as follows: $data | Write-SqlTableData -ServerInstance "myserver" -DatabaseName "mydatabase" -TableName "testTimesheetEntries" -Force

That will give me some 1:1 entries in SQL, which is a great start:

If I look at $data[0] I have this:

PROJECT NUMBER : xx1
PROJECT NAME   : some project
1              :
2              :
3              :
4              : 2
5              :
6              :
7              : 1
8              :
9              :
10             :
11             : 1
12             :
13             : 1
14             : 1
15             :

But my ultimate goal is to split these out before writing to SQL so it becomes one row per project-employee-date, more like this:

The employee number, month and year are easily grabbed because these are in static cell locations, so assume they are coming in e.g. $employee, $monthInt, $yearInt: Can someone please help me visualize the logic to loop through the existing data array so these rows are in the employee-project-date format?

Thanks!!

$excelPackage = Open-ExcelPackage -Path "$path\project_hours.xlsx"

$employeeNumber = [int]$excelPackage.Workbook.Worksheets[1].Cells[5, 1].Value
$month = $excelPackage.Workbook.Worksheets[1].Cells[2, 3].Value
$year = $excelPackage.Workbook.Worksheets[1].Cells[2, 5].Value

$data = Import-Excel -ExcelPackage $excelPackage -StartRow 7

$data | ForEach-Object {
    $projectName = $_.'PROJECT NAME' 
    $projectNumber = $_.'PROJECT NUMBER' 

    # loop through the different columns which will be properties on the $data objects 
    # and filter using Where-Object only for those that are numeric (-as [int])
    $_.psobject.properties | Where-Object { $_.Name -as [int] } | ForEach-Object {
        # inside the loop $_ will be a single property (cell) of a row ($data object)
        # $_.Name will be the column/property name (day)
        # $_.Value will be the cell value (hours)
        if ($_.Value) {
            [pscustomobject]@{
                EmployeeNumber = $employeeNumber
                ProjectNumber  = $projectNumber
                EntryDate      = [datetime]::new($year, $month, $_.Name).ToString('M/d/yy')
                Hours          = $_.Value
            }
        }
    }
}

Close-ExcelPackage $excelPackage -NoSave

Output should end up looking something like this

EmployeeNumber ProjectNumber EntryDate Hours
-------------- ------------- --------- -----
          1234 xx1           11.4.24    2.00
          1234 xx1           11.7.24    1.00
          1234 xx1           11.11.24   1.00
          1234 xx1           11.13.24   1.00
          1234 xx1           11.14.24   1.00
          1234 xx2           11.4.24    1.00
          1234 xx2           11.5.24    2.00
          1234 xx2           11.6.24    1.00
          1234 xx2           11.7.24    2.00
          1234 xx2           11.11.24   1.00
          1234 xx2           11.12.24   2.00
          1234 xx2           11.13.24   1.00
          1234 xx2           11.14.24   1.00
          1234 xx3           11.5.24    1.00
          1234 xx3           11.6.24    1.00
          1234 xx3           11.11.24   1.00
          1234 xx3           11.14.24   1.00