Bulk Insert of comma seperated textfile with text qualifiers

I want to insert a text file with comma seperated fields. De text-field have
text qualifiers:
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text quali
fiers. In the example screen the data looked perfect. After the insert howev
er the text qualifiers where imported into the colimns as well. (The first c
olumn contained "field1" in
stead of field1)
Does anybody have suggestions on how to solve this?Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> have text qualifiers:
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
>|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to be
tter explain what I want, maybe you can than explain how your reply fits int
o that.
My goal is an import from a textfile. This textfile is comma-delimited with
text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within
this Task it is possible to define a format file (fmt) in which the wizard
writes information about the import-file. Following the wizard it will ask y
ou if the file is fixed len
gth or delimited. I choose delimited, choose the text qualifier and hit the
Next-key. Than I choose Comma as field seperator. In the example list the fi
le was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In
the destination column however you could see that the text qualifiers were
ignored. The double-quotes were in the fields and every comma in the file wa
s used as a field seperator|||Under [Transform Data Task Properties] -> [Options] tab specify [
;Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.[vbcol=seagreen]
> "Uri Dimant" wrote:
>
text-field[vbcol=seagreen]
text[vbcol=seagreen]
(The[vbcol=seagreen]|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Inser
t Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Ins
ert Task.
"Ramon" wrote:

> Under [Transform Data Task Properties] -> [Options] tab specify &#
91;Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ó???Yéì/ó???
éìá ×
> ??×?ó??è óì???àY??:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> with text qualifiers for the char-fields.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as fie
ld
> seperator. In the example list the file was correctly shown.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> text-field
> text
> (The
>
>|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.[vbcol=seagreen]
> "Ramon" wrote:
>
to[vbcol=seagreen]
fits[vbcol=seagreen]
the[vbcol=seagreen]
delimited,[vbcol=seagreen]
field[vbcol=seagreen]
errors.[vbcol=seagreen]
the[vbcol=seagreen]
In[vbcol=seagreen]
SQL-Server.[vbcol=seagreen]
with[vbcol=seagreen]
the[vbcol=seagreen]
well.[vbcol=seagreen]|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> / :
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> /
> :
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Insert Task is supposed to be much faster in loading the data.
> Insert Task.
try[vbcol=seagreen]
> to
> fits
comma-delimited[vbcol=seagreen]
DTS.[vbcol=seagreen]
> the
it[vbcol=seagreen]
> delimited,
> field
> errors.
qualifiers[vbcol=seagreen]
> the
Excel.[vbcol=seagreen]
> In
> SQL-Server.
> with
> the
> well.
>