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

SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server csv1

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

SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server csv2

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Simple Example of WHILE Loop with BREAK and CONTINUE
Next Post
SQL SERVER – Sharpen Your Basic SQL Server Skills – Database backup demystified

Related Posts

839 Comments. Leave new

  • Is there a max. or performance risks in terms of the file size to be loaded using this method? Let’s say above 1GB..?

    Reply
  • 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?

    Reply
  • Just what I was looking for. After I’ve added codepage=’raw’, check_constraints I got exactly what I wanted.

    Reply
  • 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.).

    Reply
  • Doe not seem to work correctly with Unicode characters. I do not have much experience with Unicode. More googling…

    Reply
  • Todd Thomasson
    August 5, 2019 10:25 pm

    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?

    Reply
    • 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 :)

      Reply
  • Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 93
    i get this error

    Reply

Leave a Reply

Menu