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, it’s work for me.
thank you
Hi Pinal Dave.
I have a problem with the BULK,
When trying to run the sentence from VB6:
BULK INSERT ACCOUNTS FROM ‘\ \ Pruebas6 \ bdsistema \ Plano Homologacion.TXT’ WITH (FIELDTERMINATOR = ‘,’)
shows the following error:
Can not bulk load Because the file “\ \ development3 \ Users \ juan_garcia \ Desktop \ TIMES ALEJO \ Plano Homologacion1.txt” could not be opened. Operating system error code 67 (failed to retrieve text for this error. Reason: 15105).
please help me!
the error is the same but with the path “\ \ Pruebas6 \ bdsistema \ Plano Homologacion.TXT”
Hi, I want to make some programme convertion from ms.excel to sql server 2008. could you help me..? I’m beginner in programming.
Thanks a lot for help..
Hi All
You can write the following:
SELECT * INTO #TEMP1
FROM OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′,’EXCEL 8.0;DATABASE=D:\.XLS’,’SELECT * FROM [SHEET1$]’)
select * from #TEMP1
Can we read/bulk insert a flat file with inconsistent delimiters?
I have a .csv. I have column that is being uploaded to an ‘ntext(null)’ field type in SQL Server 2008 (Express). Within this column there can be data with free text with embedded char(10) and char(13)’s. In the .csv these cells are protected with ” “. I BULK_INSERT the data and the column is not preserving the carriage returns or line feeds. Bummer.
God knows how but somehow in Excel on only a couple of cells I managed to get the cell to display the ascii line break (small box with ?) as well as actually visibly break at that point (as opposed to show it all on one line with the small boxes). When I upload these columns it is fine!
Any ideas on either why my upload is not preserving the line breaks and/or how to get all my cells in Excel for this column to display the line break and show the text breaking at that point?
Thank you for this excellent article.
hi pinaldave
i want some help from ur side.
how can i upload an image into sql server.pls provide the code for this one
thankyou
somu.N
Thanks Pinal Sir,
Hi,
I want to import data from text file.format of file is as follows:
date,id,unit_number,Quantity
05/17/2012,1,117,100
05/16/2012,2,118,100
I am using bulk insert do it.like this
BULK
INSERT vw_bulk_insert_test
FROM ‘\\server\c$\csvtext.txt’–\\server\SQLEXPRESS\csvtest.txt’
WITH
(FIRSTROW=2,
check_CONSTRAINTS,
FIELDTERMINATOR = ‘~’,
ROWTERMINATOR = ‘\n’
)
GO
But before insert I want to validate values of each column.Like if second row will have values of all fields except unit_number then it should throw an error that unit_number value is missing.And it should not insert ant data after that.
Please guide me on the same.
Thanks in advance.
You should first add the data to a staging table and valida from there and add only required data to target table
thanks for share!! :D
Many Thanks for this. works just the ticket,
Hi,
first of all thanks for this post.
My requirement is my CSVs get imported into SQL databse automatically.
e.g:I have a folder where 10 CSvs resides, now these CSVs get import into SQL till the last CSV.
Please let me know is this possible or not?
rly gr8,,thanks…can i insert any document with the help of this code
Mable,
Yes it is possible; I’ve done it a few times. If the 10 csvs are always the same name and they are all in the same format and they all go into a single table then it is relatively easy. There are ways to interrogate the file system and you can create scheduled jobs to run stored procedures to automate the process. I can’t help you much more without more information about the files and the target tables.
while running this query i am getting this error “Cannot bulk load. The file “D:\csvtest.csv” does not exist.”.
thanks
Nice posting Pinal. I have tried it on 25 May 2012, and the example still working.
Hi abc,
You might be using the wrong address.Please check again and respond
How to populate oracle table with csv file?