执行BULK INSERT时遇数据转换截断错误，请求原因分析

Let’s break down why you’re hitting this truncation error—even though "470" is way shorter than your varchar(10) column’s limit, there are a few tricky culprits to check:

1. You’re trying to bulk insert directly from an XLSX file (critical issue!) #

First off, SQL Server’s BULK INSERT doesn’t support importing directly from XLSX files—XLSX is a binary format, not plain text. When you run that command, SQL is treating the Excel file’s binary data as raw text, which means the "values" it’s trying to parse for column 2 are likely garbage (not the actual "470" you see in Excel). This garbage data is almost certainly longer than 10 characters, triggering the truncation error.

Fix this by first saving your Excel file as a Tab-Separated Values (TSV) or Comma-Separated Values (CSV) file (use Excel’s "Save As" option and pick a text-based format). Then update your BULK INSERT command to target this text file instead of the XLSX.

2. Hidden whitespace or non-printable characters in the Excel cell #

Even after switching to a text file, the "470" in your Excel cell might have invisible characters attached:

• Trailing spaces, tabs, or line breaks (you won’t see them in Excel, but they exist)
• Non-printable characters like zero-width spaces or carriage returns

To verify this:

• In Excel, use =LEN(YourCell) (replace YourCell with the actual cell reference) to check the true character count. If it’s more than 3, extra characters are present.
• Copy the cell’s content into a plain text editor like Notepad—any hidden characters will become visible here.

3. Mismatched FIRSTROW parameter #

You set FIRSTROW = 6, but double-check that Excel’s row 6 is truly the start of your data. If there are hidden rows, merged cells, or unvisible header rows, SQL might be reading a row with longer content (like a header label) as the first data row, causing the truncation on column 2.

4. Incorrect field terminator handling #

You’re using FIELDTERMINATOR = ' ' (a tab), but if your saved text file uses a different delimiter (like a comma, or multiple tabs), SQL might be combining multiple columns into one. This would make the value for "Oracle Company Code" far longer than expected, leading to the truncation error. Open your text file in Notepad to confirm the delimiter matches what you’ve specified.

Once you address these issues, your bulk insert should work as expected!

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