FIle Format Problems

[vuyiswamb]

Good Day All

I have a File Format Defined like this

9.0
4
1 SQLCHAR 0 100 "," 0 ExtraField ""
2 SQLCHAR 0 100 "," 1 Descr SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 2 ABREV SQL_Latin1_General_CP1_CI_AS

and i use it like this

BULK INSERT dbo.TBL_CMPS FROM  'C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv'
WITH (
      FORMATFILE  = 'C:\Format.DAT',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n' );

and my Table Defination is like this

CREATE TABLE [dbo].[TBL_CMPS](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DESCR] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ABREV] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

and my Error is

Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "C:\Format.DAT".

Thank you

[customhosting]

I could be wrong, but is this telling you the number of fields in the csv file do not match the number of files as defined in the table?

[DCS09]

Though the thread is very old, but it might help someone, thats why posting my reply.

You have numbered the columns wrongly.

9.0
4
1 SQLCHAR 0 100 "," 0 ExtraField ""
2 SQLCHAR 0 100 "," 1 Descr SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 2 ABREV SQL_Latin1_General_CP1_CI_AS

ur format file should be like:-

9.0
3
1 SQLCHAR 0 100 "," 1 ExtraField ""
2 SQLCHAR 0 100 "," 2 Descr SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 ABREV SQL_Latin1_General_CP1_CI_AS