sqlsql-serversql-server-2008t-sqlbulkinsert

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))
go
bulk insert test
from 'c:\myFilePath'
with 
(fieldterminator=',',
rowterminator='\n'
)

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?


Solution

  • You can use a format file to do this:

    http://msdn.microsoft.com/en-gb/library/ms178129.aspx

    http://msdn.microsoft.com/en-gb/library/ms179250.aspx

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