如何用PowerShell的Import-Excel实现数据逆透视，将列转为行条目？

需求背景 #

已通过Import-Excel读取员工双月时间表的核心项目数据（起始行7），当前数据结构为每行对应一个项目，列对应当月日期（列名为数字1-31）。需要将这种宽表结构逆透视，转换为员工-项目-日期的单行交易格式，最终写入SQL数据库。

已知变量：

• $employee：员工编号
• $monthInt：月份（整数）
• $yearInt：年份（整数）
• $data：已读取的Excel数据数组

核心实现逻辑 #

遍历$data中的每个项目行，提取项目编号和名称，再遍历所有日期列，将有工时记录的条目转换为目标格式对象，最后批量写入SQL。

具体代码 #

# 初始化空数组存储转换后的数据
$transformedData = @()

foreach ($projectRow in $data) {
    # 提取当前项目的核心信息
    $projectNumber = $projectRow.'PROJECT NUMBER'
    $projectName = $projectRow.'PROJECT NAME'

    # 获取所有日期列（列名为数字的列）
    $dateColumns = $projectRow.PSObject.Properties | Where-Object { $_.Name -match '^\d+$' }

    foreach ($col in $dateColumns) {
        $dayInt = [int]$col.Name
        $hours = $col.Value

        # 跳过无工时的日期
        if ([string]::IsNullOrWhiteSpace($hours)) {
            continue
        }

        # 构造完整日期并校验有效性
        try {
            $entryDate = Get-Date -Year $yearInt -Month $monthInt -Day $dayInt
        }
        catch {
            Write-Warning "无效日期：$yearInt-$monthInt-$dayInt，已跳过"
            continue
        }

        # 生成目标格式的对象
        $entry = [PSCustomObject]@{
            EmployeeNumber = $employee
            ProjectNumber  = $projectNumber
            ProjectName    = $projectName
            EntryDate      = $entryDate
            HoursWorked    = [decimal]$hours # 根据SQL表字段类型调整，比如int
        }

        $transformedData += $entry
    }
}

# 将转换后的数据写入SQL
$transformedData | Write-SqlTableData -ServerInstance "myserver" -DatabaseName "mydatabase" -TableName "testTimesheetEntries" -Force

关键逻辑说明 #

1. 提取项目信息：通过PSObject.Properties访问每行属性，直接获取PROJECT NUMBER和PROJECT NAME字段。
2. 筛选日期列：用正则^\d+$匹配列名，精准筛选出日期列，排除非日期字段。
3. 日期校验：通过Get-Date构造日期对象，捕获无效日期（如2月30日）并跳过，避免写入错误数据。
4. 数据类型适配：将工时转换为与SQL表字段匹配的数值类型，确保数据写入时无类型冲突。
5. 批量写入优化：先收集所有转换后的条目，再一次性写入SQL，提升处理效率。

内容的提问来源于stack exchange，提问作者tb1