Bulk insert into SQL Server from CSV file by skipping first row

Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma? How to load CSV file to SQL Server database? We will discuss here the same but with more detail, so let's start

Suppose we have a CSV file of car detail, which we want to import into our CarDetail table

2008,Acura,MDX,4dr SUV 4WD,70257
2010,Audi,A5,2dr Cabriolet,10150
2008,Audi,A8,L 4dr Sedan,900
2010,BMW,3-Series,4dr Sedan,2700
2010,BMW,X5-Series,4dr SUV,1700 

First of all we need a table CarDetail, so let's create it.

 Create Table CarDetail
(
  Year int,
  Make varchar(50),
  Model varchar(50),
  Body varchar(50),
  Odometer int
)

Before writing the code to insert record, one thing we need to remember, our CSV file should be on the server if we are using the drive name, as I am going to use. Now run the following script to insert records into our car detail table.

BULK INSERT CarDetail
FROM 'C:\CarDetailCSV.txt'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
GO

If there would be any error in any line that record will not be inserted but the rest will. Now you can check your car detail table

Select * From CarDetail

Now let's say your CSV file have header row as well and you don't want to insert the header, but you want to insert record from second row by skipping first row, see this file

Year,Make,Model,Body,Odometer
2008,Acura,MDX,4dr SUV 4WD,70257
2010,Audi,A5,2dr Cabriolet,10150
2008,Audi,A8,L 4dr Sedan,900
2010,BMW,3-Series,4dr Sedan,2700
2010,BMW,X5-Series,4dr SUV,1700

So how to skip first row, it's easy use FIRSTROW = n, where n is the row number from where you want to insert record, nth row will be treated as first row.

BULK INSERT CarDetail
FROM 'C:\CarDetailCSV.txt'
WITH
(
   FIRSTROW = 2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
GO

Suppose our CSV file is huge, which will take time to insert all records so I want to test with inserting from 5th to 50th record only, is that possibe? Yes, it is, in that case we need to use FIRSTROW =5 and LASTROW = 50 like this

BULK INSERT CarDetail
FROM 'C:\CarDetailCSV.txt'
WITH
(
   FIRSTROW = 5,
   LASTROW = 50,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
GO

Other blogs you may like

Bulk insert with text qualifier in sql server

Bulk insert with text qualifier from a text or csv file into sql server can be achieved by using a format file, in format file we can define the sequence of columns in source file (txt or csv file), data type, length, destination table column, version and number of columns, we will see everything... By Ali Adravi   On 10 Jun 2013  Viewed: 20,852

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,420

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 683

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,838

Error cannot be caught by a TRY…CATCH Construct in SQL Server

TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi   On 28 Dec 2012  Viewed: 589