DB-유

Importing CSV Files into SQL Server with BULK INSERT

· mssql, tsql, import

The Import Wizard is fine for a one-off file. Once an import has to run on a schedule, or the file is a few gigabytes, I switch to BULK INSERT. It runs inside the engine, it's fast, and it needs nothing but T-SQL. The catch is that CSV files are messier than they look: quoted fields with embedded commas, Windows versus Unix line endings, a header row, UTF-8. Most failures I've had come from one of those, and SQL Server 2017 finally added a proper FORMAT = 'CSV' option that handles quoting.

Things to have in place first. SQL Server 2017 for FORMAT = 'CSV' and FIELDQUOTE (earlier versions use the older delimiter approach in the last error below). The file has to be readable by the SQL Server service account, not by you, so use a UNC path for remote files. And you need ADMINISTER BULK OPERATIONS (or bulkadmin) plus INSERT on the target.

Step 1: Create a staging table

I always import into a table of varchar columns first. Converting types during the bulk load means one bad row fails the whole batch; converting afterwards with T-SQL lets you find and report the bad rows.

CREATE TABLE dbo.Orders_Staging (
    OrderId     varchar(20),
    CustomerId  varchar(20),
    OrderDate   varchar(30),
    Amount      varchar(30),
    Note        varchar(4000)
);

Step 2: Inspect the file

Before writing the statement, check the first lines and the line endings. In PowerShell:

Get-Content -TotalCount 3 D:\Import\orders.csv
(Get-Content -Raw D:\Import\orders.csv).Contains("`r`n")

True means Windows line endings (\r\n); False usually means Unix (\n). Also check for a byte order mark or non-ASCII characters, which tells you whether the file is UTF-8.

Step 3: Run BULK INSERT

A typical UTF-8 CSV with a header row, quoted fields and Windows line endings:

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

What each option is doing:

  • FORMAT = 'CSV' follows RFC 4180: fields wrapped in FIELDQUOTE may contain commas and line breaks, and a doubled quote inside a field is a literal quote.
  • FIRSTROW = 2 skips the header. This counts rows, not lines, so a header with an embedded newline still counts as one row.
  • ROWTERMINATOR = '0x0d0a' is the hex form of \r\n; use '0x0a' for Unix files. The hex form avoids the confusion that '\n' is interpreted as \r\n in some contexts.
  • CODEPAGE = '65001' is UTF-8, supported since SQL Server 2016. Omit it for ANSI files.
  • TABLOCK takes a table lock and enables minimal logging on a heap in the Simple or Bulk-Logged recovery model, which is much faster for large files.
  • ERRORFILE writes rejected rows to a file (and creates a .Error.Txt companion describing why). The file must not exist beforehand.

Step 4: Convert and validate

Move the data into the real table with explicit conversions, and capture anything that does not parse:

SELECT * INTO dbo.Orders_Rejected
FROM dbo.Orders_Staging
WHERE TRY_CONVERT(int, OrderId) IS NULL
   OR TRY_CONVERT(date, OrderDate, 120) IS NULL
   OR TRY_CONVERT(decimal(12,2), Amount) IS NULL;

INSERT dbo.Orders (OrderId, CustomerId, OrderDate, Amount, Note)
SELECT CONVERT(int, OrderId), CustomerId, CONVERT(date, OrderDate, 120), CONVERT(decimal(12,2), Amount), NULLIF(Note, '')
FROM dbo.Orders_Staging s
WHERE NOT EXISTS (SELECT 1 FROM dbo.Orders_Rejected r WHERE r.OrderId = s.OrderId);

Count the rows

SELECT COUNT(*) FROM dbo.Orders_Staging;
SELECT COUNT(*) FROM dbo.Orders_Rejected;
SELECT TOP 5 * FROM dbo.Orders_Staging;

The staging count plus any rows listed in the ERRORFILE should equal the number of data lines in the file ((Get-Content D:\Import\orders.csv | Measure-Object -Line).Lines - 1 in PowerShell). Look at the top rows to make sure quotes were stripped and columns landed where they should.

Errors, roughly in the order I hit them

  • Msg 4861: Cannot bulk load because the file "D:\Import\orders.csv" could not be opened. Operating system error code 5(Access is denied.). The SQL Server service account cannot read the file. On a share, grant the service account (or the machine account for a virtual account) read permission, and use \\server\share\file.csv, never a mapped drive letter, since drive mappings belong to your session.
  • Msg 4860: Cannot bulk load. The file "D:\Import\orders.csv" does not exist or you don't have file access rights. The path is interpreted on the server, not your workstation. D:\Import must exist on the SQL Server machine.
  • Msg 4863: Bulk load data conversion error (truncation) for row 2, column 5 (Note). A field is longer than the staging column, or the row terminator is wrong and the whole rest of the file is being read as one field. Check line endings first.
  • Msg 4864: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 4 (Amount). Non-UTF-8 bytes with CODEPAGE = '65001', or the opposite. Open the file in a hex editor or re-save it as UTF-8.
  • Column values contain the quote characters. You are on SQL Server 2016 or earlier, or forgot FORMAT = 'CSV'. On older versions, use FIELDTERMINATOR = '","' and ROWTERMINATOR = '"\n' and strip the leading quote from the first column afterwards, or use a format file.

Once the statement works it drops straight into an Agent job or a stored procedure.