如何使用大写插入CSV到日期时间格式正确的SQL Server? [英] How use Bulk insert csv to sql server with datetime format correct?
本文介绍了如何使用大写插入CSV到日期时间格式正确的SQL Server?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将批量插入文件csv插入到 SQL Server 2012 。同一列具有日期时间,但不能使用批量插入日期时间格式,并且我不使用SSIS 。
i want use bulk insert file csv insert to SQL Server 2012. same column have datetime but use bulk insert datetime format not work and i not use SSIS.
示例创建表
CREATE TABLE [dbo].[scanindex_test](
[request_no] [varchar](13) NOT NULL,
[request_date] [datetime] NULL,
[id_card] [varchar](20) NULL,
[firstname] [varchar](100) NULL,
[surname] [varchar](100) NULL
)
查询Sql Server 2012:
declare
@path varchar(255),
@sql varchar(5000)
SET @path = 'C:\Test\TESTFILE.csv'
set @sql = 'BULK INSERT [dbo].[scanindex_test] FROM ''' + @path + '''
' + ' WITH (
CODEPAGE=''RAW'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
) '
print @sql
exec (@sql)
当我运行查询时出现错误:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 2 (request_date).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
CSV文件中的示例数据
request_no | request_date | id_card | firstname | surname
1 | 12/7/2017 | 1122 | AA | BB
2 | 12/7/2017 | 4399 | SS | EE
3 | 13/7/2017 | 5599 | QQ | KK
运行查询的结果:
request_no | request_date | id_card | firstname | surname
1 | 2017-12-07 00:00:00.000 | 1122 | AA | BB
2 | 2017-12-07 00:00:00.000 | 4399 | SS | EE
3 >> Error, because it's look datetime format: 2017-13-07 (yyyy-mm-dd)
但我希望结果日期时间格式(YYYY-MM-DD)正确:
request_no | request_date | id_card | firstname | surname
1 | 2017-07-12 00:00:00.000 | 1122 | AA | BB
2 | 2017-07-12 00:00:00.000 | 4399 | SS | EE
3 | 2017-07-13 00:00:00.000 | 5599 | QQ | KK
请帮助我。谢谢您提前;)
Please Help me. Thanks advance ;)
推荐答案
您需要将 DATEFORMAT
更改为 DMY
。将以下内容添加到脚本顶部应该可以正常工作:
You need to change the DATEFORMAT
to DMY
. Adding the following to the top of your script should work:
SET DATEFORMAT DMY;
因此,您的完整脚本应为:
So your full script should be:
SET DATEFORMAT DMY;
declare
@path varchar(255),
@sql varchar(5000)
SET @path = 'C:\Test\TESTFILE.csv'
set @sql = 'BULK INSERT [dbo].[scanindex_test] FROM ''' + @path + '''
' + ' WITH (
CODEPAGE=''RAW'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
) '
print @sql
exec (@sql)
这篇关于如何使用大写插入CSV到日期时间格式正确的SQL Server?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文