SQL Bulk Import .fmt and .txt problem

• • Reply

  13 years ago

  by Gnub

  Gary

  , United Kingdom

  Joined 13 years ago
   I have a few issues with generating a table, with a fmt file, and a .txt file.

  Im getting errors:

  Msg 4866, Level 16, State 7, Line 1

  The bulk load failed. The column is too long in the data file for row 1, column 9. Verify that the field terminator and row terminator are specified correctly.

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

  Msg 7330, Level 16, State 2, Line 1

  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  Column 9,10,11,12,16 sometimes do not contain values, and are like ",,,," when seen on the .txt file. 

  .fmt format :

  ***

  9.0
  16
  1       SQLCHAR       0       9      ","      1      OBDATE         SQL_Latin1_General_CP1_CI_AS
  2       SQLCHAR       0       6      ","      2      ROUTE         SQL_Latin1_General_CP1_CI_AS
  3       SQLINT        0       3      ","      3      NIGHTS         ""
  4       SQLCHAR       0       10     ","      4      CODE         SQL_Latin1_General_CP1_CI_AS
  5       SQLCHAR       0       10     ","      5      OBFN         SQL_Latin1_General_CP1_CI_AS
  6       SQLCHAR       0       10     ","      6      OBDEPTIME         SQL_Latin1_General_CP1_CI_AS
  7       SQLCHAR       0       10     ","      7      OBARRTIME         SQL_Latin1_General_CP1_CI_AS
  8       SQLCHAR       0       10     ","      8      OBSEATS         SQL_Latin1_General_CP1_CI_AS
  9       SQLCHAR       2       10     ","      9      IBFN         SQL_Latin1_General_CP1_CI_AS
  10      SQLCHAR       2       10     ","      10     IBDEPTIME         SQL_Latin1_General_CP1_CI_AS
  11      SQLCHAR       2       10     ","      11     IBARRTIME         SQL_Latin1_General_CP1_CI_AS
  12      SQLCHAR       2       10     ","      12     IBSEATS         SQL_Latin1_General_CP1_CI_AS
  13      SQLINT        0       10     ","      13     ADULT         ""
  14      SQLINT        0       10     ","      14     CHILD         ""
  15      SQLINT        0       10     ","      15     INFANT         ""
  16      SQLCHAR       2       3      "\t"   16     ONEWAY         SQL_Latin1_General_CP1_CI_AS
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  ***

  SQL Query:

  BULK INSERT FREEDOM
     FROM '\\SERVER1\ctsshare\FLATFILES\availff.txt'
     WITH (FORMATFILE = '\\SERVER1\ctsshare\DATABASE\FREEDOM.fmt');
  GO
  SELECT * FROM FREEDOMTS;
  GO
  
  
  
  
  

  ***

  Can anyone help?

  thanks in advance

   

  Report abuse