Bulk Insert - Truncate the actual line length

• fuxing_audrey

  Grasshopper

  Points: 13

  More actions

  April 29, 2008 at 1:02 pm

  #187081

  Hi all,

  I have a problem during the bulk insertion. SQL truncated each line of the file.

  The format file is:

  9.0

  1

  1 SQLCHAR 0 8000 "\r" 1 if1 ""

  I tried the following format too:

  9.0

  1

  1 SQLCHAR 0 8000 "\r" 1 if1 ""

  9.0

  1

  1 SQLCHAR 0 8000 "" 1 if1 ""

  Table:

  CREATE TABLE #if1 (

  [if1] [varchar] (8000) NULL

  )

  Query:

  SET @sqlstr = 'BULK INSERT #if1 FROM ''' + @inputfilepath + '.if1'' WITH (FORMATFILE = ''' + @ifformat + '.fmt1'')'

  EXEC(@SQLSTR)

  Everything is fine. The only problem is the lines are truncated.

  Any ideas?

• Dave-3000

  SSCertifiable

  Points: 6186

  More actions

  April 29, 2008 at 4:02 pm

  #808698

  Truncated? Do you mean you were expecting 8000 characters and got less? What does your input data look like?

  There is no "i" in team, but idiot has two.

• Jeff Moden

  SSC Guru

  Points: 1004645

  More actions

  April 29, 2008 at 8:47 pm

  #808748

  Try using backslash n instead of backslash r. Some text files only have a "linefeed" or "newline" character instead of an actual carriage return.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)