HELP: Bulk Insert error - SQL Server Forums

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)

HELP: Bulk Insert error

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-09-06 : 14:15:32

Hi -- I'm trying to bulk insert a file using the BULK INSERT command (SQL2000) and I keep getting the following error:

Server: Msg 4828, Level 16, State 1, Line 1
Could not bulk insert. Invalid destination table column number for source column 1 in format file 'c:\calling_list.fmt'.

The first 3 rows of the data file are here: (note that this file uses a line-feed character as the row terminator.)
"1234567891234567","MP","CT2","2005/08/31","ES","FPAYHI","RHLD025V","","X","","","","","00000","","0CD01001","000000000","0.00","","2005/09/04","14.59.21","12","2005/08/06","","","","24","CBBB","D","","","","02","","","","2005/09/04","","","","14.59.21","","","","12","","","","01","","sunday","","2",""
"1234567891234567","MP","BOX","2005/09/01","PS","FPAYHI","RBS0030V","","X","20","20","707","","","","01104054","000000000","0.00","tnjone","2005/09/04","19.59.37","52","2005/08/25","","","","8","ONBB","L","L","","","02","","","","2005/09/04","","","","19.59.37","","","","52","","","","01","","sunday","","2",""
"1234567891234567","MP","BOX","2000/00/00","PS","FPAYHI","RBS0007V","","X","15","20","714","","","","01104090","000000000","0.00","","2005/09/04","19.59.11","13","2005/09/02","","","","1","CBBB","L","","","","02","","","","2005/09/04","","","","19.59.11","","","","13","","","","01","","sunday","","2",""

The contents of the format file is here:

8.0
55
1 SQLCHAR 0 0 """ 0 first_quote ""
2 SQLCHAR 0 0 "","" 1 acctnum ""
3 SQLCHAR 0 0 "","" 2 prodcode ""
4 SQLCHAR 0 0 "","" 3 client_prodcode ""
5 SQLCHAR 0 0 "","" 4 next_work_date ""
6 SQLCHAR 0 0 "","" 5 time_zone ""
7 SQLCHAR 0 0 "","" 6 queue_id ""
8 SQLCHAR 0 0 "","" 7 disc_grp ""
9 SQLCHAR 0 0 "","" 8 custom_data_10 ""
10 SQLCHAR 0 0 "","" 9 custom_data_11 ""
11 SQLCHAR 0 0 "","" 10 custom_data_20 ""
12 SQLCHAR 0 0 "","" 11 custom_data_21 ""
13 SQLCHAR 0 0 "","" 12 custom_data_40 ""
14 SQLCHAR 0 0 "","" 13 custom_data_41 ""
15 SQLCHAR 0 0 "","" 14 custom_data_60 ""
16 SQLCHAR 0 0 "","" 15 custom_data_61 ""
17 SQLCHAR 0 0 "","" 16 custom_data_80 ""
18 SQLCHAR 0 0 "","" 17 custom_data_90 ""
19 SQLCHAR 0 0 "","" 18 dollars_risk ""
20 SQLCHAR 0 0 "","" 19 agent ""
21 SQLCHAR 0 0 "","" 20 dte ""
22 SQLCHAR 0 0 "","" 21 tme ""
23 SQLCHAR 0 0 "","" 22 code ""
24 SQLCHAR 0 0 "","" 23 entrydate ""
25 SQLCHAR 0 0 "","" 24 statusflag ""
26 SQLCHAR 0 0 "","" 25 recalldate ""
27 SQLCHAR 0 0 "","" 26 recalltime ""
28 SQLCHAR 0 0 "","" 27 dayscnt ""
29 SQLCHAR 0 0 "","" 28 phonestat ""
30 SQLCHAR 0 0 "","" 29 zonephone1 ""
31 SQLCHAR 0 0 "","" 30 zonephone2 ""
32 SQLCHAR 0 0 "","" 31 zonephone3 ""
33 SQLCHAR 0 0 "","" 32 zonephone4 ""
34 SQLCHAR 0 0 "","" 33 phonecnt1 ""
35 SQLCHAR 0 0 "","" 34 phonecnt2 ""
36 SQLCHAR 0 0 "","" 35 phonecnt3 ""
37 SQLCHAR 0 0 "","" 36 phonecnt4 ""
38 SQLCHAR 0 0 "","" 37 lastdate1 ""
39 SQLCHAR 0 0 "","" 38 lastdate2 ""
40 SQLCHAR 0 0 "","" 39 lastdate3 ""
41 SQLCHAR 0 0 "","" 40 lastdate4 ""
42 SQLCHAR 0 0 "","" 41 lasttime1 ""
43 SQLCHAR 0 0 "","" 42 lasttime2 ""
44 SQLCHAR 0 0 "","" 43 lasttime3 ""
45 SQLCHAR 0 0 "","" 44 lasttime4 ""
46 SQLCHAR 0 0 "","" 45 laststat1 ""
47 SQLCHAR 0 0 "","" 46 laststat2 ""
48 SQLCHAR 0 0 "","" 47 laststat3 ""
49 SQLCHAR 0 0 "","" 48 laststat4 ""
50 SQLCHAR 0 0 "","" 49 curphone ""
51 SQLCHAR 0 0 "","" 50 recallphone ""
52 SQLCHAR 0 0 "","" 51 jobname ""
53 SQLCHAR 0 0 "","" 52 recallnumber ""
54 SQLCHAR 0 0 "","" 53 counter ""
55 SQLCHAR 0 0 ""\n" 54 finoper ""

Can anyone help me figure out why this error is occurring? I can't see anything wrong with the format file.

Thanks in advance,

Bill

MuadDBA

628 Posts

Posted - 2005-09-06 : 15:15:57

I am a little confused.....the first column of your data seems to be the first column you wish to import....why does it have a zero for its destination column?

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-09-06 : 15:28:45

crazyjoe -- This is a way of handling the first double-quote of each record. Since the destination column is 0, the first double-quote is essentially ignored. All of the other double-quotes on each record are handled by the field terminator. I've seen this technique work elsewhere but I can't figure out if it is this that is causing the problem.

Bill

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-06 : 17:29:02

See http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html
for the quote delimitted data I used

8.0
5
1 SQLCHAR 0 0 "\"" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 "\"," 1 s Latin1_General_CI_AS
3 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
4 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
5 SQLCHAR 0 0 "\"\r\n" 4 u Latin1_General_CI_AS

where one field is not quote delimitted.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-09-07 : 09:05:42

nr -- This did the trick. Thanks!

Bill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources