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

Recommended Answers

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?

Jump to Post

All 2 Replies

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?

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.