By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,440 Members | 1,872 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,440 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Aug 2 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.