SQL Server导入多列CSV无报错但失败，单列可正常导入求解

Let’s break down this tricky issue—since single-column imports work fine, we know the row terminator and basic file accessibility are solid. The problem almost always boils down to how SQL Server parses multi-column formatting that it can’t handle silently. Here’s how to diagnose and fix it:

1. Verify Column Delimiter & Quoting Consistency #

Single-column imports don’t care about delimiters, but multi-column ones rely on them entirely. Start here:

• Open your CSV in a plain text editor (Notepad++ is perfect for this) and confirm what character is actually separating columns (comma? semicolon? tab?). Make sure this matches the FIELDTERMINATOR setting in your import (either in the SSIS Wizard or BULK INSERT command).
• Look for fields that contain your delimiter (e.g., a name like Smith, Jane)—if these aren’t wrapped in double quotes ("Smith, Jane"), SQL Server will split them into extra columns. Even if your table has enough columns, this throws off row parsing and leads to silent skips. Enable FIELDQUOTE = '"' in BULK INSERT or check the "Text qualifier" option in the import wizard to fix this.

2. Check for Hidden Encoding Mismatches #

Encoding issues can turn delimiters into unrecognizable gibberish to SQL Server, even if the file looks fine to you:

• In Notepad++, check the encoding shown in the bottom-right corner (e.g., UTF-8, UTF-8 BOM, ANSI).
• Match this encoding in your import: in the SSIS Wizard, pick the corresponding "Code page"; with BULK INSERT, add the CODEPAGE parameter (e.g., CODEPAGE = '65001' for UTF-8). A mismatch here makes SQL Server see every row as a single invalid column, so it skips all data.

3. Use Error Logs to Pinpoint Bad Rows #

Since there are no explicit errors, enable error logging to see exactly what’s failing:

• If using BULK INSERT, add the ERRORFILE parameter to capture problematic rows and their root causes:

  BULK INSERT YourTargetTable
  FROM 'C:\path\to\your.csv'
  WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\r\n',
      FIELDQUOTE = '"',
      ERRORFILE = 'C:\path\to\error_log.txt',
      FIRSTROW = 2 -- Skip header if your CSV has one
  );
  

• For SSIS, check the "Progress" tab during execution—look for warnings about skipped rows or data truncation. These often hint at formatting issues that aren’t severe enough to trigger a full error.

4. Test with a Minimal Valid Sample #

Create a tiny 2-3 row multi-column CSV with perfect formatting (correct delimiters, quoted fields where needed) and try importing it. If this works, your original file has specific rows with broken formatting. To find them:

• Use your text editor’s search function to look for unclosed quotes (a missing " at the end of a field will throw off every subsequent row).
• Check for odd line breaks—some tools save files with \n instead of \r\n, or have accidental line breaks inside unquoted fields.

5. Double-Check Column Mapping & Table Structure #

Even a small mapping mistake can cause silent failures:

• Ensure the order of columns in your CSV matches the order in your SQL table (unless you’re explicitly mapping columns in the import wizard).
• Check if any columns in your table have strict constraints (e.g., NOT NULL with no default) that the CSV data violates. If a row has a NULL value for a NOT NULL column, some import tools will skip the row instead of throwing an error.

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