How use Bulk insert csv to sql server with datetime format correct?

Question

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.

Example Create Table

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
)

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

when i run query it's error:

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)".

Example Data in CSV File

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

Result when run query:

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)

but I want result datetime format (YYYY-MM-DD) correct:

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

First, SQL Server's import and ETL tool is SSIS, not bcp or BULK INSERT. Those are meant for fast bulk operations. Date parsing or any kind of parsing isn't fast. In this case you may be able to use a format file as shown in Use a Format File to Bulk Import Data and specify a collation for the date fields that matches the date format. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 20, 2017 at 14:43
If that doesn't work, you'll have to import the file into a staging table and parse the date fields. You wouldn't have to do that though if you used SSIS — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 20, 2017 at 14:45

Siyual · Accepted Answer · 2017-07-20 15:46:32Z

2

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)

edited Jul 20, 2017 at 15:46

answered Jul 20, 2017 at 14:28

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bacon Bits Over a year ago

As far as I know, DATEFORMAT doesn't work with BULK INSERT.

10 Rep · Accepted Answer · 2021-04-20 16:17:47Z

0

It works with Bulk Insert:

SET DATEFORMAT DMY
BULK INSERT [dbo].[TRX]
        FROM 'C:\..\trx2.txt'
            WITH
    (           
                FIELDTERMINATOR = '\t',
                ROWTERMINATOR = '\n',
                FIRSTROW = 2
    )
PRINT 'BULK INSERT [TRX2]'
GO

edited Apr 20, 2021 at 16:17

10 Rep

2,2687 gold badges21 silver badges34 bronze badges

answered Apr 19, 2021 at 23:56

Sebastian Postiglione

1

Collectives™ on Stack Overflow

How use Bulk insert csv to sql server with datetime format correct?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related