使用Sylvan.Data.Csv导入CSV至SQL Server的Schema配置问题

问题背景 #

需在PowerShell 7中处理多GB级特殊CSV文件（首行为文件元信息需跳过、存在带换行的引号字段、日期格式不统一、部分记录缺失尾随分隔符），并通过SQL Server目标表Schema避免SqlBulkCopy的数据类型错误，但尝试直接映射Schema时失败：

• 直接赋值$tableSchema到readerOptions.Schema时，出现类型转换错误（无法将ReadOnlyCollection<DbColumn>转为ICsvSchemaProvider）
• 尝试通过CsvSchema构造函数包装Schema后，CsvDataReader.Schema显示为空的[CsvSchema]实例

错误原因 #

Sylvan.Data.Csv.CsvSchema无法直接接收SQL Server返回的DbColumn集合或Sylvan.Data.Schema对象，必须手动将SQL Server列元数据转换为CsvColumn定义后，才能构建合法的CsvSchema；同时原代码错误设置HasHeaders = $true，不符合CSV首行无数据表头的实际情况。

解决方案步骤 #

1. 从SQL Server目标表读取列元数据
2. 将DbColumn逐一转换为CsvColumn，指定数据类型、列名、允许空属性及特殊格式规则
3. 用转换后的CsvColumn集合构建合法的CsvSchema
4. 配置CSV读取选项：跳过首行、禁用表头识别、适配CSV的分隔符/引号规则
5. 用SqlBulkCopy高效导入数据

完整实现代码 #

$ModuleDir = "C:\TEST\Modules"
# 加载依赖程序集
Add-Type -AssemblyName System.Data
Add-Type -Path (Join-Path $ModuleDir "Microsoft.Data.SqlClient.dll") -ReferencedAssemblies Microsoft.Data.SqlClient.SNI
Add-Type -Path (Join-Path $ModuleDir "Sylvan.Data.Csv.dll") -ErrorAction Stop

# 1. 从SQL Server获取目标表列元数据
$Database = "TEST"
$Schema = "DBO"
$Table = "TEST1"
$connString = "Data Source=<你的数据源地址>;Initial Catalog=$Database;Integrated Security=True;"

$tableSchema = $null
using ($conn = [Microsoft.Data.SqlClient.SqlConnection]::new($connString)) {
    $conn.Open()
    $sql = "SELECT TOP 0 * FROM $Schema.$Table;"
    using ($cmd = [Microsoft.Data.SqlClient.SqlCommand]::new($sql, $conn)) {
        using ($reader = $cmd.ExecuteReader()) {
            $tableSchema = $reader.GetColumnSchema()
        }
    }
}

# 2. 将DbColumn转换为CsvColumn并构建CsvSchema
$csvColumns = [System.Collections.Generic.List[Sylvan.Data.Csv.CsvColumn]]::new()
foreach ($dbCol in $tableSchema) {
    $csvCol = [Sylvan.Data.Csv.CsvColumn]::new()
    $csvCol.Name = $dbCol.ColumnName
    $csvCol.DataType = $dbCol.DataType
    $csvCol.AllowNull = $dbCol.AllowDBNull
    
    # 针对特殊类型配置解析规则，示例：日期格式
    if ($dbCol.DataType -eq [DateTime]) {
        # 根据CSV实际日期格式调整，如yyyy-MM-dd HH:mm:ss、MM/dd/yyyy等
        $csvCol.Format = "yyyy-MM-dd HH:mm:ss"
    }
    $csvColumns.Add($csvCol)
}
$csvSchema = [Sylvan.Data.Csv.CsvSchema]::new($csvColumns)

# 3. 配置CSV读取选项（适配特殊CSV格式）
$file = "C:\TEST\file1.csv"
$readerOptions = [Sylvan.Data.Csv.CsvDataReaderOptions]::new()
$readerOptions.HasHeaders = $false  # CSV数据行无表头
$readerOptions.SkipRows = 1         # 跳过首行文件元信息
$readerOptions.Delimiter = ','
$readerOptions.Quote = '"'
$readerOptions.Escape = '"'
$readerOptions.Schema = $csvSchema

# 4. 用SqlBulkCopy批量导入数据
using ($csvReader = [Sylvan.Data.Csv.CsvDataReader]::Create($file, $readerOptions)) {
    using ($bulkCopy = [Microsoft.Data.SqlClient.SqlBulkCopy]::new($connString)) {
        $bulkCopy.DestinationTableName = "$Schema.$Table"
        # 大文件导入优化配置
        $bulkCopy.BatchSize = 10000
        $bulkCopy.BulkCopyTimeout = 300
        $bulkCopy.WriteToServer($csvReader)
    }
}

关键优化点 #

• Schema映射：手动转换列元数据确保CsvSchema包含有效类型定义，解决原代码中Schema为空的问题
• CSV适配：通过SkipRows = 1跳过首行文件信息，HasHeaders = $false适配无数据表头的格式
• 性能优化：设置BatchSize降低内存占用，BulkCopyTimeout避免大文件导入超时
• 格式兼容：针对日期等特殊类型指定解析格式，解决CSV与SQL Server的格式不匹配问题

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