Bulk Insert with mid-string newlines

Hi.
I have a text file with several fields. One of these sometimes contains
newline characters. Any fields that do include newline characters are
enclosed with double quotes. What Bulk Insert command line do I need to use
to impot the file without it treating the enclosed newline characters as end
of row delimiters?
Example:
a,b,"c/nld",e
becomes
a b c
d e
where it should be
a b c/nd e
Thanks in advance,
Neil JonesTo clarify, I am looking for a commandline paramter that allows me to
designate a string delimiter, so that everything within the delimiters is
treated as a single string.
"NeilDJones" wrote:

> Hi.
> I have a text file with several fields. One of these sometimes contains
> newline characters. Any fields that do include newline characters are
> enclosed with double quotes. What Bulk Insert command line do I need to us
e
> to impot the file without it treating the enclosed newline characters as e
nd
> of row delimiters?
> Example:
> a,b,"c/nld",e
> becomes
> a b c
> d e
> where it should be
> a b c/nd e
> Thanks in advance,
> Neil Jones|||If you have custom formating, you'd want to use format file.
http://msdn.microsoft.com/library/e...pt_bcp_9yat.asp
-oj
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:8EBC7227-CD12-4450-93E1-F94E4D5621C6@.microsoft.com...
> Hi.
> I have a text file with several fields. One of these sometimes contains
> newline characters. Any fields that do include newline characters are
> enclosed with double quotes. What Bulk Insert command line do I need to
> use
> to impot the file without it treating the enclosed newline characters as
> end
> of row delimiters?
> Example:
> a,b,"c/nld",e
> becomes
> a b c
> d e
> where it should be
> a b c/nd e
> Thanks in advance,
> Neil Jones|||Thanks for the reply.
Looking at the information for the format file, I still cannot see how I
specify string delimiters, as I am able to do in Excel or Access. Is this
just something that BULK INSERT doesn't handle properly?
Cheers,
Neil
"oj" wrote:

> If you have custom formating, you'd want to use format file.
> http://msdn.microsoft.com/library/e...pt_bcp_9yat.asp
>
>
> --
> -oj
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:8EBC7227-CD12-4450-93E1-F94E4D5621C6@.microsoft.com...
>
>|||Sorry for the late reply.
With bulk load (bcp/bulk insert), you can define the column and row
delimiter. However, each must be different from each other (i.e. not
possible when column and row delimiter is defined as \r\n).
If excel/access can format it correctly for you, you might want to send the
data through it first before importing into sqlserver.
-oj
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:DE2EC0CB-E11F-4DC5-9496-D03482518603@.microsoft.com...
> Thanks for the reply.
> Looking at the information for the format file, I still cannot see how I
> specify string delimiters, as I am able to do in Excel or Access. Is this
> just something that BULK INSERT doesn't handle properly?
> Cheers,
> Neil
> "oj" wrote:
>|||Thanks.
I think I am puttnig this badly.
There is no problem with row or column delimiters. The column delimiter is a
comma, and the row delimiter is newline.
Some of the columns are strings, and these may or may not contain double
quotes ("). If there are double quotes, then everything inside the quotes is
a single string.
The problem arises when there is a newline character within the double
quote-delimited string, eg "Hello\nWorld", which is the string representing
Hello
World
Ideally, this would import as a single string. Excel and Access both do this
fine, but bulk insert doesn't recognise the string delimiter ", and breaks
the string over two lines.
I am hoping someone out there knows how to specify a string delimiter, but
am thinking that there ain't one.
Cheers,
Neil
"oj" wrote:

> Sorry for the late reply.
> With bulk load (bcp/bulk insert), you can define the column and row
> delimiter. However, each must be different from each other (i.e. not
> possible when column and row delimiter is defined as \r\n).
> If excel/access can format it correctly for you, you might want to send th
e
> data through it first before importing into sqlserver.
> --
> -oj
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:DE2EC0CB-E11F-4DC5-9496-D03482518603@.microsoft.com...
>
>