469,950 Members | 1,909 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

rowdelimiter not accepted in bulk insert statement , used in an sproc - please help

BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'

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 ?


Aug 2 '07 #1
0 1902

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by akej via SQLMonster.com | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
10 posts views Thread by Daniel P. | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.