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 in detail with different examples

Suppose we have to import following file to our database

productid,"product","manufacturers"
1001,"TV","Sony, LG, Panasonic, Toshiba"
1002,"Watch","HMT, Titan, Sonata"
1003,"Computer","Dell, HCL, Sony, HP"

As we can see manufacturers column have comma separated manufacturer names, so lets create a table to hold these data say products

CREATE TABLE [Products](
[productId] INT NOT NULL,
[product] [varchar](50) NULL,
[manufacturers] [varchar](200) NULL
)

Now time to create a format file, we can manually create it in notepad but luckily we already have a utility so why cannot we use this. Open command prompt and type following lines

// If Database supports windows wuthentication
bcp dbname.schema.Products format -c -t -f d:\test.fmt -T

// For Sql Authentication use id and password with -U and -P in place of -T
bcp dbname.schema.Products format -c -t -f d:\test.fmt -Uusername -Ppassword

Open your D folder, you will see file test.fmt, open it in notepad, it will look something like this

10.0
3
1   SQLCHAR   0  12   ""       1     productId       ""
2   SQLCHAR   0  50   ""       2     product         SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0  200  "\r\n"   3     manufacturers   SQL_Latin1_General_CP1_CI_AS

Let's analyze this format file

  1. 10.0 SQL Server Version
  2. 3 in next line indicate the number of columns
  3. First column show the sequence
  4. Then DataType
  5. Minimum Length
  6. Maximum length
  7. Delimiter
  8. Table column number
  9. Table column name
  10. and finally indicate the database is case sensitive or not.

Database Version

  1. 8.0 for SQL Server 2000
  2. 9.0 for SQL Server 2005
  3. 10.0 for SQL Server 2008 (which is I am using that's why it is 10.0)
  4. 11.0 for SQL Server 2012

To know more about bcp Utility read this

Now we know everything about the format file structure so let's modify it according to our CSV/TXT data file

  1. Check first two value 1001,"TV........
  2. Second ends with "TV","Sony, LG, Panasonic......
  3. And the third ends with " and enter so "\"\r\t"

I used scape sequence for " in format file, so put all above highlighted values as delimiter in our format file so it will become to this

10.0
3
1   SQLCHAR   0  12   ",\""       1     productId       ""
2   SQLCHAR   0  50   "\",\""     2     product         SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0  200  "\"\r\n"    3     manufacturers   SQL_Latin1_General_CP1_CI_AS

We are all set, so time to write code to insert data into database

BULK INSERT [Products]
FROM 'D:\Ali\products.csv' 
WITH 
(
  FIRSTROW = 2,
  FORMATFILE='D:\Ali\test.fmt'
);

Note: I used FIRSTROW = 2 because first row is column heading in our CSV file, to see more detail first row and other bulk insert settings my other post Bulk insert into SQL Server from CSV file by skipping first row

It's complete, so let's see another example with different text qualifier in csv file

productid,~product"~,~manufacturers~
1001,~TV~,~Sony, LG, Panasonic, Toshiba~
1002,~Watch~,~HMT, Titan, Sonata~
1003,~Computer~,~Dell, HCL, Sony, HP~

In this file we used ~ to indicate the string values, so our format file will be

10.0
3
1   SQLCHAR   0  12   ",~"       1     productId       ""
2   SQLCHAR   0  50   "~,~"      2     product         SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0  200  "~\r\n"    3     manufacturers   SQL_Latin1_General_CP1_CI_AS

Isn't it quite easy, we don't need any scape sequence as we see earlier. and the best thins that we don't want to do any change in our sql statement.

Ali Adravi Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
  • bulk-insert
  • sql server
  • csv
By Ali Adravi On 10 Jun, 13  Viewed: 20,575

Other blogs you may like

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?... By Ali Adravi   On 08 Feb 2013  Viewed: 17,173

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,311

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: 666

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,556

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: 579