Ignore certain columns when using BULK INSERT

Question

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?

Answer 1

The easiest way is to create a view that has just the columns you require.

Then bulk insert into that view.

Example:

create table people (name varchar(20) not null, dob date null, sex char(1) null)

--If you are importing only name from list of names in names.txt

create view vwNames as
select name from people

bulk insert 'names.txt'