2

I have failed to load a text file's data to my db's table with the persistent error

invalid time format.

I have changed the time format to include a T between the date and time, to no avail. I also substituted the year, month, date delimiter from . to - but the error persisted.

This is how I am attempting to load from a txt file

bcp mydb.dbo.mytable in c:\data.txt -T -S myserver\instance

I then proceed to confirm data types of the fields, prefix-length (this is the length of the delimiter before the field... right?) and the field terminator. I create a format file which when used still yields the same error. The datetime field I am importing is one of four other fields in a typical row.

What am I missing?

EDIT

Here is a typical row

14,1999-01-04T08:08:24.000,1.36000,1.36000

I have also failed when using SSIS my prior work around of first using access is not applicable here. It is either bcp or SSIS

ssn
  • 439
  • 5
  • 14
  • Include a little sample of data please. – TT. Jan 09 '16 at 21:01
  • What you are missing? Don't know... But I can tell you what I am missing: Sample data, some lines of your "typical rows". And one hint: Try your statement with a file which holds just the first few lines. it could be, that one single date somewhere down there is bad and not the format itself. And another hint: Most problems occur with differing month-day-order. 2016-04-01 will work, but might produce unexpected results, 2016-13-04 won't work... – Shnugo Jan 09 '16 at 21:11
  • Got this to work via SSIS... guess bcp is an antique ;-) – ssn Jan 10 '16 at 05:32

1 Answers1

0

I believe you are using SQL Server 2008 (R2)? If so, please add appropriate tag to your question.

This problem has been reported here at dba.stackexchange.

Following hints were given:

  • You need to use a generated format file and use -f for both import and export.
  • Ensure your datetimes are formatted like yyyy-mm-dd hh:mm:ss, so with a space instead of a T in between date and time

Note that I read here the claim that this is a bug in BCP that ships with SQL Server 2008 (R2). Apparantly this was solved in SQL Server 2012+.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • Hi, was using SQL 2014... Had to resort to first importing txt file into access then exporting the accdb file to SQL – ssn Jan 15 '16 at 11:34