Bulk Insert Problem

Hi

I am trying to do a bulk insert using Query Analyzer for SQL 2000.

I have a Comma delimited file and a format file that i genrated using bcp.exe

the CSV file looks like this

08009700000,23,01,2007,23012007,16:28:09,01413412066,01,FreeConnect,00:00:05,0:05,0.083,0.002,2006,X
08009700000,24,01,2007,24012007,12:51:55,01413412066,01,FreeConnect,00:00:14,0:14,0.233,0.006,2006,X
08009700000,24,01,2007,24012007,12:52:28,01413412066,01,FreeConnect,00:00:10,0:10,0.167,0.004,2006,X

the format file look like this

8.0
15
1 SQLNCHAR 2 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 2 510 "," 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 2 510 "," 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 2 510 "," 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 2 510 "," 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 2 510 "," 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 2 510 "," 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 2 510 "," 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 2 510 "," 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 2 510 "," 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 2 510 "," 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 2 510 "," 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 2 510 "," 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 2 510 "," 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 2 510 "," 15 Field15 Latin1_General_CI_AS

and the T-SQL code that i am using is

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt')

i have also tried

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r\n')

and

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r')

what ever i do i get the get the follow error message

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

can any one help ?

I haven't read through your post completely, because I'm too busy today. But...

In Enterprise Manager (or Management Studio), use the 'Import Data' menu entry to go through a wizard to import your data. Use the option to save the package, or in MgtStudio, script it out. Then you can re-use the package/script when you need to. But hopefully you will find the answer to your problem when you step through the wizard and it tells you useful information like "I can't find the end of the line 10 lines in...".

Actually - having skimmed through, it sounds like it can't find the commas properly, or else your columns are too short. Probably the first though, because I imagine you would've noticed if your columns were short. The wizard should really help you here.

Hope this helps,

Rob|||

hi and thanks for the reply.

I have been playing around with this and i have now got round the error that i was getting.

The problem seems to be in the format file that i am using, which is odd cause it was genarated using the bcp.

I found 2 problems in this file

the first which caused the error about the first column being two long was the column prefix was set to 2, when i changed this to 0 for each column the error went away.

then then got a new error about unexpected end of line, I solved this buy removing the comma on the last line of the format file and replaceing it with \r\n

so my format file now looks like this

8.0

15
1 SQLNCHAR 0 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 "," 2 Field2 Latin1_General_CI_AS

...

15 SQLNCHAR 0 510 "\r\n" 15 Field15 Latin1_General_CI_AS

it now imports all the data, however it also seems to corrupt the data, if i do a select statement in QA the data looks like it is in the wrong font as it is all white empty boxes.

any one know why this happens ?

|||Dagz,

You got two of three issues answered, it looks like (changing the 2 to 0 and changing the final field terminator). If your file is Unicode, though, which I'm guessing it is from the SQLNCHAR in the format file, I think you need to specify the field terminators differently. Try this as the format file (or try SQLCHAR instead of SQLNCHAR, if your file is not a Unicode file):

8.0
15
1 SQLNCHAR 0 510 ",\x00" 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 ",\x00" 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 0 510 ",\x00" 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 0 510 ",\x00" 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 0 510 ",\x00" 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 0 510 ",\x00" 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 0 510 ",\x00" 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 0 510 ",\x00" 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 0 510 ",\x00" 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 0 510 ",\x00" 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 0 510 ",\x00" 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 0 510 ",\x00" 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 0 510 ",\x00" 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 0 510 ",\x00" 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 0 510 "\x0D\x00\x0A\x00" 15 Field15 Latin1_General_CI_AS

Steve Kass
http://www.stevekass.com
sql

Bulk Insert Problem

Hi

I am trying to do a bulk insert using Query Analyzer for SQL 2000.

I have a Comma delimited file and a format file that i genrated using bcp.exe

the CSV file looks like this

08009700000,23,01,2007,23012007,16:28:09,01413412066,01,FreeConnect,00:00:05,0:05,0.083,0.002,2006,X
08009700000,24,01,2007,24012007,12:51:55,01413412066,01,FreeConnect,00:00:14,0:14,0.233,0.006,2006,X
08009700000,24,01,2007,24012007,12:52:28,01413412066,01,FreeConnect,00:00:10,0:10,0.167,0.004,2006,X

the format file look like this

8.0
15
1 SQLNCHAR 2 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 2 510 "," 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 2 510 "," 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 2 510 "," 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 2 510 "," 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 2 510 "," 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 2 510 "," 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 2 510 "," 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 2 510 "," 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 2 510 "," 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 2 510 "," 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 2 510 "," 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 2 510 "," 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 2 510 "," 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 2 510 "," 15 Field15 Latin1_General_CI_AS

and the T-SQL code that i am using is

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt')

i have also tried

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r\n')

and

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r')

what ever i do i get the get the follow error message

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

can any one help ?

I haven't read through your post completely, because I'm too busy today. But...

In Enterprise Manager (or Management Studio), use the 'Import Data' menu entry to go through a wizard to import your data. Use the option to save the package, or in MgtStudio, script it out. Then you can re-use the package/script when you need to. But hopefully you will find the answer to your problem when you step through the wizard and it tells you useful information like "I can't find the end of the line 10 lines in...".

Actually - having skimmed through, it sounds like it can't find the commas properly, or else your columns are too short. Probably the first though, because I imagine you would've noticed if your columns were short. The wizard should really help you here.

Hope this helps,

Rob|||

hi and thanks for the reply.

I have been playing around with this and i have now got round the error that i was getting.

The problem seems to be in the format file that i am using, which is odd cause it was genarated using the bcp.

I found 2 problems in this file

the first which caused the error about the first column being two long was the column prefix was set to 2, when i changed this to 0 for each column the error went away.

then then got a new error about unexpected end of line, I solved this buy removing the comma on the last line of the format file and replaceing it with \r\n

so my format file now looks like this

8.0

15
1 SQLNCHAR 0 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 "," 2 Field2 Latin1_General_CI_AS

...

15 SQLNCHAR 0 510 "\r\n" 15 Field15 Latin1_General_CI_AS

it now imports all the data, however it also seems to corrupt the data, if i do a select statement in QA the data looks like it is in the wrong font as it is all white empty boxes.

any one know why this happens ?

|||Dagz,

You got two of three issues answered, it looks like (changing the 2 to 0 and changing the final field terminator). If your file is Unicode, though, which I'm guessing it is from the SQLNCHAR in the format file, I think you need to specify the field terminators differently. Try this as the format file (or try SQLCHAR instead of SQLNCHAR, if your file is not a Unicode file):

8.0
15
1 SQLNCHAR 0 510 ",\x00" 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 ",\x00" 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 0 510 ",\x00" 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 0 510 ",\x00" 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 0 510 ",\x00" 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 0 510 ",\x00" 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 0 510 ",\x00" 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 0 510 ",\x00" 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 0 510 ",\x00" 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 0 510 ",\x00" 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 0 510 ",\x00" 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 0 510 ",\x00" 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 0 510 ",\x00" 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 0 510 ",\x00" 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 0 510 "\x0D\x00\x0A\x00" 15 Field15 Latin1_General_CI_AS

Steve Kass
http://www.stevekass.com

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?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>

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.
>

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" 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 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 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 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
|||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 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> ó???Yéì/ó???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 field
> 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).[vbcol=seagreen]
> 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.
> In
> SQL-Server.
> with
> the
> well.
>

BULK INSERT from a table

Hi there.

I have a table that contains comma delimited text, and I am trying to convert this into another table

eg my target table looks like

Produce|Price|QuantityPerPrice

and my input table contains strings such as

"apples","7.5","10"

"pears","10","8"

