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?

First I tried to follow Charles Okwuagwu's advice and create view with columns I want to insert but it wouldnt work. Than I realise that column order in view has to be the same as in the table. It works now. — VatresA, Apr 23 '19 at 13:09

Charles Okwuagwu · Answer 1 · 2019-11-14T22:59:51.473

11

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'

edited Nov 14 '19 at 22:59

answered Apr 17 '15 at 10:42

Charles Okwuagwu

10,538
16
87
157

I created a view with a single column but the single column got populated with all the columns from the file. What am I doing wrong? – CodingYoshi Nov 14 '19 at 21:50
@CodingYoshi added an example – Charles Okwuagwu Nov 14 '19 at 23:00
Thanks for the reply but no luck. I tried `create table people (name varchar(20) not null, dob date null, sex char(1) null) create view vwNames as select name from people`. Then `bulk insert vwNames from 'names.txt' select name from vwNames` and it returns the full contents of the row. I have SQL Server 2012. – CodingYoshi Nov 14 '19 at 23:29
the idea is you have fewer columns in your source file than the table, you make a view that has the exact same number of columns as the file to be imported. – Charles Okwuagwu Nov 15 '19 at 01:12
1

I see. However, in the question the OP said they wanted to leave out one of the columns in the text file (column 3 to be exact). Also, in your example you said `If you are importing only name` meaning leaving out some of the columns. This doesn't work if you have less columns in the table than the file. Thanks anyways. – CodingYoshi Nov 15 '19 at 01:25

score 10 · Accepted Answer · answered Feb 05 '13 at 16:19

10

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. ;)

answered Feb 05 '13 at 16:19

Janine Rawnsley

1,240
2
10
20

I'm using SQL Server 2008. – wootscootinboogie Feb 05 '13 at 16:24
The cheekier way is what I would suggest. – Eric J. Price Feb 05 '13 at 17:04
You can use format files in all versions of sql server, I just linked you the first things I found on google. I've used format files with SQL2000 to import product data and dropped about half the columns. I can find you some more specific examples if you want, or use your own google-fu. – Janine Rawnsley Feb 05 '13 at 17:16

score 5 · Answer 3 · edited Nov 13 '13 at 09:12

5

you cant ignore a field while doing bulk insert , insted of doing that .. Load all 4 column and drop the colum which you dont want

create table test (field1 varchar(50),field2 varchar(50), field3 varchar(50),field4 varchar(50))
go
bulk insert test
from 'c:\myFilePath'
with 
(fieldterminator=',',
rowterminator='\n'
)

ALTER TABLE test DROP column [field3]

edited Nov 13 '13 at 09:12

Youbaraj Sharma

1,295
3
17
34

answered May 24 '13 at 18:22

user2418601

51
1
1

score 3 · Answer 4 · edited Jun 19 '23 at 21:21

You can create a temporary table and insert any data there. After that, you can do whatever you want with it.

CREATE TABLE #TmpTable(
    [CategoryId] [int] IDENTITY(1,1) NOT NULL,
    [ParentCategoryId] [int] NULL,
    [CategoryTypeId] [int] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Code] [varchar](255) NOT NULL,
    [AlternateName] [varchar](900) NOT NULL
)

BULK INSERT [dbo].[#TmpTable]
FROM 'C:\tmp\Categories.csv'
WITH
(
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '0x0a'   --Use to shift the control to next row         
)

-- original table insert
INSERT INTO [dbo].[Categories]
( 
    CategoryId,
    ParentCategoryId,
    CategoryTypeId,
    Name,
    Code,
    AlternateName,
    IsDeleted,
    SystemCreated
)
SELECT 
    CategoryId,
    ParentCategoryId,
    CategoryTypeId,
    Name,
    Code,
    AlternateName,
    0,           -- custom value missed by file
    GETDATE()    -- custom value missed by file
FROM #TmpTable

-- remove tmp table
DROP TABLE #TmpTable

score -1 · Answer 5 · edited Jul 01 '23 at 11:53

You can indeed ignore certain data fields - see this:

Use a format file like here (Example):

Essentially you map the unwanted source data field to column 0 in your table. Yes FMT files are wierd see this Stack Overflow article The fmt file maps data fields to columns by ordinal. column 1 is the source data field order column 6 is target table column ordinal. These are 1 based numbered, and 0 means map to no column.

Ignore certain columns when using BULK INSERT

5 Answers5

Linked