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
Hi,
I’m using SQL2005 Express and It seems that FILE_FORMAT
do not behave like “FieldTerminator” spec.
BULK INSERT [dbo].[DimCurrency]
FROM ‘D:\currencies.csv’
WITH (
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
=> Works fine
BULK INSERT [dbo].[DimCurrency]
FROM ‘D:\currencies.csv’
WITH (
FORMATFILE = ‘D:\Currency.fmt’
)
=> The bulk load failed. The column is too long in the data file for row 1, column 2.
Format file is produced thanks to bcp
9.0
3
1 SQLINT 0 4 “,” 1 CurrencyKey “”
2 SQLNCHAR 2 6 “,” 2 CurrencyAlternateKey SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 100 “\n” 3 CurrencyName SQL_Latin1_General_CP1_CI_AS
I’ve tried “\n”, \r\n” same issue..
Any ideas?
Thanks in advance!
hi this help me bulk insert query but i want same reverce how can i sql server database to cvs.txt file to using query
Read about bcp in SQL Server help file
Hi pinal,
I want to import Csv file into my Data base,
My file format is
2,Meggie,Smith,”19790122,19790122″
using the same code , “19790122,19790122” is getting error,
Please help me out..how to import data from csv file
Thanks a lot. It was quite helpful for us.
Hi Pinal,
I have one CSV file.I want to insert only first and last record into sql table using DTS.
I have created one DTS package and click on transformation.Set FirstRow and lastrow Property of OPTION tab and execute Package.It only insert first row into table.
Could you please let me know how to insert last row in to table.
Thanks,
Jeetesh
Thanks for your assistance. This helped a lot.
I have a question regarding the use of bulk insert to upload a file into a MySQL database. Although the target table ” test_table” exists and the table name is spelt correctly in the query, I keep on getting the error: “ORA-00903: invalid table name” . Can someone please help?
I use the following following query:
BULK INSERT test_table
FROM ‘C:Usersl_3.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘n’
)
;
BULK INSERT test_table
FROM ‘C:Usersl_3.csv’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘n’
)
;
hiiii,
I have txt file in D:\fd.txt and content of this file is like this
1,foo,5,20031101
3,blat,7,20031101
5,foobar,23,20031104
7,ankit,33,20031204
I want to this all data in select statement and sore in dataset and datatable.
like
select * from D:\fd.txt
i want this result in to dataset or datatable
what should i do???
In your front end, use file system object to interact with text file
This is nothing to do with usage of sql
Hi,
I need to bulk insert from text file, In My text file contains the first line is column names, I need the bulk skip the first line.
I tried the following script but it doesn’t work.
BULK INSERT member_registration FROM ‘D:\member_registration_2010_02_20.txt’ WITH (FIELDTERMINATOR = ‘|’, FIRSTROW=2, ROWTERMINATOR = ‘\n’)
if i have .csv file inplace of .txt then
Hello Mayur,
.csv file can be imported as .txt file. Let us know if you are facing any issue.
Regards,
Pinal Dave
Indeed. I used it for .DAT files that were | delimited. Worked beautifully!
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘n’
Is there a difference in the SQL sever processes Bulk insert and import data from SSMS?
Short & sweet. Got my task done.
Good Post.
Here is my Dillema –
I have a test file with this format:
John, La E*Associate Acc Exe**Inside Sales*
Poll, Pary (LAT)*VP, Prod, forms & Software Services*comapany LAT*LAT Executive*TE640
Kusu, Vas*Software Developer 3**LAT Technology*
how can I insert it to a sql 2008 table?
Very helpful indeed, Thanks alot.
Cheers
Dee
Can you please explain what the following does,
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
in the bulk insert.
??
Dee
It means from text file whenever there is a comma, treat it as feild terimator (seperate column), whenever there is newline (n) treat it as next line (next row)
How can I import csv files data using DTS, please assist me with the steps involved.
Thanks
Hello, could you please help me?
I am a new intern who was assigned a task of importing a flat file into server 2005 and then getting it to update daily. I have figured out the importing part, but I have no idea where to go from here. I am new to all of this and am just baffled. Basically I have these readings that get taken daily. they are stored in a flat file. I need the database to be able to go to that flat file and pull the new reading every day. How do I go about doing this correctly? I don’t know if I need to write a script or even how to do it. Please help!
How will you indentify new data?
Do you have date column as part of text data?
This code is help ful for me but i am having one more requirement with this
I have to pass the file name as parameter from front end
can an one give me the syntax for that
thanks in advance……..
I tried like the below code but it is showing the error as
“Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.”
ALTER PROCEDURE clientdata
@FileName varchar(30)
AS
BEGIN
BULK
INSERT usedoubleauthmode FROM @FileName
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
END
Use this
ALTER PROCEDURE clientdata
@FileName varchar(30)
AS
BEGIN
declare @sql varchar(1000)
set @sq='BULK INSERT usedoubleauthmode
FROM ”'+@FileName+”'
WITH ( FIELDTERMINATOR='';”, ROWTERMINATOR=''\n'')'
EXEC(@sql)
END