Bulk insert formatfile problem

• Harry Douglass

  SSC-Addicted

  Points: 416

  More actions

  December 21, 2004 at 2:57 pm

  #91895

  Hello, All.

  Trying to use a bulk insert and getting the following message:

  Server: Msg 4829, Level 16, State 1, Line 1

  Could not bulk insert. Error reading destination table column name for source column 23 in format file 'E:\Inetpub\wwwroot\RiskWeb\bool.fmt'.

  Here is my bool.fmt file

  7.0

  23

  01 SQLCHAR 0 9 "," 0 a

  02 SQLCHAR 0 9 "," 0 b

  03 SQLCHAR 0 9 "," 0 c

  04 SQLCHAR 0 9 "," 0 d

  05 SQLCHAR 0 9 "," 1 BooleanID

  06 SQLCHAR 0 9 "," 0 f

  07 SQLCHAR 0 9 "," 0 g

  08 SQLCHAR 0 9 "," 0 h

  09 SQLCHAR 0 9 "," 0 i

  10 SQLCHAR 0 9 "," 0 j

  11 SQLCHAR 0 9 "," 0 k

  12 SQLCHAR 0 9 "," 0 l

  13 SQLCHAR 0 9 "," 0 m

  14 SQLCHAR 0 9 "," 0 n

  15 SQLCHAR 0 9 "," 0 o

  16 SQLCHAR 0 9 "," 0 p

  17 SQLCHAR 0 9 "," 0 q

  18 SQLCHAR 0 9 "," 0 r

  19 SQLCHAR 0 9 "," 0 s

  20 SQLCHAR 0 9 "," 0 t

  21 SQLCHAR 0 9 "," 0 u

  22 SQLCHAR 0 80 "," 2 BooleanDesc

  23 SQLCHAR 0 255 "\r\n" 3 BooleanExp

  I only want to import the 5th, 22nd and 23rd columns, but something is not right.  Here is my stored procedure:

  CREATE PROCEDURE BooleanIDsFMT

  (

    @FilePathName varchar(100)

  )

  AS

  TRUNCATE TABLE BooleanTest

  DECLARE @sql varchar(1000)

  BEGIN

    SET @sql = "BULK INSERT BooleanTest FROM '" +@FilePathName+ "' WITH (FORMATFILE = 'E:\Inetpub\wwwroot\RiskWeb\bool.fmt')"

  END

  EXEC(@SQL)

  GO

  Any help is appreciated!

  Harry

• December 24, 2004 at 8:00 am

  This was removed by the editor as SPAM

• THANG HOANG

  Say Hey Kid

  Points: 662

  More actions

  December 28, 2004 at 12:52 pm

  #535182

  The error suggests there is problem with the destination table definition. Make sure the column name and data type match.

  It would also be helpful if you post the table definition and sample of import data file.

• Pinaki Sen

  Newbie

  Points: 5

  More actions

  April 20, 2005 at 10:07 pm

  #553964

  Recreate the fmt file from the Bulk Insert tool`s format file generate option.

  This might help.

   

   

• Vijaykumar-250739

  Valued Member

  Points: 57

  More actions

  August 5, 2005 at 3:48 am

  #579942

  Hi all,

  I am also getting the same error.

  Server: Msg 4829, Level 16, State 1, Line 1

  Could not bulk insert. Error reading destination table column name for source column 17

  Can any one help me.

  Thanks in advance.

  VJ

• Vijaykumar-250739

  Valued Member

  Points: 57

  More actions

  August 5, 2005 at 3:58 am

  #579943

  Hi,

  I could resolve it in the end. Generated the format file again and it just worked fine.

  Also check the spacing in the format file.

  Vj