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 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?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...
> > 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?
>|||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.
"Uri Dimant" wrote:
> 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...
> > > 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?
> >
> >
>
>|||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.
> "Uri Dimant" wrote:
> > 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...
> > > > 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?
> > >
> > >
> >
> >
> >|||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.
"Ramon" wrote:
> 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.
> >
> > "Uri Dimant" wrote:
> >
> > > 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...
> > > > > 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?
> > > >
> > > >
> > >
> > >
> > >
>
>|||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.
> "Ramon" wrote:
> > 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> OIIAYEI/OIIAYEIA ?
> > II?IOONE OIAAOAYAA:
> > 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.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > 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...
> > > > > > 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?
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||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...
> > 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.
> >
> > "Ramon" wrote:
> >
> > > 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> OIIAYEI/OIIAYEIA ?
> > > II?IOONE OIAAOAYAA:
> > > 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.
> > > >
> > > > "Uri Dimant" wrote:
> > > >
> > > > > 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...
> > > > > > > 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?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>