Posted by Erland Sommarskog on 10/11/07 21:37

(bob.speaking@gmail.com) writes:
> I'm sorry, cut and pasting sample text file I've removed the correct
> syntax :\
> In fact the last line has the terminator you specified. :)

OK, here is the next guess: your data file has a byte-order mark. Byte-order
marks is no good when you use a format file, because with a format
file, the data file is treated as a stream of bytes (as testified of
the funky way to specify the delimiters in a Unicode file.)

Since you have consistent delimiters, you may not need a format file,
but you could use

WITH (FIELDTERMINATOR = '|', DATAFILETYPE = 'widechar')

I created a data file according to your description, and indeed I got
"string or binary data would be truncated" at first when I used your
format file, because of the BOM. When I used FIELDTERMINATOR and
DATAFILETYPE, I was able to load my test file.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]