Bulk load data conversion error

[Brian Conner via SQLMonster.com]

I have a Tab delimited .txt file I am trying to load into sql server table.
Below is the code I am using.

BULK
INSERT Lab_To_Billing_Recon.dbo.TEST
FROM '\\addfile00\groups\billing\BAM!\LIS_BIS_RECON\BULKINSERT.txt'
with (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR='\n', KEEPNULLS)

I get the following 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 2, column 6 (Signout Date).

Any ideas what I may be leaving out or doing wrong?
Any help is appreciated..

Brian

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1

[TheSQLGuru]

Given that column 6 is labeled SignoutDate, the most likely cause is a value
that cannot be converted to a date datatype in SQL Server. Review the data.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message
news:a151e781bb896@uwe...

[Erland Sommarskog]

Brian Conner via SQLMonster.com (u47161@uwe) writes:
> I have a Tab delimited .txt file I am trying to load into sql server
> table. Below is the code I am using.
>
>
> BULK
> INSERT Lab_To_Billing_Recon.dbo.TEST
> FROM '\\addfile00\groups\billing\BAM!\LIS_BIS_RECON\BULKINSERT.txt'
> with (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR='\n',
> KEEPNULLS)
>
>
> I get the following 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 2, column 6 (Signout Date).
>
> Any ideas what I may be leaving out or doing wrong?

Given that I don't see the data file, nor the table you are trying to
insert to, I can't really say more than what Kevin said: reivew the
data.

However, if I am to take to guessing, I notice that you have specified
FIRSTROW = 2. This indicates that there is a header in the file, that
you are trying to escape.

How, "rows" for BULK INSERT are not rows in a file. It is really records
that it has identified according to your description. That is, you
have specified a table which has, say, 6 columns. That gives six
fields. Of these five end with tab and the six with newline. And BULK
INSERT reads the data in that order. If it sees a newline while it's
reading field3, the newline is simply part of that field. It is not a
premature end of record.

As you can imagine this can lead to a mess.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx