Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert Failure: NewLine Character in Data Expand / Collapse
Author
Message
Posted Thursday, March 11, 2010 2:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 6, 2011 9:53 PM
Points: 1, 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
Post #880813
Posted Thursday, May 20, 2010 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 3:40 AM
Points: 4, 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.
Post #924987
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse