Import CSV Data Into MsSql Server Using Bulk Insert Method

Import CSV Data or File Into MsSql Server Using Sql Bulk Insert Method in Asp.Net. Some time while developing applications using sql server we need to import or insert data into database from data files or csv files.

For this we can use bulk insert method to insert data from csv file to sql server .

1. create a csv file with some data in it like


amit,noida,26

sharad,noida,26

Shobhit,Delhi,25

Now run the following script to load data from this csv file into Database

--Temp table to store the data
CREATE TABLE Test
(
Name VARCHAR(50),
City VARCHAR(50),
Age INT
)
GO

--Bulk insert into Test
BULK INSERT Test
FROM 'C:\test.csv'
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR = '\n'
)
GO

--Get all data from Test
SELECT *
FROM Test
GO

--Drop the temp table
DROP TABLE Test
GO


Other SQL Server articles:
1.Import/Export Excel Data into Sql Server using SqlBulkCopy-ASP.NET
2.Install configure and troubleshooting sql server reporting services 2005
3.Ms sql server bulk insert method to import bulk csv data into database
4.Combine Multiple Records Comma Separated In One Column MSSQL
6.Disable browser back button functionality using javascript in ASP.NET
7.Method error 500 / 12031 in implementing ajax cascadingdropdown extender
8.Unable to attach binding handle invalid error in visual studio 2005 while debugging
9.The backup set holds a backup of a database other than the existing database-Sql Server Error 3154
If you like this post than join us or share

4 comments:

Anonymous said...

Can i also insert into a specific column?


Sandeep said...

Hi my first field in the Table is Auto incremented column with primary key so i m getting this error
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (LocationId).
What to do?


Anonymous said...

Hi friend,
iam placing my cvs file in my C:\
while using ur bulk insert code it bring the following error, pls give me some ideas
"Could not bulk insert because file 'c:\3103_AAA.csv' could not be opened. Operating system error code 21(The device is not ready.)."
Thanuks and regards
TKM Thanusree
thanusree@ncssoft.in


Anonymous said...

Is there anyway to do this with \t delimeter? I am trying but I keep getting error messages. Does tab delimeter not work with csvs?


Find More Articles