Thursday, December 13, 2012

SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server.


These are the common questions, How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? 
Let us see the solution here
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

1) Create a Table using the below script: (To Download Please Click Here)

USE [Experimental]
GO
/****** Object:  Table [dbo].[CSVTest]    Script Date: 12/13/2012 16:08:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CSVTest](
[ID] [int] NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BirthDate] [smalldatetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF





2) Create CSV file in drive e: with name csvtest.txt with following content. The location of the file is E:\csvtest.txt (To Download Please Click Here)
1, Anup, Shah, 19880803
2, Mac, Macwan, 19880803
3, Ankit, Patel, 19880803
4, Devang, Shah, 19880803

3) Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted. 
BULK
INSERT CSVTestFROM 'E:\csvtest.txt' WITH
(
FIELDTERMINATOR ',',

ROWTERMINATOR '\n')
GO
--Check the content of the table.
SELECT FROM CSVTest
GO









No comments:

Post a Comment