如何以无法识别的日期时间格式导入CSV? [英] How to import CSV with unrecognized datetime format?

问题描述

这是表格的样子:

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"

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

This is how my format file looks like:

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'
    )

尝试执行脚本时出现此错误:

I'm getting this error when I try to execute the script:

第4级第1线第2行4864消息

Msg 4864, Level 16, State 1, Line 2

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

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (datetime).

我认为datetime将不接受像12/11/2016 10:06 AM这样的日期格式，但是此CSV文件是由第三方软件创建的，该怎么办?

I think datetime won't accept the format of date like this 12/11/2016 10:06 AM, but this CSV file is created by a third party software, so what can I do?

推荐答案

我希望BULK INSERT处理文件日期时间格式.问题在于格式文件规范.您需要:

I would expect BULK INSERT to handle the file datetime format. The issue is with the format file specification. You need to:

• 为第一个报价行框指定一个虚拟字段 字段
• 为所有文件字段指定SQLCHAR(描述文件，而不是 表格)
• 在字段终止符中指定报价字段附件

• specify a dummy field for the quote field enclosure of the first field
• specify SQLCHAR for all file fields (describes the file, not the table)
• specify the quote field enclosures in the field terminator

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

If the rows are terminated with both carriage return and newline characters, specify "\"\r\n" instead of "\"\n" as the last field terminator.

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

The format file for the specified data should be:

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语句中省略这些关键字:

Because the format file specifies the field and row terminators, you can omit those keywords from the BULK INSERT statement:

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

这篇关于如何以无法识别的日期时间格式导入CSV?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！