Ignore Certain Columns When Using Bulk Insert

Ignore certain columns when using BULK INSERT

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

Bulk Insert how to skip column using format file in SQL Server 2017

Can you try this format file: I think the new Format=CSV option does some thinking that the Format File no longer needs to do...

14.0
2
1       SQLCHAR             0       1       ","      2     Id           SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       50      "\r\n"   3     Name         SQL_Latin1_General_CP1_CI_AS

How do I use Bulk insert without inserting into the autonumber column?

I would -I always do- create a temporary (unindexed, no unique ID, no primary key, etc) table and bulk insert CSV there, then either move data to the target table or alter the table to make it look as I want.
The reason is, formatting can be complicated in some cases and it's just another source of error, easier to filter for issues like a field is NOT NULL but CSV has an error in it, etc. One also can trigger updating data if it's not an ASAP insert (e.g. indexing up the table can run overnight)
I had enough headaches with CSV import, so... in my experience the best solution is a temp table and ALTER it or move data. But it's your call of course :)

In SQL Server Management Studio, how do I ignore the first column in a table when bulk inserting from a csv fle?

Load them into a staging table which has 4 columns, and then create an sql to insert them into the final table specifying the 4 columns to insert to.

I don't know about the unique identify syntax you have above, but you have specified newid() works, so the following should be the complete answer

CREATE TABLE IA.CONTACTS
(
CONT_GUID UNIQUEIDENTIFIER  NOT NULL,
CONT_FNAME VARCHAR(100) NOT NULL,
CONT_LNAME VARCHAR(100) NOT NULL,
CONT_DOB DATE,
CONT_CALLS INTEGER
)

BULK INSERT 
IA.IA.Staging_Contacts  
   FROM 'C:\Users\k20\Desktop/IA.CONTACTS.csv'  
   WITH   
      (  
        FIRSTROW=2,
        KEEPIDENTITY,
         FIELDTERMINATOR =',',  
         ROWTERMINATOR ='\n'  
      );

insert into contacts (CONT_GUID,CONT_FNAME ,CONT_LNAME,CONT_DOB,CONT_CALLS )
select newid(),CONT_FNAME ,CONT_LNAME,CONT_DOB,CONT_CALLS 
from staging_contacts

Excluding Content From SQL Bulk Insert

The approach I generally use with BULK INSERT and irregular data is to push the incoming data into a temporary staging table with a single VARCHAR(MAX) column.

Once it's in there, I can use more flexible decision-making tools like SQL queries and string functions to decide which rows I want to select out of the staging table and bring into my main tables. This is also helpful because BULK INSERT can be maddeningly cryptic about the why and how of why it fails on a specific file.

The only other option I can think of is using pre-upload scripting to trim comments and other lines that don't fit your tabular criteria before you do your bulk insert.

---

Related Topics