"oranges","8","6"

Does anyone have any ideas on how to do this? I am after a solution that does them all at once: I am currently using charindex() to find each column, one at a time, but given the speed of BULK INSERT I would much rather do it as a table. The one solution that I don't want to resort to is to export the table with delimited strings to a data file, then BULK INSERT it...

Cheers

Neil

After reading the problem I am very interested to see the solution as I cant think of one without moving data to file or using charindex.|||Bulk insert or openrowset(bulk) (in SQL Server 2005) requires file as a data source. So what you are trying is not possible. You could however optimize some of the string splitting and do just insert. If there are large number of rows then perform the insert in batches in a transaction to get better performance. But I am confused as to why you would store such inefficient format in the database table and then try to manipulate it relationally. Why can't you store the data directly into the target table?|||

Thanks for the reply.

We are taking inputs with unknown numbers of fields and unknown delimiters, and working with them. We do this by importing the data into a single field and then analysing it. This leads to single-field tables with delimited data in them.

I think we are going to have to export with bcp, create a format file, then re-import with the known delimiter and fieldnames.

Which raises another question: is there a command line parameter I can pass to bulk insert so that it will take a delimiter and use the first row as fieldnames, or do I need a format file?

Regards,

Neil

|||I am still confused as to why you would first import the data into single column values and then try to do the parsing operation on the server. This seems inefficient to me. You could easily design DTS/SSIS packages or write your own scripting code to import the files directly in the first place. There is no facility in any of the bulk API to consider the first row as column names. You will have to skip it using the FIRSTROW parameter in BULK INSERT or similar option in BCP. There are also options to specify delimiter. Use of format files depends on the complexity of your data format. For the simple, delimiter case you may not require it. See BOL for more details on the command syntax and the BCP utility.

BULK INSERT from a table

Hi there.

I have a table that contains comma delimited text, and I am trying to convert this into another table

eg my target table looks like

Produce|Price|QuantityPerPrice

and my input table contains strings such as

"apples","7.5","10"

"pears","10","8"

"oranges","8","6"

Does anyone have any ideas on how to do this? I am after a solution that does them all at once: I am currently using charindex() to find each column, one at a time, but given the speed of BULK INSERT I would much rather do it as a table. The one solution that I don't want to resort to is to export the table with delimited strings to a data file, then BULK INSERT it...

Cheers

Neil

After reading the problem I am very interested to see the solution as I cant think of one without moving data to file or using charindex.|||Bulk insert or openrowset(bulk) (in SQL Server 2005) requires file as a data source. So what you are trying is not possible. You could however optimize some of the string splitting and do just insert. If there are large number of rows then perform the insert in batches in a transaction to get better performance. But I am confused as to why you would store such inefficient format in the database table and then try to manipulate it relationally. Why can't you store the data directly into the target table?|||

Thanks for the reply.

We are taking inputs with unknown numbers of fields and unknown delimiters, and working with them. We do this by importing the data into a single field and then analysing it. This leads to single-field tables with delimited data in them.

I think we are going to have to export with bcp, create a format file, then re-import with the known delimiter and fieldnames.

Which raises another question: is there a command line parameter I can pass to bulk insert so that it will take a delimiter and use the first row as fieldnames, or do I need a format file?

Regards,

Neil

|||I am still confused as to why you would first import the data into single column values and then try to do the parsing operation on the server. This seems inefficient to me. You could easily design DTS/SSIS packages or write your own scripting code to import the files directly in the first place. There is no facility in any of the bulk API to consider the first row as column names. You will have to skip it using the FIRSTROW parameter in BULK INSERT or similar option in BCP. There are also options to specify delimiter. Use of format files depends on the complexity of your data format. For the simple, delimiter case you may not require it. See BOL for more details on the command syntax and the BCP utility.

Bulk Insert does not handle text qualifier

