I have a file that has data arranged in the following style:

    1    1    0.5102    0.4898    0.0000    0.0000    0.0000
    1    2    0.3114    0.6886    0.0000    0.0000    0.0000

I need to load this into a table on MS-SQL and process it. I am using the BULK INSERT statement with the following settings


But what happens is that I end up with a table having this content (NOTE: I made all columns VARCHAR to see how the data is getting imported):

COL1    COL2    COL3    COL3    COL5    COL6    COL7
NULL    1       1       0.5102  0.4898  0.0000  0.0000    0.0000
NULL    1       2       0.3114  0.6886  0.0000  0.0000    0.0000

Essentially, there is a ' '(four spaces) before the first column of data. But BULK IMPORT assumes that the first column is NULL and offsets the data by a column.

Now, my question - is it possible to obviate this issue? Can we instruct BULK INSERT to ignore the first occurrence of the FIELD TERMINATOR? I cannot change the input file as it is used by some other parts of the process.

  • Did you try adding the spaces to the end of the row terminator? (If this works, make sure you still get the last row.) – Jon Seigel May 15 '13 at 16:39
  • Nice trick. But now all rows of data except the first row looks good... Any way to resolve that? – sriramn May 15 '13 at 17:12
  • Ahhh, the files don't have a header row? (I may have misread.) – Jon Seigel May 15 '13 at 17:31
  • 1
    See this answer -- I believe this can be done using a format file. – Jon Seigel May 15 '13 at 17:38

If the amount of data is small enough or you can otherwise afford to spend a bit more resources on the operation, there are ways around this. Most of the scenarios where I've done bulk inserts required additional data manipulation in any case, so I usually just insert the bulk data to a specially created bulk insert table, and then manipulate and transfer the data to the real table from there. Makes tasks such as this one a lot easier. But of course there's an increased overhead from handling the same data twice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.