Load Comma Delimited file (csv) in SQL Server

We often need to import data that comes from different data sources, into a database like SQL Server. Usually data comes in the form of a comma delimited file aka CSV file.

Note: To handle complex scenarios and large files, use a utility like DTS/SSIS (SQL Server Integration Services). However for simpler scenarios, the approach shown in this article works fine.

These are the two easy ways to import data from a CSV file into a table of a SQL Server Database – Using Bulk Insert and Using SQL Server Management Studio.

Consider the following data

1,test,89300
2,testing,52801
3,test,1000

Create a file name test.csv in your system and add the data shown above in that file

Create a test table

CREATE TABLE test(
id int,
names varchar(100),
amount decimal(12,2)
)

Method 1: Using Bulk Insert

Here’s the same query for you to try out

bulk insert test from 'F:\test.csv'
with
(
fieldterminator=',',
rowterminator='\n'
)

The above code reads data from the file located at F:\text.csv and splits data into different
columns based on the fieldterminator ‘,’ (comma) and into different rows based on the rowterminator '\n' (\n is for newline).

Now if you do a SELECT * FROM test you will get the following output

Method 2: Using SQL Server Management Studio

Right click on your database > Tasks > Import data

Select the datasource as Flat file. Select the file using the browse button or type the file path and name directly and click Next

Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next

Select Destination as your server and select the database where the table exists. Click Next

The wizard will import the data and show you the details about the data which was imported

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan