Bulk Insert, Format File

• bsluman

  Say Hey Kid

  Points: 671

  More actions

  November 4, 2002 at 4:50 pm

  #64422

  Ok, I am trying to do a bulk insert into a table. Seems pretty straight forward. Problem is that I get this error:Server: Msg 4839, Level 16, State 1, Line 1

  Cannot perform bulk insert. Invalid collation name for source column 8 in format file 'c:\bcp.fmt'.

  Here is what I have:

  fmt file:

  8.0

  8

  1SQLCHAR00""0recid"RAW"

  2SQLCHAR050","2PlanName"SQL_Latin1_General_CP1_CI_AS"

  3SQLCHAR075","3Country"SQL_Latin1_General_CP1_CI_AS"

  4SQLCHAR09","4Rate"RAW"

  5SQLCHAR010","5EffectiveDate"RAW"

  6SQLCHAR00""0ImportDate"RAW"

  7SQLCHAR09","7Round1"RAW"

  8SQLCHAR09"\r\n"8Round2"RAW"

  Bulk Insert Statement:

  BULK INSERT vo..ratehistory2 FROM 'D:\BillingProject\rateadmen_p\WTC_23-9-2002.csv'

  WITH (FORMATFILE = 'c:\bcp.fmt')

  Here is the table:

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

  drop table [dbo].[ratehistory2]

  GO

  CREATE TABLE [dbo].[ratehistory2] (

  [recid] [int] IDENTITY (1, 1) NOT NULL ,

  [PlanName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [Country] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [Rate] [numeric](18, 4) NULL ,

  [EffectiveDate] [datetime] NULL ,

  [ImportDate] [datetime] NULL ,

  [Round1] [numeric](18, 0) NULL ,

  [Round2] [numeric](18, 0) NULL

  ) ON [PRIMARY]

  GO

  Any help you can give me would be much appreciated.

• bsluman

  Say Hey Kid

  Points: 671

  More actions

  November 4, 2002 at 5:44 pm

  #440305

  Here is the actual file I am importing:

  Planname,Country,Rate,Effectivedate,R1,R2

  WTC_p,Italy,0.021,9/24/2002,30,6

  WTC_p,Peru - Lima,0.0228,9/24/2002,30,6

  WTC_p,United States,0.025,9/24/2002,30,6

  WTC_p,Poland,0.057,9/24/2002,30,6

  WTC_p,Colombia,0.0816,9/24/2002,30,6

  WTC_p,Philippines,0.105,9/24/2002,30,6

  WTC_p,Philippines - Manila,0.105,9/24/2002,30,6

  WTC_p,Bangladesh - Dhaka,0.1056,9/24/2002,30,6

  WTC_p,Morocco - Casablanca,0.109,9/24/2002,30,6

  WTC_p,Ecuador - Guayaquil,0.122,9/24/2002,30,6