I'm trying to use Bulk Insert to upload a flat file.
The file is comma delimited but contains one field that has quotation marks around it. That field typically contains a comma (which of course, I don't want to consider as a field delimeter)
here is an example of the layout.

1,test,D,"OCB, England",450727,8575

I use the DTS Bulk Insert task to help me generate a format file to use with the bulk insert statement. I tell the wizard that the file is comma delimited with a text qualifer and it's fine with that. But when I run the bulk insert, it bombs with a "String or Binary data would be trunctated error.
So, any idea's on how to bulk insert to be smart enough to deal with this situation?"String or Binary data would be trunctated" usually means that the string you are trying to insert is longer than that allowed by the field in your destination table. If, for instance, your table's field is defined as 50 characters (the default) and one of your quoted strings is longer than 50 characters, you will get this error.

Either increase the size of your destination field, or cast your string as a field of the proper width before attempting to insert it.

blindman

BULK INSERT and text fields

Hi,

I am trying to do a bulk insert using a text file that is comma delimited.

In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.

Is there a way to tell the BULK INSERT statement not to treat these as delimiters?

thanks,

sivilianyou need to set the field qualifiers.|||Do you mean using format files in order to do this?

If so I do not believe that you can do it even with that.

I found this excerpt from SQL Books Online.

Important Terminators must be chosen to ensure that their pattern does not appear in any of the data. For example, when using tab terminators with a field that contains tabs as part of the data, bcp does not know which tab represents the end of the field. The bcp utility always looks for the first possible character(s) that matches the terminator it expects. Using a character sequence with characters that do not occur in the data avoids this conflict.

If I am wrong and I hope I am; Can you elaborate as to how to set these field qualifers.

thanks,

sivilian

Bulk insert

hi all,
I am using bulk insert to insert records from a text file to a table.
The text file contains # or comma as fieldterminator. how to handle
both the field terminators in a stored procedure.
thanks to all,
kc.Do you mean that some columns are seperated by # and some columns are
seperated by commas? Or that there's no pattern at all to the separators?
-Paul
<kc4u7@.yahoo.com> wrote in message
news:1141886075.704092.89530@.i39g2000cwa.googlegroups.com...
> hi all,
> I am using bulk insert to insert records from a text file to a table.
> The text file contains # or comma as fieldterminator. how to handle
> both the field terminators in a stored procedure.
> thanks to all,
> kc.
>|||Hi Paul,
I am using bulk insert to transfer hugh data from a .dbf file to my sql
server table. Can you guide me in this case what shoud I specify as
fieldterminator and rowterminator?
Thanks in advance.
Regards,
Shailesh
"Paul Nielsen (MVP)" wrote:

> Do you mean that some columns are seperated by # and some columns are
> seperated by commas? Or that there's no pattern at all to the separators?
> -Paul
>
> <kc4u7@.yahoo.com> wrote in message
> news:1141886075.704092.89530@.i39g2000cwa.googlegroups.com...
>
>|||Shailesh wrote:
> Hi Paul,
> I am using bulk insert to transfer hugh data from a .dbf file to my sql
> server table. Can you guide me in this case what shoud I specify as
> fieldterminator and rowterminator?
> Thanks in advance.
> Regards,
> Shailesh
>
Shailesh,
Please don't post the same question multiple times. I answered in one
of the several other threads you posted to. I'll leave you to work out
which it was! :-(
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

BULK INSERT

Hi,
I am trying to do a bulk insert using a text file that is comma delimited.
In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.
Is there a way to tell the BULK INSERT statement not to treat these as delimiters?
thanks,
JohTo do this you will have to use a format file
For an example see
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

BULK INSERT

Hi,
I am trying to do a bulk insert using a text file that is comma delimited.
In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.
Is there a way to tell the BULK INSERT statement not to treat these as delimiters?
thanks,
John
To do this you will have to use a format file
For an example see
http://www.nigelrivett.net/BCP_quote...rmat_file.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!