May 28, 2008 at 11:15 am
I'm getting the following message when I execute a BULK INSERT statement:
BULK INSERT dbName..FCALLPUT FROM '\\machine\temp\FCALLPUT.txt'
WITH(FIRSTROW=2,FORMATFILE = '\\machine\temp\FCALLPUT.fmt')
Error Message:
Server: Msg 4823, Level 16, State 1, Line 1
Could not bulk insert. Invalid column number in format file '\\machine\temp\FCALLPUT.fmt'.
(ignore dbName and machine as error, changed here to protect privacy)
Here is the format file (FCALLPUT.fmt):
8.0
15
1SQLCHAR09"|"1af13_key_secynoSQL_Latin1_General_CP1_CI_AS NULL
2SQLCHAR010"|"2af13_key_calldateNULL
3SQLCHAR01"|"3af13_key_callcodeSQL_Latin1_General_CP1_CI_AS NULL
4SQLCHAR01"|"4af13_callcodeSQL_Latin1_General_CP1_CI_AS NULL
5SQLCHAR010"|"5af13_calldateNULL
6SQLCHAR05"|"6af13_callprNULL
7SQLCHAR02"|"7af13_windowNULL
8SQLCHAR01"|"8af13_sourceSQL_Latin1_General_CP1_CI_AS NULL
9SQLCHAR020"|"9af13_liabnoteSQL_Latin1_General_CP1_CI_AS NULL
10SQLCHAR010"|"10af13_recorddtNULL
11SQLCHAR01"|"11af13_switch1SQL_Latin1_General_CP1_CI_AS NULL
12SQLCHAR01"|"12af13_switch2SQL_Latin1_General_CP1_CI_AS NULL
13SQLCHAR01"|"13af13_switch3SQL_Latin1_General_CP1_CI_AS NULL
14SQLCHAR01"|"14af13_switch4SQL_Latin1_General_CP1_CI_AS NULL
15SQLCHAR010"\r"15af13_xtradateNULL
(ignore extra line feeds, they don't exsist in orginal file)
AND here is the data file (FCALLPUT.txt):
af13_key_secyno|af13_key_calldate|af13_key_callcode|af13_callcode|af13_calldate|af13_callpr|af13_window|af13_source|af13_liabnote|af13_recorddt|af13_switch1|af13_switch2|af13_switch3|af13_switch4|af13_xtradate
001669AK6|2004-02-01|X|X|2004-02-01|104.750000|0|M| || | | | |2004-02-01
003714870|2003-12-15|X|X|2003-12-15|100.000000|0|M| || | | | |2003-12-15
00432MAT6|2003-07-06|T|T|2003-07-06|100.000000|0|M| || | |C| |2003-07-06
00432MAW9|2003-08-17|T|T|2003-08-17|100.000000|0|M| || | |C| |2003-08-17
005068JF9|2013-02-01|X|X|2013-02-01|100.000000|0|M| || | | | |2013-02-01
008428KL7|2007-07-01|X|X|2007-07-01|101.000000|0|M| || | | | |2007-07-01
008428KL7|2009-07-01|X|X|2009-07-01|100.000000|0|M| || | | | |2009-07-01
012086BL7|2012-08-01|X|X|2012-08-01|100.000000|0|M| || | | | |2012-08-01
012086BL7|2013-08-01|X|X|2013-08-01|100.000000|0|M| || | | | |2013-08-01
012086BL7|2014-08-01|X|X|2014-08-01|100.000000|0|M| || | | | |2014-08-01
012086BL7|2015-08-01|X|X|2015-08-01|100.000000|0|M| || | | | |2015-08-01
012086BL7|2016-08-01|X|X|2016-08-01|100.000000|0|M| || | | | |2016-08-01
012086BL7|2017-08-01|X|X|2017-08-01|100.000000|0|M| || | | | |2017-08-01
012086BL7|2018-08-01|X|X|2018-08-01|100.000000|0|M| || | | | |2018-08-01
012086BL7|2019-08-01|X|X|2019-08-01|100.000000|0|M| || | | | |2019-08-01
012086BL7|2020-08-01|X|X|2020-08-01|100.000000|0|M| || | | | |2020-08-01
Three pairs of eyes seems to think the format file is correct based on the data file, so what else could be causing this error? There is a carriage return/line feed at the end of each data row and the fields are "pipe" delimited. The format file columns are tab delimited (as described in BOL).
I know this says T-SQL for SS2K5, but the syntax would have changed. We actually are still using SS2000 (v8.0)
May 28, 2008 at 2:35 pm
For those interested the errors I was getting related to the Collation sequence.
For char and varchar data types I had SQL_Latin1_General_CP1_CI_AS NULL, which I had copied from a CREATE TABLE script. Silly me, the NULL was allowed values, not the collation sequence. Shortened it up to SQL_Latin1_General_CP1_CI_AS and everything worked.
Good tip found about NOT worrying what the field length is from the source file, just always set that to 999, which will help you find truncation errors.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply