Ignore certain columns when using BULK INSERT

I have a comma delimited text file with the structure

field1   field2   field3   field4
1        2        3        4

I wrote the following script to bulk insert the text file, but I wanted to leave out column 3

create table test (field1 varchar(50),field2 varchar(50),field4 varchar(50))
bulk insert test
from 'c:\myFilePath'

The insert worked fine, but the results of the insert made field4 look like field3,field4, so the field 3 was actually just concatenated onto field4. The flat files I'm working with are several gigs and can't be easily modified. Is there a way to use bulk insert but have it ignore the columns that aren't declared in the create table statement?


  • You can use a format file to do this:



    Or if you want a slightly cheekier way, just import it all and drop a column afterwards. ;)