I'm having a lot of issues importing data to SQL Azure using the bcp utility. They issues seem to mainly center around dates, so I have distilled this issue down to the following:

data (cp1252 encoding) -

2015-09-01 00:00:00
2015-09-01 00:00:00
2015-09-01 00:00:00
2015-09-01 00:00:00

fmt - 

11.0
1
1       SQLDATETIME         0       8       ","    1     Date         ""

schema -

CREATE TABLE test_table ( Date DATETIME NOT NULL );

bcp command line -

bcp test_table in stest.csv -S tcp:<our_server> -d <our_db> -U <our_user> -P <super_secret_password> -k -r "\r\n" -f test.fmt -e test_errors.txt

error output:

#@ Row 1, Column 1: Invalid time format @#
20420-02-27 702:31:15.350

As you can see, the data reported by the error bears no relation to the actual data we're passing in. 

I've tried varying date formats with different results, none of them good or useful in any way, all of them reporting data in the errors file that looks nothing like the data we're trying to import. I've tried putting the csv file in utf-8 and, cp1252, but nothing changes. It does appear to be reading the proper file though, as changing the date to something different will result in a different seemingly made up error:

data -

2009-10-11 00:00:00

error -

#@ Row 1, Column 1: Invalid time format @#
7370-07-22 701:58:30.123

I've also tried various different command line switches, -R produced:

Starting copy...
SQLState = 37000, NativeError = 102
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.

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

...despite the fact that there is no , in the test.csv, or the command line. 

The issue is that you are using a format file that is used for native data files (with -n option of bcp). Best way to generate format files for bcp is used to the format option (-f) with the schema / object in question. Ex:

bcp db..table format datetime.dat -S srvr -U usr -P pwd -c -f datetime.fmt

The format file which will work with your data file is below (can be generated using above command):

13.0
1
1       SQLCHAR             0       24      "\r\n"   1     Date         ""

• Proposed as answer by Joseph IdziorekMicrosoft employee Saturday, September 05, 2015 12:09 AM

The issue is that you are using a format file that is used for native data files (with -n option of bcp). Best way to generate format files for bcp is used to the format option (-f) with the schema / object in question. Ex:

bcp db..table format datetime.dat -S srvr -U usr -P pwd -c -f datetime.fmt

The format file which will work with your data file is below (can be generated using above command):

13.0
1
1       SQLCHAR             0       24      "\r\n"   1     Date         ""

• Proposed as answer by Joseph IdziorekMicrosoft employee Saturday, September 05, 2015 12:09 AM

The issue is that you are using a format file that is used for native data files (with -n option of bcp). Best way to generate format files for bcp is used to the format option (-f) with the schema / object in question. Ex:

bcp db..table format datetime.dat -S srvr -U usr -P pwd -c -f datetime.fmt

The format file which will work with your data file is below (can be generated using above command):

13.0
1
1       SQLCHAR             0       24      "\r\n"   1     Date         ""

• Proposed as answer by Joseph IdziorekMicrosoft employee Saturday, September 05, 2015 12:09 AM
• Marked as answer by Casey KarstMicrosoft employee, Moderator 13 hours 46 minutes ago

The issue is that you are using a format file that is used for native data files (with -n option of bcp). Best way to generate format files for bcp is used to the format option (-f) with the schema / object in question. Ex:

bcp db..table format datetime.dat -S srvr -U usr -P pwd -c -f datetime.fmt

The format file which will work with your data file is below (can be generated using above command):

13.0
1
1       SQLCHAR             0       24      "\r\n"   1     Date         ""

• Proposed as answer by Joseph IdziorekMicrosoft employee Saturday, September 05, 2015 12:09 AM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Tuesday, September 08, 2015 5:20 PM

The issue is that you are using a format file that is used for native data files (with -n option of bcp). Best way to generate format files for bcp is used to the format option (-f) with the schema / object in question. Ex:

bcp db..table format datetime.dat -S srvr -U usr -P pwd -c -f datetime.fmt

The format file which will work with your data file is below (can be generated using above command):

13.0
1
1       SQLCHAR             0       24      "\r\n"   1     Date         ""

• Proposed as answer by Joseph IdziorekMicrosoft employee Saturday, September 05, 2015 12:09 AM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Tuesday, September 08, 2015 5:20 PM

Thanks Umachandar,

We did finally find that solution on our own. We were not looking to pull the data out of our system with BCP, which is I guess where the issue began - most examples I could find using BCP assumed you were pulling data out of one table and inserting it into an identical table somewhere else. We were working with data that was already extracted from a system and looking to bulk insert it to SQL Azure. The format file we used above was what BCP produced when we first tried running the insert without the -f option - it appears that was generated based on the target table though, with no consideration of the input file.