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.