BULK Insert error on format file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BULK Insert error on format file

Author

Topic

wtgmvgwsg
Starting Member

3 Posts

Posted - 2008-09-04 : 11:32:29

I am using SQL 2005. I am trying to load a table using BULK Insert with data from a csv or tab delimited file. Here is my table definition, format file, and a few rows from my input file.

CREATE TABLE [dbo].[account_Codes](
[acct_cd] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[acct_desc] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[acct_stat] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_account_Codes_acct_stat] DEFAULT ('A'),
[rec_ts] [datetime] NULL CONSTRAINT [DF_account_Codes_rec_ts] DEFAULT (getdate()),
CONSTRAINT [PK_Account_Codes] PRIMARY KEY CLUSTERED
(
[acct_cd] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

My format file is:

9.0
4
1 SQLCHAR 0 20 "\t" 1 acct_cd COLLATE SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\t" 2 acct_desc COLLATE SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\t" 0 acct_stat ""
4 SQLCHAR 0 0 "\r\n" 0 rec_ts ""

Notice I am skipping the last two columns to be loaded since they have database defaults. I have also tried using SQLVARYCHAR on the first two columns. No difference.

Sample of my data

100150 INVENTORY ASSETS 1 2
100160 PIPELINE INVENTORY (GENERAL) 1 2
100161 COLUMBIA 1 2
100162 ATCHAFALAYA 1 2
100165 SHORE DELIVERY BOOSTER PUMP 1 2

tab delimited. I have also tried comma delimted and changed the format file accordingly but I still get the same error message

Cannot bulk load. Invalid column number in the format file

Please provide any assistance. I think I have given you all the information that I'm aware of.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:43:29

why are you using SQLCHAR for last 2 numeric values?

wtgmvgwsg
Starting Member

3 Posts

Posted - 2008-09-04 : 11:51:22

I changed it the last one to SQLDATETIME

wtgmvgwsg
Starting Member

3 Posts

Posted - 2008-09-04 : 12:02:24

I resolved my problem just by taking the word "COLLATE" out of my format file. I guess I was just cutting and pasting and didn't quite understand the format of the file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources