Click here to Skip to main content
15,860,861 members
Articles / Database Development / SQL Server / SQL Server 2014
Tip/Trick

Import CSV or txt File Into SQL Server Using Bulk Insert.

Rate me:
Please Sign up or sign in to vote.
4.93/5 (5 votes)
21 May 2014CPOL 160.2K   16   14
Import CSV or txt File Into SQL Server Using Bulk Insert.

Introduction

I am showing here How to import CSV or txt file into SQL Server Database Table. How to load comma delimited file into SQL Server.

Using the code

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values. and
if loading txt file then file should have Comma Delimited Values. and file should be like

Image 1

Here is the script to create table-:

SQL
CREATE TABLE Employee(
Id int,
Name VARCHAR(100),
Designation VARCHAR(100)
)

I Created a txt and a CSV file in location 'F:\\MyPublis\\ with the txt file name is TestToInsert.txt
Now run following script to load all the data from txt file to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
I created Id column as integer in the Emloyee table, if there is any row in my file having first part of the data as a string means that will go to Id column then it will not insert that row and will continew with next row.

SQL
BULK
INSERT Employee
FROM 'F:\\MyPublis\\TestToInsert.txt' --location with filename
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Now see data in table -:

SQL
SELECT *FROM Employee

Image 2

execute same script for the CSV file.

CSV file having the data -:

10, Siv_CSV, CEO
11, Brijendra_CSV, Operatore
12, Micro, Company

SQL
BULK 
INSERT Employee
FROM 'F:\\MyPublis\\CSVTest.csv' --location with filename
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
SELECT *FROM Employee

output will be-:

Image 3

History

Keep a going.....

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
QuestionError: Cannot bulk load because the file "C:\Document\test.txt" could not be open Pin
Member 1321728224-May-17 16:39
Member 1321728224-May-17 16:39 
GeneralI Vote 5 for this Article Pin
DivyaNaidu48616-Oct-14 20:08
DivyaNaidu48616-Oct-14 20:08 
GeneralRe: I Vote 5 for this Article Pin
Arvind Singh Baghel26-Oct-14 23:51
Arvind Singh Baghel26-Oct-14 23:51 
QuestionForum for tip/trick: Import CSV or txt File Into SQL Server Using Bulk Insert. Pin
deenasudhakar23-May-14 1:41
deenasudhakar23-May-14 1:41 
AnswerRe: Forum for tip/trick: Import CSV or txt File Into SQL Server Using Bulk Insert. Pin
Arvind Singh Baghel23-May-14 2:31
Arvind Singh Baghel23-May-14 2:31 
GeneralRe: Forum for tip/trick: Import CSV or txt File Into SQL Server Using Bulk Insert. Pin
deenasudhakar23-May-14 3:08
deenasudhakar23-May-14 3:08 
GeneralRe: Forum for tip/trick: Import CSV or txt File Into SQL Server Using Bulk Insert. Pin
Gary Henning27-May-14 5:08
Gary Henning27-May-14 5:08 
AnswerRe: Forum for tip/trick: Import CSV or txt File Into SQL Server Using Bulk Insert. Pin
usuariosapp10-May-18 17:51
usuariosapp10-May-18 17:51 
QuestionVery Nice Pin
dbrenth22-May-14 8:16
dbrenth22-May-14 8:16 
Questionnice article Pin
Darshan.Pa21-May-14 23:38
professionalDarshan.Pa21-May-14 23:38 
AnswerRe: nice article Pin
Arvind Singh Baghel26-Oct-14 23:52
Arvind Singh Baghel26-Oct-14 23:52 
GeneralNice article Pin
HardikPatel.SE21-May-14 22:10
professionalHardikPatel.SE21-May-14 22:10 
GeneralRe: Nice article Pin
Arvind Singh Baghel26-Oct-14 23:52
Arvind Singh Baghel26-Oct-14 23:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.