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

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
RSFrom BOL:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_0fqq.htm
"...However, it is only necessary to enter the characters \r\n as the
terminator when manually editing the terminator column of a bcp format file.
When you use bcp interactively and specify \n (newline) as the row
terminator, bcp prefixes the \r (carriage return) character automatically..."
So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
automatically - that is why it is not part of the data that gets inserted via
BULK INSERT. You should not specify the rowterminator as '\r\n'.
> So i run the above code and it inserts rows into table. No data is
> present in last column.
If you run the above code with '\n' as the row terminator and BULK INSERT is
not inserting any data into the last column, it must be for a different
reason than the rowterminator. Do you have a sample row that you can provide?
Can you take a close look at what is between the last ';' and the 0x0D0A? Try
BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
row and see if you get data.
Thanks.
-Mike
"rshivaraman@.gmail.com" wrote:
> 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
>|||Here is an example of what I meant. Based on your problem description, the
BULK INSERT should work.
Create a text File, with a crlf at the end of each row:
a;b
c;d
e;f
create table x(c1 char(1), c2 char(1))
go
BULK INSERT x FROM 'c:\dev\x.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
(3 row(s) affected)
select * from x
Results:
c1 c2
-- --
a b
c d
e f
(3 row(s) affected)
-Mike
"rshivaraman@.gmail.com" wrote:
> 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
>|||Hi Mike
Your answer solves my the question i had in mind.
2=2E What i meant to tell was, zero data is present in last column which
is a valid scenario . So i was expecting atleast one
carriage return character but the len of the column came as 0. which
is true as there is no data.
So you are saying the /r is assumed automatically.
Thanks a lot for your answer. I was going in loops trying to figure
this out.
On Aug 2, 12:08 pm, Mike Whiting
<MikeWhit...@.discussions.microsoft.com> wrote:
> From BOL:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\=ad=ADminsql.chm::/ad_impt_bcp_0fqq.htm
> "...However, it is only necessary to enter the characters \r\n as the
> terminator when manually editing the terminator column of a bcp format fi=le.
> When you use bcp interactively and specify \n (newline) as the row
> terminator, bcp prefixes the \r (carriage return) character automatically=.=2E."
> So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
> automatically - that is why it is not part of the data that gets inserted= via
> BULK INSERT. You should not specify the rowterminator as '\r\n'.
> > So i run the above code and it inserts rows into table. No data is
> > present in last column.
> If you run the above code with '\n' as the row terminator and BULK INSERT= is
> not inserting any data into the last column, it must be for a different
> reason than the rowterminator. Do you have a sample row that you can prov=ide?
> Can you take a close look at what is between the last ';' and the 0x0D0A?= Try
> BULK INSERT with FIRSTROW =3D1 and LASTROW=3D1 so that you only insert th=e first
> row and see if you get data.
> Thanks.
> -Mike
>
> "rshivara...@.gmail.com" wrote:
> > BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> > WITH
> > (
> > FIELDTERMINATOR =3D ';',
> > ROWTERMINATOR =3D '\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- Hide quoted text -
> - Show quoted text -|||>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:
>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