bcp/bulk insert of csv file that contains comma
Rob
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.
Geoff Schaller
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" <R...@discussions.microsoft.com> wrote in message
news:430BEC11-81D4-479F...@microsoft.com:
Dan
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
John Bell
wrote:
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
ashish chotalia
>> On Tuesday, May 04, 2010 1:59 AM Geoff Schaller wrote:
>> Rob,
>>
>> Basically you use a different delimiter.
>>
>> When you generate the csv (hey... let us 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
>>> On Tuesday, May 04, 2010 8:44 AM Dan wrote:
>>> Normally you would double quote the field that can contain commas - this way
>>> it is 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
>>>> On Tuesday, May 04, 2010 9:35 AM John Bell wrote:
>>>> wrote:
>>>>
>>>>
>>>>
>>>> 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 is constiuent parts.
>>>>
>>>> John
>>>> Submitted via EggHeadCafe
>>>> Excel Generate High Quality RoadMaps
>>>> http://www.eggheadcafe.com/tutorials/aspnet/3310004f-e1ae-45a7-9bea-7b2b970d1230/excel-generate-high-quality-roadmaps.aspx
ashish chotalia
Have a look at <a href="http://tinyurl.com/2v4ndco">here</a>
> On Monday, May 03, 2010 6:31 PM Rob wrote:
>>>>> On Saturday, February 19, 2011 1:18 AM ashish chotalia wrote:
>>>>> You can convert List Separator value from qaws2efrt c42yu 87 before creating CSV file
>>>>> Submitted via EggHeadCafe
>>>>> Excel JSON Storing Data
>>>>> http://www.eggheadcafe.com/tutorials/aspnet/233e58b3-72f4-4220-8d45-37c2c44e795e/excel-json-storing-data.aspx
ashish chotalia
Have a look at <a href="http://tinyurl.com/2v4ndco">here</a>
> On Monday, May 03, 2010 6:31 PM Rob wrote:
>>>>>> On Saturday, February 19, 2011 1:21 AM ashish chotalia wrote:
>>>>>> You can convert List Separator value from Regional Setting before creating CSV file.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Have a look at <a href="http://tinyurl.com/2v4ndco">here</a>
>>>>>> Submitted via EggHeadCafe
>>>>>> Serializing Excel data for input to any Google visualization
>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/571d84dc-9fcf-44de-b2ad-005c12372ab3/serializing-excel-data-for-input-to-any-google-visualization.aspx