使用PowerShell上传CSV至SQL Server遇日期转换错误，如何修复？

问题描述 #

我用PowerShell将桌面指定CSV文件的数据上传至SQL Server数据库，部分行可正常上传，但多数行返回如下错误：

"Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when converting date
and/or time from character string."
At line:3 char:5 #

$sqlCommand.ExecuteNonQuery()

- CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
  - FullyQualifiedErrorId : SqlException"

我知道是日期列的转换问题，但不清楚修复方法，相关脚本如下：

# Define variables
$serverName = "TDALYW10"
$databaseName = "ScriptTest"
$tableName = "VehicleStock"
$csvPath = "C:\Users\TDaly\Desktop\PowerShellTest2\vehicleStock.csv"

# Create a new SQL Server connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True"

# Open the SQL Server connection
$sqlConnection.Open()

# Create a new SQL Server command
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection

# Read the CSV file
$csv = Import-Csv $csvPath

# Loop through each row in the CSV file and insert it into the SQL Server table
foreach ($row in $csv) {
    $sqlCommand.CommandText = "INSERT INTO $tableName (branch, stockNo, reg, category, make, model, variant, buyInSP, purchaseInvDate, dateIn, purchaseInvRef, odometer, clock, qual, [Base & Delivery], Overallowance, WriteDown, Sublet, PDI, Accessory, Bonus, [Motor Tax], [House Charge], Finance, Other, Previous, totalCost, purchaseAmt, purchaseVAT, retail, trade, VRT, VIBE, fuel, cc, body, colour, trim, owners, year, regDate, class, transm, [new/used], daysInStock, lastMOT, nextMOT) VALUES ('$($row.branch)', '$($row.stockNo)', '$($row.reg)', '$($row.category)', '$($row.make)', '$($row.model)', '$($row.variant)', '$($row.buyInSP)', '$($row.purchaseInvDate)', '$($row.dateIn)', '$($row.purchaseInvRef)', '$($row.odometer)', '$($row.clock)', '$($row.qual)', '$($row.'Base & Delivery')', '$($row.Overallowance)', '$($row.WriteDown)', '$($row.Sublet)', '$($row.PDI)', '$($row.Accessory)', '$($row.Bonus)', '$($row.'Motor Tax')', '$($row.'House Charge')', '$($row.Finance)', '$($row.Other)', '$($row.Previous)', '$($row.totalCost)', '$($row.purchaseAmt)', '$($row.purchaseVAT)', '$($row.retail)', '$($row.trade)', '$($row.VRT)', '$($row.VIBE)', '$($row.fuel)', '$($row.cc)', '$($row.body)', '$($row.colour)', '$($row.trim)', '$($row.owners)', '$($row.year)', '$($row.regDate)', '$($row.class)', '$($row.transm)', '$($row.'new/used')', '$($row.daysInStock)', '$($row.lastMOT)', '$($row.nextMOT)')"
    $sqlCommand.ExecuteNonQuery()
}

# Close the SQL Server connection
$sqlConnection.Close()

修复方案 #

1. 使用参数化查询（推荐，同时解决SQL注入风险） #

直接拼接SQL字符串会导致日期格式不匹配，还存在SQL注入隐患。改用参数化查询，让.NET自动处理类型转换，同时兼容空值：

# Define variables
$serverName = "TDALYW10"
$databaseName = "ScriptTest"
$tableName = "VehicleStock"
$csvPath = "C:\Users\TDaly\Desktop\PowerShellTest2\vehicleStock.csv"

# Create connection and command
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True"
$sqlConnection.Open()

# 定义带参数的INSERT语句
$insertQuery = @"
INSERT INTO $tableName (branch, stockNo, reg, category, make, model, variant, buyInSP, purchaseInvDate, dateIn, purchaseInvRef, odometer, clock, qual, [Base & Delivery], Overallowance, WriteDown, Sublet, PDI, Accessory, Bonus, [Motor Tax], [House Charge], Finance, Other, Previous, totalCost, purchaseAmt, purchaseVAT, retail, trade, VRT, VIBE, fuel, cc, body, colour, trim, owners, year, regDate, class, transm, [new/used], daysInStock, lastMOT, nextMOT)
VALUES (@branch, @stockNo, @reg, @category, @make, @model, @variant, @buyInSP, @purchaseInvDate, @dateIn, @purchaseInvRef, @odometer, @clock, @qual, @BaseDelivery, @Overallowance, @WriteDown, @Sublet, @PDI, @Accessory, @Bonus, @MotorTax, @HouseCharge, @Finance, @Other, @Previous, @totalCost, @purchaseAmt, @purchaseVAT, @retail, @trade, @VRT, @VIBE, @fuel, @cc, @body, @colour, @trim, @owners, @year, @regDate, @class, @transm, @newUsed, @daysInStock, @lastMOT, @nextMOT)
"@

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($insertQuery, $sqlConnection)

# 添加参数（需与SQL表字段类型对应）
$sqlCommand.Parameters.Add("@branch", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@stockNo", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@reg", [System.Data.SqlDbType]::VarChar, 20) | Out-Null
$sqlCommand.Parameters.Add("@category", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@make", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@model", [System.Data.SqlDbType]::VarChar, 100) | Out-Null
$sqlCommand.Parameters.Add("@variant", [System.Data.SqlDbType]::VarChar, 100) | Out-Null
$sqlCommand.Parameters.Add("@buyInSP", [System.Data.SqlDbType]::Decimal) | Out-Null
# 日期类型参数
$sqlCommand.Parameters.Add("@purchaseInvDate", [System.Data.SqlDbType]::DateTime) | Out-Null
$sqlCommand.Parameters.Add("@dateIn", [System.Data.SqlDbType]::DateTime) | Out-Null
$sqlCommand.Parameters.Add("@regDate", [System.Data.SqlDbType]::DateTime) | Out-Null
$sqlCommand.Parameters.Add("@lastMOT", [System.Data.SqlDbType]::DateTime) | Out-Null
$sqlCommand.Parameters.Add("@nextMOT", [System.Data.SqlDbType]::DateTime) | Out-Null
# 其他参数
$sqlCommand.Parameters.Add("@purchaseInvRef", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@odometer", [System.Data.SqlDbType]::Int) | Out-Null
$sqlCommand.Parameters.Add("@clock", [System.Data.SqlDbType]::Int) | Out-Null
$sqlCommand.Parameters.Add("@qual", [System.Data.SqlDbType]::VarChar, 20) | Out-Null
$sqlCommand.Parameters.Add("@BaseDelivery", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@Overallowance", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@WriteDown", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@Sublet", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@PDI", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@Accessory", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@Bonus", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@MotorTax", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@HouseCharge", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@Finance", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@Other", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@Previous", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@totalCost", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@purchaseAmt", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@purchaseVAT", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@retail", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@trade", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@VRT", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@VIBE", [System.Data.SqlDbType]::Decimal) | Out-Null
$sqlCommand.Parameters.Add("@fuel", [System.Data.SqlDbType]::VarChar, 20) | Out-Null
$sqlCommand.Parameters.Add("@cc", [System.Data.SqlDbType]::Int) | Out-Null
$sqlCommand.Parameters.Add("@body", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@colour", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@trim", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@owners", [System.Data.SqlDbType]::Int) | Out-Null
$sqlCommand.Parameters.Add("@year", [System.Data.SqlDbType]::Int) | Out-Null
$sqlCommand.Parameters.Add("@class", [System.Data.SqlDbType]::VarChar, 50) | Out-Null
$sqlCommand.Parameters.Add("@transm", [System.Data.SqlDbType]::VarChar, 20) | Out-Null
$sqlCommand.Parameters.Add("@newUsed", [System.Data.SqlDbType]::VarChar, 10) | Out-Null
$sqlCommand.Parameters.Add("@daysInStock", [System.Data.SqlDbType]::Int) | Out-Null

# 读取CSV并循环插入
$csv = Import-Csv $csvPath
foreach ($row in $csv) {
    # 赋值普通参数
    $sqlCommand.Parameters["@branch"].Value = $row.branch
    $sqlCommand.Parameters["@stockNo"].Value = $row.stockNo
    $sqlCommand.Parameters["@reg"].Value = $row.reg
    $sqlCommand.Parameters["@category"].Value = $row.category
    $sqlCommand.Parameters["@make"].Value = $row.make
    $sqlCommand.Parameters["@model"].Value = $row.model
    $sqlCommand.Parameters["@variant"].Value = $row.variant
    $sqlCommand.Parameters["@buyInSP"].Value = $row.buyInSP
    
    # 处理日期参数，空值设为DBNull
    if (-not [string]::IsNullOrEmpty($row.purchaseInvDate)) {
        $sqlCommand.Parameters["@purchaseInvDate"].Value = [DateTime]::Parse($row.purchaseInvDate)
    } else {
        $sqlCommand.Parameters["@purchaseInvDate"].Value = [DBNull]::Value
    }
    if (-not [string]::IsNullOrEmpty($row.dateIn)) {
        $sqlCommand.Parameters["@dateIn"].Value = [DateTime]::Parse($row.dateIn)
    } else {
        $sqlCommand.Parameters["@dateIn"].Value = [DBNull]::Value
    }
    if (-not [string]::IsNullOrEmpty($row.regDate)) {
        $sqlCommand.Parameters["@regDate"].Value = [DateTime]::Parse($row.regDate)
    } else {
        $sqlCommand.Parameters["@regDate"].Value = [DBNull]::Value
    }
    if (-not [string]::IsNullOrEmpty($row.lastMOT)) {
        $sqlCommand.Parameters["@lastMOT"].Value = [DateTime]::Parse($row.lastMOT)
    } else {
        $sqlCommand.Parameters["@lastMOT"].Value = [DBNull]::Value
    }
    if (-not [string]::IsNullOrEmpty($row.nextMOT)) {
        $sqlCommand.Parameters["@nextMOT"].Value = [DateTime]::Parse($row.nextMOT)
    } else {
        $sqlCommand.Parameters["@nextMOT"].Value = [DBNull]::Value
    }

    # 赋值剩余参数
    $sqlCommand.Parameters["@purchaseInvRef"].Value = $row.purchaseInvRef
    $sqlCommand.Parameters["@odometer"].Value = $row.odometer
    $sqlCommand.Parameters["@clock"].Value = $row.clock
    $sqlCommand.Parameters["@qual"].Value = $row.qual
    $sqlCommand.Parameters["@BaseDelivery"].Value = $row.'Base & Delivery'
    $sqlCommand.Parameters["@Overallowance"].Value = $row.Overallowance
    $sqlCommand.Parameters["@WriteDown"].Value = $row.WriteDown
    $sqlCommand.Parameters["@Sublet"].Value = $row.Sublet
    $sqlCommand.Parameters["@PDI"].Value = $row.PDI
    $sqlCommand.Parameters["@Accessory"].Value = $row.Accessory
    $sqlCommand.Parameters["@Bonus"].Value = $row.Bonus
    $sqlCommand.Parameters["@MotorTax"].Value = $row.'Motor Tax'
    $sqlCommand.Parameters["@HouseCharge"].Value = $row.'House Charge'
    $sqlCommand.Parameters["@Finance"].Value = $row.Finance
    $sqlCommand.Parameters["@Other"].Value = $row.Other
    $sqlCommand.Parameters["@Previous"].Value = $row.Previous
    $sqlCommand.Parameters["@totalCost"].Value = $row.totalCost
    $sqlCommand.Parameters["@purchaseAmt"].Value = $row.purchaseAmt
    $sqlCommand.Parameters["@purchaseVAT"].Value = $row.purchaseVAT
    $sqlCommand.Parameters["@retail"].Value = $row.retail
    $sqlCommand.Parameters["@trade"].Value = $row.trade
    $sqlCommand.Parameters["@VRT"].Value = $row.VRT
    $sqlCommand.Parameters["@VIBE"].Value = $row.VIBE
    $sqlCommand.Parameters["@fuel"].Value = $row.fuel
    $sqlCommand.Parameters["@cc"].Value = $row.cc
    $sqlCommand.Parameters["@body"].Value = $row.body
    $sqlCommand.Parameters["@colour"].Value = $row.colour
    $sqlCommand.Parameters["@trim"].Value = $row.trim
    $sqlCommand.Parameters["@owners"].Value = $row.owners
    $sqlCommand.Parameters["@year"].Value = $row.year
    $sqlCommand.Parameters["@class"].Value = $row.class
    $sqlCommand.Parameters["@transm"].Value = $row.transm
    $sqlCommand.Parameters["@newUsed"].Value = $row.'new/used'
    $sqlCommand.Parameters["@daysInStock"].Value = $row.daysInStock

    $sqlCommand.ExecuteNonQuery()
}

$sqlConnection.Close()

2. 直接转换日期格式适配SQL Server（不推荐，仍有SQL注入风险） #

如果坚持使用字符串拼接，需将CSV中的日期转换为SQL Server可识别的格式（如yyyy-MM-dd），同时处理空值：

# 修改循环内的日期处理逻辑
foreach ($row in $csv) {
    # 转换日期格式，空值替换为SQL的NULL
    $purchaseInvDate = if ([string]::IsNullOrEmpty($row.purchaseInvDate)) { "NULL" } else { "'$([DateTime]::Parse($row.purchaseInvDate).ToString('yyyy-MM-dd'))'" }
    $dateIn = if ([string]::IsNullOrEmpty($row.dateIn)) { "NULL" } else { "'$([DateTime]::Parse($row.dateIn).ToString('yyyy-MM-dd'))'" }
    $regDate = if ([string]::IsNullOrEmpty($row.regDate)) { "NULL" } else { "'$([DateTime]::Parse($row.regDate).ToString('yyyy-MM-dd'))'" }
    $lastMOT = if ([string]::IsNullOrEmpty($row.lastMOT)) { "NULL" } else { "'$([DateTime]::Parse($row.lastMOT).ToString('yyyy-MM-dd'))'" }
    $nextMOT = if ([string]::IsNullOrEmpty($row.nextMOT)) { "NULL" } else { "'$([DateTime]::Parse($row.nextMOT).ToString('yyyy-MM-dd'))'" }

    # 拼接SQL时替换原日期字段为转换后的值
    $sqlCommand.CommandText = "INSERT INTO $tableName (branch, stockNo, reg, category, make, model, variant, buyInSP, purchaseInvDate, dateIn, purchaseInvRef, odometer, clock, qual, [Base & Delivery], Overallowance, WriteDown, Sublet