hex value in field/row terminator for bulk insert

Question

I'm running SQL Server 2005 Express. And I'm trying to do a bulk insert/import of a data file with a field/row terminator that uses a hexadecimal value 0x001. How should I represent it in a bulk insert command?

I have something like:

bulk insert xxx.dbo.[yyy]
from 'D:\zzz\zzz.dat'
with (
         CODEPAGE='RAW',
         FIELDTERMINATOR = '=|=',
         ROWTERMINATOR = '=|=\001\n',
         KEEPNULLS
); 

which results in

Msg 4863, Level 16, State 1, Line 7
Bulk load data conversion error (truncation) for row 1, column 3 (code).

Column 3 is the last column. And removing the hex value from the string lets it load properly in SQL Server, however, I want to know if it's possible to represent/use a hex value in a terminator.

Answer 1

ROWTERMINATOR = '=|=01\n'

This assumes the file looks like this with an actual string "01"

foo=/=bar=/=bob=/=01

Otherwise, if it's literal character (1), you can't: Specifying Field and Row Terminators

Answer 2

No it's not. It's an actual hex value 0x001. The answer is to use a hex to string converter to get the literal character and use the literal funny looking character in the string to ROWTERMINATOR.