Importing CSV File into SQL Server: SQL in Sixty Seconds #018

Importing CSV file data into SQL Server means matching the file to a table, loading it and checking the result.

SQL in Sixty Seconds #018 introduces that workflow. The 2012 recording uses BULK INSERT. The examples below use the CSV support added in SQL Server 2017, and I ran them on SQL Server 2025.

Watch the Original CSV Import Episode

Start with a Small File

Save the following text as C:\Import\items.csv in UTF-8 with LF line endings. Notepad shows both settings at the bottom right. The first row names the columns. The file holds a quoted comma, an accented letter and one bad quantity, all on purpose.

ItemId,Description,Quantity
1,"Notebook, blue",4
2,Pencil,12
3,Müsli bar,many
4,Eraser,7

The path must be readable by the SQL Server service on the server machine. A path on your laptop is not visible to a remote server. The service account also needs permission to read the folder.

Load into a Typed Table

A temporary table lets you inspect the result without touching an application table. The columns use the types you want in the end.

CREATE TABLE #CsvItems (ItemId int NOT NULL, Description nvarchar(100) NOT NULL, Quantity int NOT NULL);
BULK INSERT #CsvItems FROM 'C:\Import\items.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001', ROWTERMINATOR = '0x0a');
SELECT ItemId, Description, Quantity FROM #CsvItems ORDER BY ItemId;
DROP TABLE #CsvItems;

SQL Server printed Msg 4864 for row 4, column 3, which is the word many in Quantity. It skipped that row and loaded the other three. A TRY and CATCH block around the statement did not catch the message. So a load can finish with a printed error and a short table. BULK INSERT keeps going until it reaches its error limit, which is 10 by default. Compare the row count with the number of data lines in the file.

Load as Text, Then Convert

A safer pattern loads everything as text and converts afterwards. Nothing is skipped, and you can see every bad row. TRY_CONVERT returns NULL instead of an error when a value does not fit.

CREATE TABLE #Staging (ItemId nvarchar(20), Description nvarchar(100), Quantity nvarchar(20));
BULK INSERT #Staging FROM 'C:\Import\items.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001', ROWTERMINATOR = '0x0a');
SELECT ItemId, Description, Quantity FROM #Staging
WHERE TRY_CONVERT(int, ItemId) IS NULL OR TRY_CONVERT(int, Quantity) IS NULL;
SELECT TRY_CONVERT(int, ItemId) AS ItemId, Description, TRY_CONVERT(int, Quantity) AS Quantity
FROM #Staging
WHERE TRY_CONVERT(int, ItemId) IS NOT NULL AND TRY_CONVERT(int, Quantity) IS NOT NULL;
DROP TABLE #Staging;

All four rows arrived, so the staging count matched the four data lines in the file. The comma inside Notebook, blue stayed in one value. The first query isolated row 3, the one with the quantity many. The second returned the three good rows as real integers. Send the rejected row back to whoever owns the file. Do not drop it without a note. In a real load, I keep the staging table until the owner confirms the fix.

Match the Encoding

The file is UTF-8, so the load needs CODEPAGE = '65001'. I loaded the same file twice and measured the accented name.

CREATE TABLE #Raw (ItemId nvarchar(20), Description nvarchar(100), Quantity nvarchar(20));
BULK INSERT #Raw FROM 'C:\Import\items.csv' WITH (FORMAT = 'CSV', FIRSTROW = 2, ROWTERMINATOR = '0x0a');
SELECT LEN(Description) AS CharsNoCodepage, UNICODE(SUBSTRING(Description, 2, 1)) AS SecondChar FROM #Raw WHERE ItemId = N'3';
DELETE FROM #Raw;
BULK INSERT #Raw FROM 'C:\Import\items.csv' WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001', ROWTERMINATOR = '0x0a');
SELECT LEN(Description) AS CharsCodepage, UNICODE(SUBSTRING(Description, 2, 1)) AS SecondChar FROM #Raw WHERE ItemId = N'3';
DROP TABLE #Raw;

Without the setting, the name had 10 characters and the second one was code 9500, a box drawing symbol. With it, the name had 9 characters and the second was code 252, the letter ü. Wrong encoding does not raise an error. It quietly damages the text, so check how the file was saved before you pick the code page.

Match the Line Endings

My file uses LF endings, so the load sets ROWTERMINATOR = '0x0a'. Without it, the same load failed with Msg 7301. A copy of the file with CRLF endings loaded with no terminator at all. Setting 0x0a on that copy left a carriage return at the end of the last text column. The value 4 had a length of 2. Check where the file came from before you choose.

The Import Flat File Wizard

For a one-time import into a new table, SSMS offers Tasks, Import Flat File on the database. Review the preview and the guessed column types before you finish. A wizard import still needs the same data check afterwards.

More CSV Examples

• Import CSV File Into SQL Server Using Bulk Insert
• Import CSV File into Database Table Using SSIS
• Create a Comma Delimited List Using SELECT Clause From Table Column
• Comma Separated Values (CSV) from Table Column
• Comma Separated Values (CSV) from Table Column, Part 2

A CSV import is not complete when the command finishes, it is complete when the data checks out.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.