none
How to avoid last column in data file while doing BULK INSERT?

    Question

  • Hi I am trying to load data file to SQL server table using bulk insert and non-XML format.

    Below is my data file

    1	Skipme	FirstName1	LastName1	skipname
    2 Skipme FirstName2 LastName2 skipname
    3 Skipme FirstName3 LastName3 skipname
    4 Skipme FirstName4 LastName4 skipname
    5 Skipme FirstName5 LastName5 skipname
    6 Skipme FirstName6 LastName6 skipname

    Below is my non-XML format file:

    11.0
    5
    1 SQLCHAR 0 0 "\t" 1 PersonID ""
    2 SQLCHAR 0 0 "\t" 0 ExtraField SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0 0 "\t" 2 FirstName SQL_Latin1_General_CP1_CI_AS
    4 SQLCHAR 0 0 "\t" 3 ExtraField SQL_Latin1_General_CP1_CI_AS
    5 SQLCHAR 0 0 "\r\n" 0 LastName SQL_Latin1_General_CP1_CI_AS

    After executing the following query I am getting following result:

    INSERT INTO #temp1 
       SELECT *
          FROM  OPENROWSET(BULK  'c:\opentest.ci',
          FORMATFILE='c:\opentest.fmt' ,
           FIRSTROW =  0
           ) AS t1;
    
    PersonID FirstName            LastName
    -------- --------------   --------------
    1        FirstName1           LastName1
    2        FirstName2           LastName2
    3        FirstName3           LastName3
    4        FirstName4           LastName4
    5        FirstName5           LastName5
    

    Please help me understand why last row is missing? If this is because of ignoring last file "\r\n" , what is the alternative?





    Friday, January 31, 2014 6:41 PM

Answers

  • You are probably missing the row terminator on the last line of the file.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by DOTNETDEV1983 Monday, February 03, 2014 4:22 AM
    Friday, January 31, 2014 10:02 PM
  • As David already wrote; open the source file in an (hex) editor and check if all line are correctly terminated with carriage return + line feed

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by DOTNETDEV1983 Monday, February 03, 2014 4:22 AM
    Sunday, February 02, 2014 8:12 AM

All replies

  • Can anybody help please?
    Friday, January 31, 2014 6:52 PM
  • You are probably missing the row terminator on the last line of the file.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by DOTNETDEV1983 Monday, February 03, 2014 4:22 AM
    Friday, January 31, 2014 10:02 PM
  • As David already wrote; open the source file in an (hex) editor and check if all line are correctly terminated with carriage return + line feed

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by DOTNETDEV1983 Monday, February 03, 2014 4:22 AM
    Sunday, February 02, 2014 8:12 AM