January 19, 2011

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

bulk insert csv

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

image

Method 2: Using SQL Server Management Studio

Right click on your database > Tasks > Import data

ssms 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

ssms data source

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

csv rowcolumn delimiter

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

ssms destination

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

import export wizard


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

5 Responses to "Load Comma Delimited file (csv) in SQL Server"
  1. Anonymous said...
    January 19, 2011 at 4:13 AM

    very good article on bulk insert

  2. govind said...
    January 19, 2011 at 5:05 PM

    How do i use an ip address here? like

    172.18.253.12\f:\test.csv

    it is not working

  3. Madhivanan said...
    January 19, 2011 at 11:23 PM

    Govind,

    Try using this

    \\172.18.253.12\f$\test.csv

    Also make sure the drive f is shared drive

  4. govind said...
    January 21, 2011 at 1:25 AM

    omg this works! thank you very very much

  5. Madhivanan said...
    January 21, 2011 at 1:33 AM

    You are welcome govind. Thanks for the feedback

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions