PowerShell导入Excel至SQL Server时如何添加文件名作为导入列

该需求可直接通过PowerShell的Select-Object计算属性语法实现，你之前添加不生效是没有使用自定义列的正确声明方式，同时原有代码存在变量未定义的小错误，修正方案如下：

修正后的单文件导入代码 #

$FileDirectory = "C:\QualityReport\"
$FileName = "QualityReport05142021.xlsx"    
$BatchFileName = -JOIN ($FileDirectory,$FileName)    
Write-Host $BatchFileName    
$SQLInstance = "localhost"
$Database = "GenericDB"    
# 修正点1：Import-Excel的Path参数使用已定义的$BatchFileName，修复原代码$ImportFile未定义的错误
# 修正点2：通过计算属性新增BatchFileName自定义列
$data = Import-Excel -Path $BatchFileName -WorksheetName "Sorted" | Select-Object "IP","Network","DNS","NetBIOS","Tracking Method","OS","IP Status","QID","Title","Vuln Status","Type","Severity","Port","Protocol","FQDN","SSL",@{
    Name = 'BatchFileName'
    Expression = {$BatchFileName}
}  
Write-DbaDataTable -SqlInstance $SQLInstance -Database $Database -Table "dbo.QualityReport" -InputObject $data -Truncate

批量遍历文件夹导入改造方案 #

• 用Get-ChildItem遍历目标目录下所有Excel文件，循环处理每个文件
• 批量导入时如果不需要清空之前的数据，要去掉Write-DbaDataTable的-Truncate参数，避免每次导入都清空目标表

批量导入示例代码：

$FileDirectory = "C:\QualityReport\"
$SQLInstance = "localhost"
$Database = "GenericDB"  
# 遍历目录下所有xlsx文件
Get-ChildItem -Path $FileDirectory -Filter "*.xlsx" -File | ForEach-Object {
    $BatchFileName = $_.FullName
    Write-Host "正在导入文件：$BatchFileName"
    $data = Import-Excel -Path $BatchFileName -WorksheetName "Sorted" | Select-Object "IP","Network","DNS","NetBIOS","Tracking Method","OS","IP Status","QID","Title","Vuln Status","Type","Severity","Port","Protocol","FQDN","SSL",@{
        Name = 'BatchFileName'
        Expression = {$BatchFileName}
    }
    # 首次全量导入可保留-Truncate，后续增量导入删除该参数即可
    Write-DbaDataTable -SqlInstance $SQLInstance -Database $Database -Table "dbo.QualityReport" -InputObject $data
}

注意事项 #

• 提前确认目标表dbo.QualityReport已存在BatchFileName字段，字段类型建议设为NVARCHAR(260)，足够存储Windows系统下的全路径
• 若Excel工作表名不固定，可自行调整-WorksheetName参数，或添加逻辑动态获取工作表名

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