Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

FIELDTERMINATOR = '    '
ROWTERMINATOR = '\n'

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.

share|improve this question
    
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? –  RazorXsr 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
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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