使用PowerShell批量导入大CSV至SQL Server遇日期转换错误求助

Hey, let's break down this problem clearly—your error is telling you that some date string from your CSV can't be converted to the date type in your SQL Server table. But the real culprit here is how your script is handling CSV parsing—using Split() directly is a terrible idea for standard CSV files, because it doesn't account for quoted fields, commas inside fields, or other standard CSV nuances. This is almost certainly causing some of your date values to get split incorrectly, leading to the conversion failure.

1. Fix Your CSV Parsing (The Critical Fix) #

Right now, you're splitting each line with $line.Split($csvdelimiter), which ignores all standard CSV rules. For example, if a date field is "2024-05-20, 14:30" (with a comma inside quotes), splitting on ', would tear that field into two broken pieces, resulting in a date string that can't be converted.

Instead, use .NET's TextFieldParser (built into PowerShell via the VisualBasic assembly) which properly handles standard CSV formatting:

First, add the assembly load at the top of your script:

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.VisualBasic")

Then replace your entire CSV reading and DataTable population section with this:

# Initialize proper CSV parser
$csvParser = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser($csvfile)
$csvParser.TextFieldType = [Microsoft.VisualBasic.FileIO.FieldType]::Delimited
# Fix delimiter: if your CSV uses standard commas, set this to "," instead of "',"
$csvParser.SetDelimiters($csvdelimiter.Trim("'")) 
$csvParser.HasFieldsEnclosedInQuotes = $true # Critical for handling fields with commas/quotes

# Get column names from header
if ($firstRowColumnNames) {
    $columns = $csvParser.ReadFields()
} else {
    # Auto-generate columns if no header exists
    $firstRow = $csvParser.ReadFields()
    $columns = 1..$firstRow.Count | ForEach-Object { "Column$_" }
    $csvParser.BaseStream.Position = 0 # Reset to start of file
}

# Create DataTable columns matching CSV headers
foreach ($col in $columns) {
    $datatable.Columns.Add($col) | Out-Null
}

$i = 0
while (!$csvParser.EndOfData) {
    try {
        $rowFields = $csvParser.ReadFields()
        
        # Optional: Add date conversion logic here (see step 2)
        
        $datatable.Rows.Add($rowFields) | Out-Null
        $i++

        # Batch insert when we hit our batch size
        if ($i % $batchsize -eq 0) {
            $bulkcopy.WriteToServer($datatable)
            Write-Host "$i rows inserted in $($elapsed.Elapsed.ToString())"
            $datatable.Clear()
        }
    } catch {
        Write-Warning "Failed processing line $($i+1): $_"
        # Log bad rows for debugging later
        $rowFields -join "," | Out-File "C:\temp\failed_rows.log" -Append
    }
}

2. Fix Date Type Mismatches #

The error specifically calls out date conversion, so you need to ensure your CSV date values align with what SQL Server expects:

• Check SQL column type: Is your target column date, datetime, or datetime2? If it's date, make sure CSV dates don't have time components (or strip them).
• Check CSV date format: Is it MM/dd/yyyy, dd/MM/yyyy, or something else? Use ParseExact to convert it correctly before adding to the DataTable.

Add this inside the try block right after reading $rowFields:

# Example: Convert "dd/MM/yyyy" formatted strings to valid DateTime objects
$dateColName = "YourDateColumnNameHere" # Replace with your actual date column name
$dateColIndex = $columns.IndexOf($dateColName)

if ($dateColIndex -ge 0 -and ![string]::IsNullOrEmpty($rowFields[$dateColIndex])) {
    try {
        $rowFields[$dateColIndex] = [DateTime]::ParseExact(
            $rowFields[$dateColIndex],
            "dd/MM/yyyy",
            [System.Globalization.CultureInfo]::InvariantCulture
        )
    } catch {
        Write-Warning "Invalid date on line $($i+1): $($rowFields[$dateColIndex])"
        $rowFields[$dateColIndex] = $null # Set to NULL since your column allows empty values
    }
}

3. Verify Data Consistency #

Even though you checked column names and nullability, double-check these edge cases:

• Are any date fields using non-standard empty values (like "N/A" instead of an empty string)? Convert those to $null so SQL accepts them as NULL.
• Do all rows have the same number of columns as the header? The TextFieldParser will catch mismatches, and the warning log will help you locate bad rows.

Final Notes #

• Your original $csvdelimiter = "'," looks suspicious—most standard CSVs use just , as the delimiter. Double-check your CSV file to confirm what's actually separating fields.
• Using SqlBulkCopy with TableLock is a great call for performance, which is crucial for importing 5 million rows.

After making these changes, your script should properly parse the CSV without breaking fields, and handle date conversions correctly.

内容的提问来源于stack exchange，提问作者J.R.