Bulk Insert Invalid Collation

• cristopher.alvis

  SSC Veteran

  Points: 216

  More actions

  February 19, 2010 at 3:37 pm

  #217000

  Hey all rad SQL dudes,

  As a bit of a SQL newbie, I need a little help. I'm trying to import a tab delimited text file into a SQL 2005 table using a bulk insert with a format file:

  BULK INSERT

  dbo.tablename

  FROM 'C:\FolderName\flat_file_to_upload.txt'

  with (FIELDTERMINATOR = '\t',

  firstrow = 2

  , FORMATFILE = 'C:\FolderName\formatfile.fmt'

  )

  A portion of my format file:

  8.0

  19

  1 SQLCHAR 0 255 "\t" 1 "" SQL_Latin1_General_CP1_CI_AS

  2 SQLCHAR 0 255 "\t" 2 "" SQL_Latin1_General_CP1_CI_AS

  Try as I might to make it work, I keep getting the error message:

  Cannot perform bulk insert. Invalid collation name for source column 1 in format file 'C:\FolderName\formatfile.fmt'

  I've tired all the things I've read on other forums like using empty double quotes for the collation on the first line of the format file, etc. Nothing seems to work. Curiously, when running it without the format file specified in the bulk insert, it works just fine. Since the file comes from an outside vendor however, I really need a functional format file in place. I don't think it's an actual collation error. I think it's getting tripped up on something else and returning that error for some reason. I almost wonder if it's something out of whack in the file itself that I'm not seeing. I'm really at a loss here, so any input would be greatly appreciated. Thanks to all in advance. Cheers!

  Cris

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 21, 2010 at 4:00 pm

  #1122611

  I have listed some articles that pertain to the format file and it use with the BCP Utility:

  http://www.sqlservercentral.com/Forums/Topic273875-8-1.aspx

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35526

  http://www.developerfusion.com/code/5357/bulk-insert-from-flat-file-using-transactsql/

  http://support.microsoft.com/kb/67409

  http://www.issociate.de/board/post/180881/BCP_Format_File.html

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• cristopher.alvis

  SSC Veteran

  Points: 216

  More actions

  February 22, 2010 at 7:50 am

  #1122958

  Thank you Mr. Corgi. I'll give them a look!