Loading CSV Into SQL Server With BULK INSERT Correctly

BULK INSERT is the T-SQL way to read a file into a table, and it has been in SQL Server for a long time, which shows in its history: for years it had no idea what a quoted CSV field was. A file with "Smith, J" in it split into two fields at the comma, and everyone worked around it with format files or by choosing another delimiter. That changed with FORMAT = 'CSV', and the statement is now a decent loader. This article walks through a load that works, the options that decide whether it works on your file, the permission and path rules that trip people up and the errors, which are terse. It is part of the set of load guides in loading CSV into cloud warehouses, and the export side is in exporting SQL Server results to CSV with bcp and sqlcmd.

The statement

Here is a table and a file:

CREATE TABLE dbo.orders (
    order_id  int           NOT NULL PRIMARY KEY,
    customer  nvarchar(100) NOT NULL,
    zip       char(5)       NOT NULL,
    placed_on date          NOT NULL,
    total     decimal(10,2) NOT NULL,
    note      nvarchar(200) NULL
);

order_id,customer,zip,placed_on,total,note
1001,Acme Ltd,02134,2026-10-01,120.50,
1002,"Beta, Corp",90210,2026-10-01,89.90,"said ""hi"""

and the load:

BULK INSERT dbo.orders
FROM 'C:\data\orders.csv'
WITH (
    FORMAT       = 'CSV',
    FIRSTROW     = 2,
    FIELDQUOTE   = '"',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    CODEPAGE     = '65001',
    MAXERRORS    = 10,
    ERRORFILE    = 'C:\data\orders.err',
    TABLOCK
);

Line by line. FORMAT = 'CSV' selects RFC 4180 handling and is the line that fixes "Beta, Corp"; it needs SQL Server 2017 or later (and Azure SQL). FIRSTROW = 2 starts at the second line, which skips the header. FIELDQUOTE names the quote character, and the default for CSV format is the double quote, so this restates it. FIELDTERMINATOR is the comma. ROWTERMINATOR = '0x0a' says that lines end in a line feed; the default expects carriage return plus line feed, so a file written on Linux loads as one giant row without it, and the failure is an error about the data being too long for a column. CODEPAGE = '65001' reads the file as UTF-8 (supported since SQL Server 2016), and leaving it off reads the server's default code page, which turns accented letters into other characters without a complaint (converting a CSV from Latin 1 or Windows 1252 to UTF8 covers the opposite problem). MAXERRORS and ERRORFILE are the bad-row policy, below. TABLOCK takes a table lock, which allows a minimally logged load into an empty or heap table under the right recovery model and speeds up large loads, at the cost of blocking other writers.

The columns of the file are mapped to the table's columns by position, left to right. BULK INSERT does not read the header for names, so the file's columns must be in the table's order, with the same count, and an identity column or one the file lacks needs a view or a format file. If the file has fewer columns than the table, load through a view that exposes only the matching columns.

The path is the server's

The most common first failure has nothing to do with CSV. FROM 'C:\data\orders.csv' is read by the SQL Server process on the machine where the instance runs, not by your client. If you are connected from a laptop and the file is on the laptop, the server cannot see it, and the error says the file does not exist or you do not have access. The fixes are to put the file on the server or on a share the service account can read, as a UNC path (\\fileserver\loads\orders.csv), or to use a client-side tool such as bcp ... in, which reads from where you run it. On Azure SQL Database and Managed Instance, BULK INSERT reads from Azure Blob Storage through an external data source (DATA_SOURCE = 'MyBlob'), and local paths do not apply.

Permissions are the second wall. The login needs INSERT on the table and the server-level ADMINISTER BULK OPERATIONS permission (database-level in Azure SQL). The Windows account running the SQL Server service, not your login, needs read access to the file. A load that works for an administrator and fails for the application's login is nearly always one of these two, and the wrong fix is to give the application account a broad role. Grant the narrow permission, and put loads in a stored procedure with EXECUTE AS if the caller should not have it directly.

Errors, in plain words

The messages are brief, and they map to a short list of causes.

• Cannot bulk load because the file could not be opened (error 4860): the path is wrong, the file is on the wrong machine or the service account lacks access.

• The bulk load failed. The column is too long in the data file (error 4866): the terminators do not match the file. An LF file with the default CRLF row terminator, a header read as data or a quote problem makes SQL Server read far past the end of a field. Check ROWTERMINATOR first.

• Bulk load data conversion error (type mismatch or invalid character for the specified codepage) (error 4864) for row 3, column 5: a value does not convert to the column's type, for example eighty for a decimal or a malformed date. The message names the row and the column, which is more useful than most.

• Bulk load data conversion error (truncation) (error 4863): a value is longer than its column.

• An unexpected end of file was encountered: the last line is incomplete, or an unterminated quote swallowed the end of the file.

MAXERRORS sets how many bad rows are tolerated before the load fails, ten by default. ERRORFILE names a file to receive the rejected rows, plus a companion .Error.Txt with the reasons. Be aware that some failures, especially type conversion on the whole batch, abort the batch rather than reject a row, depending on the options, so do not count on MAXERRORS to make a messy file load.

Load into text first

The most readable way to deal with a dirty file is to avoid asking BULK INSERT to convert anything. Create a staging table of nvarchar columns, load the file into it, and move the rows into the typed table with a query that you control:

CREATE TABLE dbo.orders_stage (
    order_id nvarchar(50), customer nvarchar(200), zip nvarchar(20),
    placed_on nvarchar(50), total nvarchar(50), note nvarchar(500)
);

BULK INSERT dbo.orders_stage FROM 'C:\data\orders.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001', ROWTERMINATOR = '0x0a');

INSERT INTO dbo.orders (order_id, customer, zip, placed_on, total, note)
SELECT TRY_CAST(order_id AS int), customer, zip,
       TRY_CAST(placed_on AS date), TRY_CAST(total AS decimal(10,2)), NULLIF(note, '')
FROM dbo.orders_stage
WHERE TRY_CAST(order_id AS int) IS NOT NULL AND TRY_CAST(total AS decimal(10,2)) IS NOT NULL;

SELECT * FROM dbo.orders_stage
WHERE TRY_CAST(order_id AS int) IS NULL OR TRY_CAST(total AS decimal(10,2)) IS NULL;

TRY_CAST returns NULL instead of raising, so the same predicate that filters the insert also finds the rejects in the last query, which you can write to a table. The cost is a second pass over the data, and the benefit is that the error is a row you can read. It is the staging-then-merge pattern in loading a CSV into a staging table, then merging, and it is the shape I would use for any feed with human-made data. For idempotency, add a ledger with the file's hash and wrap the insert in a transaction (idempotent CSV imports that are safe to run twice).

When bcp or OPENROWSET fits better

bcp ... in is the client-side loader, so it is the answer when the file is on your machine, and it handles very large files with batching (-b) and error files (-e) (the bcp article covers the export side, and classic bcp has no mode for quoted CSV fields, so a file with quoted commas needs a format file or BULK INSERT with FORMAT = 'CSV'; check your version's documentation). OPENROWSET(BULK ..., FORMAT = 'CSV', FIRSTROW = 2) reads a file as a rowset you can SELECT from, join and filter before inserting, which is a neat way to load only some rows or to transform on the way in, at the price of needing a format file or column definitions with a WITH clause.

If the load is a recurring job, the ledger, validation, archiving and alerting of automating CSV imports into a database apply here as anywhere, and SSIS or Azure Data Factory are the heavier tools for it. Microsoft's reference for every option is the BULK INSERT page, and the CSV-specific behavior is described on its page for importing CSV files.