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 Pinal Dave, I got a problem to import data to new table in sql server,
first of all I get data files(like .csv[diff formats], .xls, .xlsx, .mdb, .accdb, .dbf, .dat, .txt etc) and I need to convert any type to .csv and then import it to server do you have any idea about it
Hi Pavan,
or manual import, If you are using Windows Application you can set property for file type or for automatic import you can use classes to identify and process different type.
CREATE TABLE List_encrypted
( ID int not null identity(1, 1) primary key,
LName varchar(255),
FName varchar(255),
email varchar(255),
projectName varchar(255));
BULK INSERT [List_encrypted]
FROM ‘C:\filename.txt’
WITH
( FIELDTERMINATOR = ‘” ; “‘,
ROWTERMINATOR = ‘\r\n’)
is there a way i can assign static value to ProjectName for example projectName = “ABC”
I have a csv file as below:
2015-11-18 00:00:00.000,2092,2441,64.00000000,WM2165,Neighborhood Mkt 2165 Slocomb, AL,4-8201,735216966092,005619597,1
2015-11-18 00:00:00.000,285,4540,8.00000000,WM0977,Wal-Mart 977 of Fernandina Beach, FL,4-1452,735216307826,554418906,1
2015-11-18 00:00:00.000,342,4540,8.00000000,WM1297,Wal-Mart 1297 of Chiefland, FL,4-1452,735216307826,554418906,1
2015-11-18 00:00:00.000,334,4551,60.00000000,WM1223,Wal-Mart 1223 of Tallahassee, FL,4-4019,735216307932,554418920,1
2015-11-18 00:00:00.000,380,4551,60.00000000,WM2920,Wal-Mart 2920 of Orange Park, FL,4-4019,735216307932,554418920,1
2015-11-18 00:00:00.000,230,4552,12.00000000,WM0604,Wal-Mart 604 of Dothan, AL,4-4221,735216307949,554418910,1
2015-11-18 00:00:00.000,358,2441,64.00000000,WM1638,Wal-Mart 1638 of Eufaula, AL,4-8201,735216966092,005619597,1
2015-06-17 00:00:00.000,242,2494,5.00000000,WM0712,Wal-Mart 712 of Scottsboro, AL,4-5423,681131070560,552035859,3
2015-06-17 00:00:00.000,242,1966,8.00000000,WM0712,Wal-Mart 712 of Scottsboro, AL,4-1407-F1,735216969239,005660571,3
2015-06-17 00:00:00.000,242,3082,9.00000000,WM0712,Wal-Mart 712 of Scottsboro, AL,4-4335,735216972239,553263178,3
2015-06-17 00:00:00.000,242,1992,6.00000000,WM0712,Wal-Mart 712 of Scottsboro, AL,4-2034,735216966658,550830643,3
2015-06-17 00:00:00.000,242,1985,6.00000000,WM0712,Wal-Mart 712 of Scottsboro, AL,4-2012,735216201988,005664982,3
2015-06-17 00:00:00.000,242,2476,6.00000000,WM0712,Wal-Mart 712 of Scottsboro, AL,4-2610-F1,735216202398,005664863,3
2015-06-17 00:00:00.000,242,2105,24.00000000,WM0712,Wal-Mart 712 of Scottsboro, AL,4-4021,735216969420,005651198,3
2015-11-05 00:00:00.000,2063,1996,6.00000000,WM7280,Wal-Mart 7280 of CULLMAN, AL,4-2204,735216202121,005624598,4
2015-11-05 00:00:00.000,2063,2330,12.00000000,WM7280,Wal-Mart 7280 of CULLMAN, AL,4-2036-SB,735216300193,550415804,4
2015-11-05 00:00:00.000,2063,4541,72.00000000,WM7280,Wal-Mart 7280 of CULLMAN, AL,4-1453,735216307833,554418922,4
2015-11-12 00:00:00.000,1067,2938,40.00000000,WM0848,Wal-Mart 848 of Southaven, MS,4-3425,735216969987,552874332,4
2015-11-12 00:00:00.000,1067,2480,12.00000000,WM0848,Wal-Mart 848 of Southaven, MS,4-2703,735216216999,551988047,4
2015-11-12 00:00:00.000,1067,4550,60.00000000,WM0848,Wal-Mart 848 of Southaven, MS,4-4018,735216307925,554418908,4
2015-11-12 00:00:00.000,1067,4533,24.00000000,WM0848,Wal-Mart 848 of Southaven, MS,4-3430,735216980500,554418884,4
2015-11-12 00:00:00.000,1067,4539,48.00000000,WM0848,Wal-Mart 848 of Southaven, MS,4-3452,735216980562,554418894,4
when i try to insert this using above query error comes that
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Id).
id has type datetime.
I used smalldatetime also but same error comes.
Thanks.
BULK INSERT YourTable
FROM ‘D:LoadFromFolderNameSourceFile.txt’
The source File is Tab Delimited. Try this and would work…
I need to in import a csv file to a existed database table everyday. From the first time, I’m using your suggested way is good, however, when I need to import another csv file into a same table, I have a question, how can I avoid to insert the duplicate content?? If the csv file contains data which I imported previously, how can I insert the latest data from this new csv file and delete the previous?
You can create another table. The content of your .CSV file will be imported to an AUXTABLE, then you set up a job that will run daily and will call a stored procedure.
The stored procedure will make an INSERT INTO ‘YOURTABLE’ SELECT (VALUES) FROM AUXTABLE.
Your AUXTABLE should also be dropped after every import to guarantee that it wont import any duplicade values.
…
Or putting it in a simple way:
1. Create a STORED PROCEDURE that will run a BULK INSERT from CSV to an AUXTABLE that it will create on spot.
2. Create a STORED PROCEDURE that will INSERT VALUES from AUXTABLE into YOURTABLE and then DROP AUXTABLE.
3. Set a JOB that will run STORED PROCEDURE 1 and then it will run STORED PROCEDURE 2.
I am getting a permission error. Can this be done on SQL express edition without SQL agent use?
Thank you Pinal, very useful query. I tried hard to do this via SSIS-Import but managed to do it with your query. Thanks again
Hi,
In my csv file, I have a line starts with 0 like below:
Identifier,Register Time
0010000145,”2016-01-04T12:15:30″
When I import this file into sql server, Identifier column is 10000145. How can i import this line like 0010000145.
Sorry about my poor English.
no need anymore. thanks :)
Hi Pinal,
Just wanted to ask using sql script – an help me or suggest code that’ll work on how to upload multiple CSV fils using temporary tables? Thanks, yes, this is VB.net + SQL for database.
Looking forward on your immediate assistance, just need it badly for our thesis. Thanks!
i have one question after bulk inserting the all rows and i need to delete the csv file. Once all row inserted means delete,otherwise it will not delete csv . Please give some examples
hi , i want to insert rows from a CSV file to sql table. But in my destination table, the number of columns is greater than the source file. Say Sql table have 40 Columns and CSV file contains 19 Columns. Can you help me in this.
How to read csv file in sql server
wrong path!
Really love your Page here :)
;) Thanks
Hi Pinal,
I have same requirement, but instead of local repository, file is on FTP location. So how to directly get data from of CSV file from FTP location?
Hi Sachin,
You can use a batch file to move data from FTP to local and from there, the above procedure.
Great suggestion Viral.
I have a CSV file that has text fields surrounded by double quotes. I know this is an old problem, but do you have a SIMPLE solution that uses BULK INSERT? I’m trying a format file but am getting weird results.
Thanks in advance,
Above is not working?
Use
FIELDTERMINATOR = ‘”,”‘
and then run the replace command on the first and last columns to remove the double quotes.
update set = REPLACE(,'”‘,”)
I saw Mani’s response, but unfortunately that approach would only work if *every* field was delimited by quotation marks as well as commas. Properly formatted CSV allows lines like this:
1, “Seymour, Barry”, Los Angeles, CA
I ended up rewriting a VB.NET program I called CSVtoTAB to use Excel to convert the CSV into tab-delimited text. It works a treat.