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
Just what I was looking for. After I’ve added codepage=’raw’, check_constraints I got exactly what I wanted.
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “\D\raw.xls” could not be opened. Operating system error code 3(The system cannot find the path specified.).
Note that the file should exist in the Server’s directory. or use UNC path //system_name/folder
Doe not seem to work correctly with Unicode characters. I do not have much experience with Unicode. More googling…
With BULK INSERT within SQL 2014 is there not a switch or something that would allow the BULK INSERT to ignore commas within double quotes?
No, that is impossible in 2014
Handling of CSV become more usable starting with 2017 version.
That is funny, support for basic CSV format, was absent for decades in the flagship M$ **DATA** processing product SQL server :)
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 93
i get this error