Bulk Insert, Format File

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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply