Sql-server – bcp error: date invalid date format

bcpcsvsql serversql-server-2016

I'm a bcp newbie. I want to insert a large file. I'm having a hard time uploading datetime format.

Table SQL:

"Create Table dbo.my_test ( " + \
    "transaction_id BIGINT ," + \
    "transaction_no INT ," + \
    "store_no INT ," + \
    "register_no INT ," + \
    "transaction_date DATETIME," + \
    ");"

My csv file:

"771666690","346138","400","1","2015-06-18 00:00:00"

My format file:

13.0
6
1       SQLBIGINT           0       8       "\""      0     test_                                                       ""
2       SQLBIGINT           0       8       "\",\""   1     transaction_id                                                       ""
3       SQLINT              0       4       "\",\""   2     transaction_no                                                       ""
4       SQLINT              0       4       "\",\""   3     store_no                                                             ""
5       SQLINT              0       4       "\",""   4     register_no                                                          ""
6       SQLDATETIME         0       8       "\r\n"   5     transaction_date                                                     ""

My bcp command:

>bcp data_base.dbo.my_test in "test.csv" -f "test.fmt" -S localhost -U *** -P *****

Output

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

I am using SQL Server 2016. Default language is csv.

I slimmed down the type just to get a toy example up, here is the new scripts:

Table SQL:

"Create Table dbo.my_test ( " + \
    "transaction_date DATETIME," + \
    ");"

My csv file:

2015-06-18

My bcp command:

>bcp data_base.dbo.my_test in "test.csv" -f "test.fmt" -S localhost -U *** -P *****

Output:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

Best Answer

Your bcp format file has six columns defined, but the csv dataset and table have only five columns.

I was able to import your sample data successfully using the format file:

14.0
5
1       SQLBIGINT       0       8       ",\""     1     transaction_id      ""
2       SQLINT          0       4       "\",\""   2     transaction_no      ""
3       SQLINT          0       4       "\",\""   3     store_no            ""
4       SQLINT          0       4       "\",\""   4     register_no         ""
5       SQLDATETIME     0       8       "\"\r\n"  5     transaction_date    ""

This works regardless of whether the table columns are NULL or NOT NULL, though it is good practice to always specify this in your CREATE TABLE statements to avoid ambiguity.

That said, importing csv data prior to SQL Server 2017 can be difficult, or even impossible. From 2017 onward, you can use the RFC 4180 compliant:

BULK INSERT dbo.my_test
FROM 'C:\Temp\my_test.csv'
WITH (FORMAT = 'CSV');

See Using the Bulk-Load Tools in SQL Server by Erland Sommarskog for a comprehensive description of the topic, including the special problems posed by csv data.

Related Question