如何导入无法识别的日期时间格式的 CSV？

该表如下所示：

CREATE TABLE [dbo].[temptable] 
    (
        [id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [datetime] [datetime] NOT NULL, 
        [status] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
        [col4] [money] NULL,
        [col5] [float] NULL,
        [col6] [money] NULL,
        [col7] [float] NULL,
        [col8] [money] NULL,
        [total] [money] NOT NULL
    ) 

CSV 如下所示：

"ID","Date","status","Total"
"1611120001","12/11/2016 10:06 AM","closed","8.15"
"1611120002","12/11/2016 10:14 AM","closed","21.25"
"1611120003","12/11/2016 10:24 AM","closed","10.75"
"1611120004","12/11/2016 10:39 AM","closed","10.90"
"1611120005","12/11/2016 10:46 AM","closed","30.10"
"1611120006","12/11/2016 11:04 AM","closed","7.40"

这是我的格式文件的样子：

10.0
4
1   SQLCHAR 0   50  "," 1   sale_id ""
2   SQLDATETIME 0   8   "," 2   sale_datetime   ""
3   SQLCHAR 0   50  "," 3   sale_status ""
4   SQLMONEY    0   8   "\n"    9   grand_total ""

SQL 脚本：

SET DATEFORMAT dmy
BULK INSERT temptable
    FROM 'C:\backup\temp.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n',   
    FORMATFILE = 'C:\backup\temp_format.txt'
    )

当我尝试执行脚本时出现此错误：

消息 4864，16 级，状态 1，第 2 行

第 2 行第 2 列（日期时间）出现批量加载数据转换错误（类型不匹配或指定代码页的字符无效）。

I think datetime不会接受这样的日期格式12/11/2016 10:06 AM，但是这个 CSV 文件是由第三方软件创建的，那么我该怎么办？

我希望BULK INSERT处理文件日期时间格式。问题在于格式文件规范。你需要：

• 为第一个的报价字段附件指定一个虚拟字段 场地
• 为所有文件字段指定 SQLCHAR（描述文件，而不是 桌子）
• 在字段终止符中指定引用字段括起来

如果行以回车符和换行符终止，请指定“\”\r\n”而不是“\”\n”作为最后一个字段终止符。

指定数据的格式文件应为：

10.0
5
1   SQLCHAR 0 0 "\""     0 field1         ""
2   SQLCHAR 0 0 "\",\""  1 sale_id        ""
3   SQLCHAR 0 0 "\",\""  2 sale_datetime  ""
4   SQLCHAR 0 0 "\",\""  3 sale_status    ""
5   SQLCHAR 0 0 "\"\n"   9 total            ""

由于格式文件指定了字段和行终止符，因此您可以从BULK INSERT陈述：

BULK INSERT temptable
FROM 'C:\backup\temp.csv'
WITH
    (
        FIRSTROW = 2,
        FORMATFILE = 'C:\backup\temp_format.txt'
    );