Post reply

Bulk Insert with no last column

  • October 20, 2006 at 11:18 am

    #170841

     Hi all,  I have an issue where we want to bulk insert a series of files.  The fielse are from a 3rd party where fields are | separated and rows are newline separated.

      We are attempting to use bulk insert, but are running into a problem where the last row, last column of data has no data and does not end in a newline.  When this condition occurs, the row is ignored by bulk copy.  Example below, the last line is not inserted.

    Does anyone have a solution (other than to wrap it in a script to add a newline to the end of the file)?

    3528602|1000|AA1549|

    3529264|1000|RB0572|data might be here

    3529264|1001|RB0573|

     

     Thanks!

     Curt

  • October 20, 2006 at 11:32 am

    #667162

    Two options.

    1.As the vendow to pull "Blank or Null data" when the columns does not have values that way ur file definition is same.

    2.Load it into temp table that have one varchar column. Then parse it.

    Thanks

    Sreejith

  • October 20, 2006 at 1:44 pm

    #667195

    I don't see any issue here. BULK INSERT from files like you described work in my system for almost 2 years, no problems with missing data in last column.

    You must just make sure that last column is nullable.

    Problem may appear if your files look like this:

    3528602|1000|AA1549

    3529264|1000|RB0572|data might be here

    3529264|1001|RB0573

     

    _____________
    Code for TallyGenerator

  • October 20, 2006 at 2:01 pm

    #667201

    Hmm..  Do you have a newline at the end of the last line?  Columns are all nullable.  Our solution du jour is to tack a newline at the end which is working.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply