I give up on this one...

I have tried and tried to get this to work and I'm doing something dumb.
I'm going to include sample data and my format file. Can someone help
me figure out my dumb mistake on a bulk insert using a format file?
Don't worry about data types, I'll handle that later. Thanks a lot!!!

CSV File:

ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPET
ITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContr
act,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch,
forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,Networ
kComparedTo
1,name1,Family Practice General Practice,FP,PCP,address1,Deerfield
Beach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4,
Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp1
2,name2,Family Practice General
Practice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,100006
7851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp2
3,name3,General Practice,GP,PCP,address3,Deerfield
Beach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4
Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp3
4,name4,Reproductive
Endocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,10
00083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4

Format File:
8.0
31
1 SQLCHAR 0 1 "\r\n" 0 quote Latin1_General_CI_AS
2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS
3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS
4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS
5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS
6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS
7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS
8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS
9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS
10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS
11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS
12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS
13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS
14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS
15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS
16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS
17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS
18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS
19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS
20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS
21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS
22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS
23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS
24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS
25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS
26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS
27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS
28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS
29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS
30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS
31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_AS

Here's a script for the table!

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Provider_Results]
GO

CREATE TABLE [dbo].[Provider_Results] (
[Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[NetworkComparedTo] [varchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[forceMatchUserId] [varchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[paymentPlatforms] [varchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[matchStatusColor] [varchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Thanks again. I am dying on this one. With everything included
hopefully someone (smarter than me) can figure it out.

Thanks,

Andrew

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Might look at the solution Erland gave me...
see subject "Bulk Insert Problems"

HTH

CBL

"Andrew Slentz" <ajslentz@.yahoo.com> wrote in message
news:40cf845e$0$25534$c397aba@.news.newsgroups.ws.. .
> I have tried and tried to get this to work and I'm doing something dumb.
> I'm going to include sample data and my format file. Can someone help
> me figure out my dumb mistake on a bulk insert using a format file?
> Don't worry about data types, I'll handle that later. Thanks a lot!!!
> CSV File:
> ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPET
> ITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContr
> act,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch,
> forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,Networ
> kComparedTo
> 1,name1,Family Practice General Practice,FP,PCP,address1,Deerfield
> Beach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4,
> Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp1
> 2,name2,Family Practice General
> Practice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,100006
> 7851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp2
> 3,name3,General Practice,GP,PCP,address3,Deerfield
> Beach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4
> Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp3
> 4,name4,Reproductive
> Endocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,10
> 00083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4
>
> Format File:
> 8.0
> 31
> 1 SQLCHAR 0 1 "\r\n" 0 quote Latin1_General_CI_AS
> 2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS
> 3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS
> 4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS
> 5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS
> 6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS
> 7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS
> 8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS
> 9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS
> 10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS
> 11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS
> 12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS
> 13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS
> 14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS
> 15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS
> 16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS
> 17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS
> 18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS
> 19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS
> 20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS
> 21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS
> 22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS
> 23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS
> 24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS
> 25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS
> 26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS
> 27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS
> 28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS
> 29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS
> 30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS
> 31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_AS
> Here's a script for the table!
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Provider_Results]
> GO
> CREATE TABLE [dbo].[Provider_Results] (
> [Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [NetworkComparedTo] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [forceMatchUserId] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [paymentPlatforms] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [matchStatusColor] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Thanks again. I am dying on this one. With everything included
> hopefully someone (smarter than me) can figure it out.
> Thanks,
> Andrew
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Might look at the solution Erland gave me...
see subject "Bulk Insert Problems"

HTH

CBL

"Andrew Slentz" <ajslentz@.yahoo.com> wrote in message
news:40cf845e$0$25534$c397aba@.news.newsgroups.ws.. .
> I have tried and tried to get this to work and I'm doing something dumb.
> I'm going to include sample data and my format file. Can someone help
> me figure out my dumb mistake on a bulk insert using a format file?
> Don't worry about data types, I'll handle that later. Thanks a lot!!!
> CSV File:
> ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPET
> ITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContr
> act,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch,
> forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,Networ
> kComparedTo
> 1,name1,Family Practice General Practice,FP,PCP,address1,Deerfield
> Beach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4,
> Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp1
> 2,name2,Family Practice General
> Practice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,100006
> 7851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp2
> 3,name3,General Practice,GP,PCP,address3,Deerfield
> Beach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4
> Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp3
> 4,name4,Reproductive
> Endocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,10
> 00083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4
>
> Format File:
> 8.0
> 31
> 1 SQLCHAR 0 1 "\r\n" 0 quote Latin1_General_CI_AS
> 2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS
> 3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS
> 4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS
> 5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS
> 6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS
> 7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS
> 8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS
> 9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS
> 10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS
> 11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS
> 12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS
> 13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS
> 14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS
> 15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS
> 16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS
> 17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS
> 18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS
> 19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS
> 20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS
> 21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS
> 22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS
> 23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS
> 24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS
> 25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS
> 26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS
> 27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS
> 28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS
> 29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS
> 30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS
> 31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_AS
> Here's a script for the table!
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Provider_Results]
> GO
> CREATE TABLE [dbo].[Provider_Results] (
> [Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [NetworkComparedTo] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [forceMatchUserId] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [paymentPlatforms] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [matchStatusColor] [varchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Thanks again. I am dying on this one. With everything included
> hopefully someone (smarter than me) can figure it out.
> Thanks,
> Andrew
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!