SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk Insert Failure: NewLine Character in Data


Bulk Insert Failure: NewLine Character in Data

Author
Message
Amol Zagade
Amol Zagade
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 7
Hi
I have a file containing a text attribute that can have newlines into data. I am not sure how do I use Bulk Insert to load this file?

Field Delimiter: |~|
Row Delimiter : Newline

Eg. three Rows of file look like below. (2 Records is split into two lines because of newline)

id|~|Comments
1|~|This is My First Comment
2|~|This is My
First Comment
3|~|This is My First Comment

Is there a way to handle the Text Field if it is Qualified within Double Quotes. This also does not look like a safe option though, as my Data may contain double as it is a comments field.

Do I ask for a file where row-delimter is not a newline?

Thanks
david.hulme
david.hulme
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 6
Amol Zagade (3/11/2010)
Hi

Field Delimiter: |~|
Row Delimiter : Newline

Do I ask for a file where row-delimter is not a newline?



No: You ask for a replacement file where the newline character is not in the columns.

You could (try to) process the data supplied according to the RULE that states a new line denotes a new row, but you will have broken records.
You could spend a while cleaning up the rogue new line characters, but at what cost?
You could outsource the data to a commerical data-cleaning company, but cost remains an issue.

Unless you need to keep someone very happy and are prepared to run at a potential loss, send it back with an explanatory note.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search