Sql-server – BULK INSERT Data from file but ignore the first occurence of field terminator

bulkcopyinsertsql server

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.

Best 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.

Related Question