BULK Insert problem with DateTime field

I am trying to perform a bulk insert in SQL Query analyzer. This is the Schema for my table:

Taxonomic_Units Table:

INT 4 tsn
CHAR 1 unit_ind1
CHAR 35 unit_name1
CHAR 1 unit_ind2
CHAR 34 unit_name2
CHAR 7 unit_ind3
CHAR 35 unit_name3
CHAR 7 unit_ind4
CHAR 35 unit_name4
CHAR 1 unnamed_taxon_ind
CHAR 12 usage
CHAR 50 unaccept_reason
CHAR 40 credibility_rtng
CHAR 10 completeness_rtng
CHAR 7 currency_rating
SMALLINT 2 phylo_sort_seq
DATETIME 8 initial_time_stamp
INT 4 parent_tsn
INT 4 taxon_author_id
INT 4 hybrid_author_id
SMALLINT 2 kingdom_id
SMALLINT 2 rank_id
DATETIME 4 update_date
CHAR 3 uncertain_prnt_ind

I use the following SQL Statement to BULK INSERT:

BULK INSERT itis.taxonomic_units
FROM '<dir path to input file>/taxonomic_units.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n',
KEEPIDENTITY,
KEEPNULLS

)

Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement:

50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996||

The error is:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).

Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.

llzamboni wrote:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).

Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.

For me, that works just fine...

SELECT CAST('1996-06-13 14:51:08.0' AS DATETIME)

Are you *sure* that row is the problem?

|||

It seems like BULK INSERT is having difficulties with the (sort of) malformed date 1996-06-13 14:51:08.0

If you remove the last .0 or add two zeroes so it becomes .000 then BULK INSERT will insert the row.

However, if you instead use BCP, no changes are needed.
Apparently, BCP isn't as cranky as BULK INSERT in this case.

/Kenneth

Bulk Insert (type mismatch) on datetime field containing NULL

Can anyone help please, I am using bulk insert for the first time.
The statement I am running is:
BULK INSERT Titles
FROM 'c:\Titles.txt'
WITH (FIRSTROW = 3,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS,
FORMATFILE = 'c:\Titles.fmt')
Titles.txt contains tab delimited data like:
ID Description StartDate ExpiryDate ParentItemID
-- --
-- -- --
440 Doctor 1 Jan 1997 0:00 NULL NULL
441 Mr 1 Jan 1990 0:00 NULL 1
If I run the bulk insert statement I get the message:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 4
(ExpiryDate)
In the file Titles.txt, if I find and replace NULL with nothing and
then execute the statement the data inserts into the Titles table.
I need to be able to insert without having to do find and replace as I
have hundreds of files to bulk insert.
The format file Titles.fmt looks like this:
8.0
5
1 SQLCHAR 0 12 "\t" 1
ID ""
2 SQLCHAR 0 100 "\t" 2
Description Latin1_General_CI_AS
3 SQLCHAR 0 24 "\t" 3
StartDate ""
4 SQLCHAR 0 24 "\t" 4
ExpiryDate ""
5 SQLCHAR 0 12 "\t" 5
ParentItemID ""If this is not a one time deal, you'd be better off making sure that when
these files are generated, the value for column ExpireDate that is null does
not contain a string NULL.
With the existing data files, personally, I'd write a little utility to
find/replace all the 'NULL' string in the ExpireDate column with an empty
string. This can be esily done with any tool that supports regular
expressions.
Linchi
"rai_sk@.hotmail.com" wrote:
> Can anyone help please, I am using bulk insert for the first time.
> The statement I am running is:
> BULK INSERT Titles
> FROM 'c:\Titles.txt'
> WITH (FIRSTROW = 3,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> FORMATFILE = 'c:\Titles.fmt')
> Titles.txt contains tab delimited data like:
> ID Description StartDate ExpiryDate ParentItemID
> -- --
> -- -- --
> 440 Doctor 1 Jan 1997 0:00 NULL NULL
> 441 Mr 1 Jan 1990 0:00 NULL 1
> If I run the bulk insert statement I get the message:
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 4
> (ExpiryDate)
> In the file Titles.txt, if I find and replace NULL with nothing and
> then execute the statement the data inserts into the Titles table.
> I need to be able to insert without having to do find and replace as I
> have hundreds of files to bulk insert.
> The format file Titles.fmt looks like this:
> 8.0
> 5
> 1 SQLCHAR 0 12 "\t" 1
> ID ""
> 2 SQLCHAR 0 100 "\t" 2
> Description Latin1_General_CI_AS
> 3 SQLCHAR 0 24 "\t" 3
> StartDate ""
> 4 SQLCHAR 0 24 "\t" 4
> ExpiryDate ""
> 5 SQLCHAR 0 12 "\t" 5
> ParentItemID ""
>