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.