|
|
Posted by Roy Harvey on 08/02/07 18:39
>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
Navigation:
[Reply to this message]
|