Prev: XML Data Type Limitations
Next: How to list all machines currently connected to SQL Server
From: Rob on 3 May 2010 18:31
How would I be able to insert data from a csv file, wherein, part of the
value contained for a field, includes a comma (,)?

For example, in a csv file containing three values, delimited by a comma,
but one of them has commas defined as part of its value. How can I ensure
that bcp/bulk insert interprets that as a single value as opposed to multiple
values.

Thanks.
From: Geoff Schaller on 3 May 2010 21:59
Rob,

Basically you use a different delimiter.

When you generate the csv (hey... let's not call it csv shall we?) make
sure that field an row separators are predictable but weird. Then use
that template in your bcp import.

For example, we use row separators of ||+CRLF and field separators of
{<}.

Both these constructs almost never occur in real data so they are safe
to use.

Cheers,

Geoff Schaller
Software Objectives



"Rob" <Rob(a)discussions.microsoft.com> wrote in message
news:430BEC11-81D4-479F-A56B-3037ACFEC638(a)microsoft.com:

> How would I be able to insert data from a csv file, wherein, part of the
> value contained for a field, includes a comma (,)?
>
> For example, in a csv file containing three values, delimited by a comma,
> but one of them has commas defined as part of its value. How can I ensure
> that bcp/bulk insert interprets that as a single value as opposed to multiple
> values.
>
> Thanks.

From: Dan on 4 May 2010 08:44

"Rob" <Rob(a)discussions.microsoft.com> wrote in message
news:430BEC11-81D4-479F-A56B-3037ACFEC638(a)microsoft.com...
> How would I be able to insert data from a csv file, wherein, part of the
> value contained for a field, includes a comma (,)?
>
> For example, in a csv file containing three values, delimited by a comma,
> but one of them has commas defined as part of its value. How can I ensure
> that bcp/bulk insert interprets that as a single value as opposed to
> multiple
> values.
>
> Thanks.

Normally you would double quote the field that can contain commas - this way
it's treated as a single string inside the quotes, rather than being split.
Of course the CSV parser has to be able to handle quoted strings to deal
with this too though.

--
Dan

From: John Bell on 4 May 2010 09:35
On Mon, 3 May 2010 15:31:00 -0700, Rob <Rob(a)discussions.microsoft.com>
wrote:

>How would I be able to insert data from a csv file, wherein, part of the
>value contained for a field, includes a comma (,)?
>
>For example, in a csv file containing three values, delimited by a comma,
>but one of them has commas defined as part of its value. How can I ensure
>that bcp/bulk insert interprets that as a single value as opposed to multiple
>values.
>
>Thanks.


Not mentioned by the other replies... would be to load the data into a
a single column and then parse it from there, but you are going to
need to identify when the comma is part of a value or delimiter.

If there is always a comma in a given column then you can use a
staging table and re-construct the values from it's constiuent parts.

John

 | 
Pages: 1
Prev: XML Data Type Limitations
Next: How to list all machines currently connected to SQL Server