使用BULK INSERT导入CSV时最后列空值出现双FIELDTERMINATOR问题

当CSV最后一列是空值时，BULK INSERT容易出现字段终止符重复识别的问题，以下是几种可行的解决办法：

• 调整CSV文件格式：确保每一行的末尾保留字段终止符（逗号）。比如原本的行是val1,val2，改成val1,val2,，这样BULK INSERT会自动把最后一个逗号后的空内容识别为NULL。

• 使用XML格式文件指定导入规则：
  自定义一个XML格式文件，明确每列的终止符规则，强制识别空值。假设你的表有3列，格式文件示例如下：

  <?xml version="1.0"?>
  <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="100"/>
   </RECORD>
   <ROW>
    <COLUMN SOURCE="1" NAME="col1" xsi:type="SQLVARCHAR"/>
    <COLUMN SOURCE="2" NAME="col2" xsi:type="SQLVARCHAR"/>
    <COLUMN SOURCE="3" NAME="col3" xsi:type="SQLVARCHAR"/>
   </ROW>
  </BCPFORMAT>
  

  然后修改BULK INSERT语句，引入这个格式文件：

  BULK INSERT my_table FROM 'path/to/your.csv'
  WITH (
     FORMATFILE = 'path/to/your_format_file.xml',
     FIRSTROW = 2
  )
  

• 先导入再修正数据：如果没法修改CSV文件，可以先把数据导入临时表，再通过SQL语句把异常值替换为NULL。比如假设最后一列是last_column，执行以下更新：

  UPDATE my_table
  SET last_column = NULL
  WHERE last_column = '' OR last_column LIKE '%\n'
  

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