FIle Format Problems

[Vimalsoft Pty Ltd]

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

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[dan sh]

I think if you change the column count after 9.0 to 3, it should work.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

[Vimalsoft Pty Ltd]

i get this

Msg 4862, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Format.DAT" could not be read. Operating system error code (null).

I have give a Full Permission to Everyone, Admin, ASP.NET Thanks

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[dan sh]

Try placing return(enter) after SQL_Latin1_General_CP1_CI_AS.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

[Vimalsoft Pty Ltd]

still the Same Error Thanks

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Ashfield]

Its looking for the file in c:\ on your SQL SERVER machine, not your pc. Have you copied the file there?

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Vimalsoft Pty Ltd]

yes. The First Part of my code is to code this file in that Directory,in debug mode, i can see that the file has been created there and it has proper permissions on it.

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Ashfield]

Just a thought,if you are creating the format file in code before calling your proc, are you sure your code has properly closed the file? While in debug can you stop just before running the proc and see if you can open the file with another program such as notepad? May be total rubbish, but its a thought :)

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Vimalsoft Pty Ltd]

Yes the File is totally closed. I have thought of another way to do this. well not always a good idea. The Reason why i use a format File is that there is an Identity Field. Now what i did is that i drop the field and imported the data and recreated the Field and it looks OK. Thanks :)

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Ashfield]

There are many ways to skin a cat - pleased you found one that worked for you :-D

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Vimalsoft Pty Ltd]

Thank you :)

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/