This is a very common request recently – 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 in quick steps.
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.
Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
Create CSV file in drive C: with name sweetest. text with the following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
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 CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
839 Comments. Leave new
good one……..
how to store text file in oracle 8i………..
You should post this question at ORACLE forums such as http://www.orafaq.com
Thanks buddy :-)
Thanku very much
BULK
INSERT testing_table
FROM ‘c:\testing.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
Above code works.. and this is exactly what i was searching for…
hi!
its really work..thank so much
if you dont mind I would like to ask, how to filter certain data in the csv file during the insertion?
Tq.
How to bulk upload text file. That text file having fixed length format. I want to upload with length specification. in sql server 2005. Can any one help me?….
prabu
sir, currently i am developing a windows application where all the file of extension .xls (excel format) must be saved in to the database
i mean to say the content MUST SAVED INTO DATABASE
and condition is that the content is not maintained as row or column
PLS REPLY
THANKS
HASMUKH JAIN
Hello. It solved my problem too. But I have an another problem.
It converts the characters “ş,Ş,ç,Ç,ö,Ö,ü,Ü,ğ,Ğ,ı,İ” to unreadable characters. How can I solve it?
hI,
Im seriously in need of help, ive looked into many options including dts and bcp but ive not had any joy
sorry, too quick on the submit button!
Im seriously in need of help, ive looked into many options including dts and bcp but ive not had any joy.
my txt file is formated in the following way
|fname1|,|26/02/03|,|lname1|
|fname2|,|26/02/03|,|lname2|
|fname3|,|27/02/03|,|lname3|
and so on…
Ive tried the following but the data is not being added correctly. I dont want to search and replace stuff in the file becuase i have 100’s of files with 10,000’s rows to deal with. Please help.
thanks.
D
BULK
INSERT tblTest
FROM ‘c:\Feb03Names.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘|\n’
)
The following is what gets stored in the db when running the above code.
ID Name RegDate Lname
1 fname1 , 26/02/03|,|lname1
2 fname2 , 26/02/03|,|lname2
3 fname3 , 27/02/03|,|lname3|
Thank you so much! Simple, yet elegant solution, just what I needed!!
BULK
INSERT CHUMMA.DBO.PRODUCTS
FROM ‘E:\INSERT.CSV’
WITH
(
FIELDTERMINATOR=’,’,
ROWTERMINATOR=’\n’
)
GO
When I run this …am getting error saying…
Msg 4832, Level 16, State 1, Line 189
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 189
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 189
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
hi this is sunil singh
i m college student
i have project, pls help me with this.
I want to import excel sheet with has three column,
First Name Last Name Date Of Birth
has 30 rows
and i have assigment
to convert all data in Sql data base file Name “Student Info”
with one single sql command.
pls reply mee
Hi Sunil,
Please find h ttp://www.sqlyoga.com/2009/12/sql-server-how-to-read-excel-file-by.html
Thanks,
Tejas
SQLYoga.com
company: xxxxxxxx
ssno: xxx-xx-xxxx
identification no:xxxx
1 fname1 , 26/02/03|,|lname1
2 fname2 , 26/02/03|,|lname2
3 fname3 , 27/02/03|,|lname3
———————————————————————-
Above is the file format in txt. I simply need to grab in row per record like below:
company,ssno,fname1,date,lname1
company,ssno,fname2,date,lname2 and so on….
How can I parse and import the data from .txt to sql 2005. Please help and reply me at forent@hotmail.com
Thanks.
Miki
@miki
what have you done so far?
Hi friends,
I want to do Bulk Insert in my table. The actual senario is , I have written a stored procedure which does some calculation and Insert the row in my table.
Stored procedure takes one second to do its calculation and insert operation.
I have around One crores of records to be inserted in my table.
If i do use of above store procedure it will take , 1 sec * No of Rows(1 crore) to be inserted.
So how can i go with Batch insert to achieve this.
Please help.
Thanks,
Sharan
Hello Sharan,
If possible, try to implement the calculation on the whole source rowset instead of processing the rows one by one. SQL Server engine is optimzed to perform SET based.
Kind Regards,
Pinal Dave
Dear i have a project result4u.com in that i add many school and university i want give a penal to each user by that they can add result by excel file. just like 10th class or 12th class or B.A Iyear,or M.Com…….many…. in that i want to upload excel file in sql dynamically if i upload a excel file then that file save in sql and create table in sql dynamically….bcos different-2 excel upload hogi in that all details will be there like Roll no, name, address, subject,hindi ,english…..
so plz help me how it come possible excel file save directly in sql dynamically create table…
I’m trying to do an import from a text file using the BULK INSERT. The text file is separated by fixed width (no delimiters).
Can anybody give me an example query for this?
Hi Mr. Pinal,
I have a flat ascii file with tabs to delimit all the columns, within each column is text or dates, text is delimited by the quotes as seen in sample A below. I can import into ms sql with bulk insert just fine, except I want to strip the quotes off so that the final row in the sql database looks like example B. Here is my code;
BULK INSERT dbo.NC_Voter_History
FROM “C:\Users\Kevin\Documents\NC Folder\NC_His\his1-50.txt”
WITH
(
FIELDTERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n’
)
GO
What can I add to strip off the “quotes” at bulk insert?
Thanks, Kevin
Sample A:
89 “TYRRELL” “000000000002” 23 2002-11-05 00:00:00 “11/05/2002” “11/05/2002 GENERAL ” “IN-PERSON ” “DEM” “DEMOCRATIC ” “14” “KILKENNY” “EE2035” 89 “TYRRELL”——- “14” “14”
Sample B:
89 TYRRELL 000000000002 23 2002-11-05 00:00:00 11/05/2002 11/05/2002 GENERAL IN-PERSON DEM DEMOCRATIC 14 KILKENNY EE2035 89 TYRRELL 14 14
Hi,
I get the error like this
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:\book1.txt” does not exist.
It ll be very helpfull if anyone can solve this problem.
Thanks in advance.
bari looks like windows security issue, sql user does not have access on file system on your local machine..