Importing CSV Files Without Surprises

The file arrives as CSV, and the first row looks perfectly ordinary. Importing CSV files without surprises starts by testing the odd rows before they reach the final table.

Treat CSV as a Format, Not a Promise

CSV looks simple until a customer name contains a comma, a note contains a quote, or a date changes format halfway through the file. A delimiter is only a field separator when it is outside a quoted field. A text editor can make malformed data look regular because rows and fields are visually close together. I ask for a sample containing the awkward cases, not only the first ten rows.

Define the contract before importing: header presence, column order, delimiter, quote character, line ending, encoding, null representation and date format. Which of those did the sending system actually promise? A file extension does not answer. The best intake check is boring and specific, which is exactly what an overnight import needs. Surprise is a poor data type.

Use a CSV-Aware Parser

SQL Server BULK INSERT supports FORMAT = ‘CSV’, so quoted delimiters are handled as CSV fields rather than as ordinary separators. FIELDQUOTE can specify the quote character when needed. The example uses the default comma field terminator and a first row of headers. Run it in a test database after creating the staging table and placing a trusted file where the SQL Server service account can read it.

I avoid splitting rows with string functions before CSV parsing. That shortcut works until a comma appears inside a quoted address, and then every subsequent column is shifted. A parser still needs an agreed encoding and row ending. If the sender’s CSV dialect is unusual, validate the actual parser behavior with a small sample before scheduling a production load.

Stage Everything as Text First When Importing CSV Files

A staging table preserves the raw field values so conversion errors can be inspected. The example stores an order identifier, an ISO-style date, a customer name and an amount as text. Give the table enough width for the agreed contract and reject oversized input deliberately. Do not let a direct import into a typed final table decide the fate of a whole batch without an error path.

I include a load identifier or file name in a production staging design, though the small script below focuses on the CSV mechanics. That marker lets operations tie every rejected row to the exact source file and rerun safely. It also helps when a partner sends the same file twice, a habit that rarely comes with an apology.

CREATE TABLE dbo.StageOrderCsv
(
    order_id_text nvarchar(50) NULL,
    order_date_text nvarchar(50) NULL,
    customer_name nvarchar(200) NULL,
    amount_text nvarchar(50) NULL
);

Load With BULK INSERT

The BULK INSERT statement names a local path from the SQL Server machine’s perspective. A path that exists only on the operator’s laptop will fail when the service runs elsewhere. CODEPAGE = ‘65001’ declares UTF-8; confirm that this matches the source bytes. FIRSTROW = 2 skips the header in this example. FORMAT = ‘CSV’ enables CSV quoting rules, so a quoted comma stays inside its field.

I test with a file containing an accented name, a quoted comma and a doubled quote. A clean load count is useful, but it does not prove correct character decoding. Inspect representative values after loading. If a byte-order mark, unusual delimiter or line ending changes the result, adjust the contract and script together rather than patching the final table by hand.

BULK INSERT dbo.StageOrderCsv
FROM 'D:\Imports\orders.csv'
WITH
(
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDQUOTE = '"',
    CODEPAGE = '65001'
);

Validate Dates and Numbers Explicitly When Importing CSV Files

A staging row becomes a final row only after every required field converts and business checks pass. Use TRY_CONVERT with an explicit style for dates rather than relying on session language. Style 23 corresponds to yyyy-mm-dd. Check amount range, required names and identifier uniqueness according to the receiving table’s rules. Keep invalid rows in staging with a clear reason and a decision path.

I never infer a date format from one friendly-looking row. A value such as 03/04/2026 can mean two different days. An explicit source contract makes that argument unnecessary. The validation query below identifies rows that fail the example conversions; it does not silently discard them. A rejected-row report is part of a reliable import, not an optional souvenir.

SELECT order_id_text,
       order_date_text,
       customer_name,
       amount_text
FROM dbo.StageOrderCsv
WHERE TRY_CONVERT(int, order_id_text) IS NULL
   OR TRY_CONVERT(date, order_date_text, 23) IS NULL
   OR TRY_CONVERT(decimal(12,2), amount_text) IS NULL
   OR NULLIF(LTRIM(RTRIM(customer_name)), N'') IS NULL;

Reconcile Before Publishing

Compare the source record count, staged count, valid count, rejected count and final inserted count. If the file uses quoted line breaks, a naive physical-line count is not the same as a record count; use a CSV-aware source count. Load the final table in a transaction where practical, with a uniqueness rule that makes reruns deliberate. Record the source file identity and completion status.

I look at a few rows near boundaries as well as aggregate totals. The first and last records, long text, quotes, non-ASCII characters and dates at month boundaries are useful checks. Matching counts can coexist with shifted columns, so sample content matters. A CSV import is complete when the data meaning survived the trip, not merely when BULK INSERT returned without an error.

Make Importing CSV Files Predictable Next Time

Once the script works, keep the format assumptions beside it. A source owner should know that changing delimiter, encoding or date representation is a contract change. Add an alert for rejected rows and unexpected volume, and retain enough source evidence to investigate. Clean up staging only after reconciliation and the agreed retention period.

I prefer a small repeatable pipeline over a heroic one-time import script. The recurring questions are ordinary: Can the server read the file? Did the parser preserve quoted fields? Did encoding survive? Did dates convert without ambiguity? Did every row end somewhere accountable? If those answers are visible, CSV stops being mysterious and becomes a manageable input format.

For importing CSV files on a schedule, add a controlled landing directory and archive policy. The database service account needs read access to the landing path, while the sender should not have broad database privileges. Record the source file name, byte size and receipt time before loading. I also retain a small sample of rejected rows with reasons, subject to the organization’s data-retention rules, so a correction can be requested without asking everyone to reconstruct the file.

Test the import with one intentionally malformed row. Confirm that it is rejected or quarantined according to policy while valid rows are accounted for. Then rerun the same file and observe whether the pipeline prevents duplicates. Idempotency is easier to design before a scheduler retries a job at night. The reliable result is not merely a populated table; it is a traceable decision about every record.

Related reading on this blog: Troubleshooting Common CSV Import Issues and Import CSV File Into SQL Server Using Bulk Insert: Load Comma Delimited File Into SQL Server.

A successful CSV load is not a clean import, it is the start of a checked path from bytes to typed rows.

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