SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
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 )
Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.
Pinal has authored 14 SQL Server database books and 99 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.
Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.
Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).
Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.
Comprehensive Database Performance Health Check
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
SQL Server Performance Tuning Practical Workshop
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
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