Posted by rshivaraman on 08/02/07 14:58

BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)

---------------------------------
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n

So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.

2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.

So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?

thanks
RS

[Back to original message]