0

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

3
Contributors
2
Replies
4
Views
7 Years
Discussion Span
Last Post by DCS09
0

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?

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.