我是新的批量插入脚本。我正在尝试加载下面的csv文件。所有字段都用双引号括起来，如果日期字段有任何日期值，则它以=符号开头，否则它将是一个空字符串。下面是csv文件数据示例。
csv文件数据：

"Name","Market","Year","Family","Date"
"Test1                          ","C","2021","DS",="2020-12-31 00:00:00.000000"
"Test1                          ","C","2020","DS",="2020-12-31 00:00:00.000000"
"Test1                          ","C","2021","DS",""
"Test1                          ","C","2020","DS",""
"Test1                          ","C","2020","DS",="2020-12-31 00:00:00.000000"

我使用了以下代码。

BULK INSERT #temp1 FROM 'samplerec.csv'
    WITH (
        FIRSTROW = 2
        ,FORMATFILE = 'FormatFile.xml'
        )

格式化文件内容

<?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" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=",=" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Market" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Year" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Family" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Date" xsi:type="SQLNVARCHAR"/>
 </ROW>  
</BCPFORMAT>

当我运行bulkinsert语句时，我得到了下面的错误。

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 4

我确信没有值的日期列（第3行的空字符串）导致了问题。我不知道如何处理多个字段终止符批量插入格式文件。请在这件事上帮助我。我必须加载大量这种格式的csv文件。