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
The above works fine for fields with no commas. I have a CSV file that has a ‘name’ field enclosed in quotes in the format Lastname, Firstname. My BULK INSERT treats the commas as delimiters even though the field is enclosed in quotation marks. I’m using SQL Server 2008 and the research I’ve done online tells me that SQL Server does surprisingly poorly in importing such files.
For now our solution has been to save the file as tab-delimited text. At that point BULK INSERT delimits the fields properly but the quotation marks come in as well! We have to run a few REPLACE statements to eliminate those.
I have a local copy of SQL Server 02016 developer and it has similar problems.
I’ve also tried OPENROWSET – which works on my copy of SQL 2016 but not on SQL 2008 – I suspect some newer driver needs to be installed on the server for it to work.
Never faced/solved this. If you find any solution, please share.
I have found two ways to solve this. Unfortunately, neither uses SQL Server alone.
A solution I coded at my last job was to write a VB.NET procedure that opens the file as text and reads each line, looking for both commas and quotation marks. It replaces every comma with a tab, but ONLY if the comma is NOT inside a pair of quotation mark.
Another solution is use VB.NET and Excel.Interop to run Excel, load the CSV file and save as tab-delimited text. (Excel seems to understand this kind of file natively.) For now our solution is to have one of our programmers do this manually.
After the file is correctly converted to tab-delimited text with delimiters, I use a BULK INSERT statement with FIELDTERMINATOR = ‘t’ and the data imports correctly.
Will do.
I have a csv file that I need to upload to a site I have. I have to upload data daily to the server. Do you have any simpler way to do so? Thanks In advance
Gives an error for date column. Infact if you just copy “19750101” and try to insert in sql server smalldatetime it will give an error. what is the solution to insert this using import wizard? I even tried using other daetype columns but I still get an error for date column
You save my life! Thanks a lot
Hi,
In SQL server, how to load multiple csv files of a folder into the database table.
Please help me with this.
Thank you!
Hi Pinal,
How can I validate entered data (10 column) in one script?
Thanks
Hi Ji,
Can u help me to insert the csv to sql table only particular columns. Like i have 10 columns in my table but in csv i have only 6 column values.. how to do this through the script like above
I am also having an issue with Date when trying to insert the value ‘2017216’ into a column that is a data type of date.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (CheckDate).
here! i have an auto incremented ID which is not included in my csv .. how do i only inset the data and increment id as i insert data from csv file..
Dear sir
I have a .CSV file which is sent from a field device. but when it is written in sql server. My first row got missing.Below is my code.
BULK INSERT LDMS.dbo.tblData
FROM ‘C:\FTPsite\rwpctrl1\rwpctrl1.csv’
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
Thanks! Just what I needed.
Hi, can anyone help me please?
I have a column “YearsModel” with the value “2017-”
When importing the data of this table, to a CSV using a batch file… the result in csv is… “-2017”
Do i need to config something?
Thank you guys
good.
Thanks!
its not working to me !
Msg 4860, Level 16, State 1, Line 9
Cannot bulk load. The file “c:\csvtest.txt” does not exist.
Displaying this error message
The file should be on the SQL server machine not local machine. Can you please check?
Please note that the file path should be in Server’s system and not in your local system
Hi, I do not have access to the Server’s system, is there a way to upload file using T-sql or sqlcmd to upload the csv file to the server? Thanks for the help
Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. When using bulk? I Am using SQL2008R2 . I was wondering what the reason or if it’s the way I am performing the bulk load?
‘bulk insert [Staging].[‘+@xml_name+’] from ”’ +@path +”’ with (firstrow=2,
CODEPAGE = ”1252”,
FIELDTERMINATOR = ””|””,
ROWTERMINATOR=””\n””,
MAXERRORS = 0,
CHECK_CONSTRAINTS)’
This does work on smaller files with no problems but this one is a slightly hefty 15GB csv.
Cannot bulk load. The file “C:TempCalendar.csv” does not exist.
My text file has diffentents numbers of columns and the and each column is as “a” “b” “c”, also including field names as a header.
Thank you
Is there a max. or performance risks in terms of the file size to be loaded using this method? Let’s say above 1GB..?
Memory related issue may arise
I’m getting an error while running this on a server. Is there a way to get the local machine path in a way such that its picked up by SQL Server?
The PATH needs to be accessible to the user account who’s credentials are running SQL Server.