BULK INSERT Data from file but ignore the first occurence of field terminator

Question

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.

score 1 · Answer 1 · answered May 16 '13 at 07:00

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.

BULK INSERT Data from file but ignore the first occurence of field terminator

1 Answers1