C# & ADO.NET

Import CSV or Excel data into SQL Server from ASP.NET (SqlBulkCopy and BULK INSERT)

C# & ADO.NET · By the Postback Notes editorial team · Published 2026-08-16

Importing a spreadsheet of products or contacts is a classic admin feature. Inserting row by row works for a few dozen rows; for thousands, SqlBulkCopy streams them to SQL Server in one operation. The work around it is parsing the file correctly and rejecting bad rows before they reach the database.

1. Parse the CSV

Splitting lines on commas fails as soon as a field contains a quoted comma. .NET Framework ships a real CSV parser in the Visual Basic assembly, usable from C# (add a reference to Microsoft.VisualBasic):

using Microsoft.VisualBasic.FileIO;

public static DataTable ParseProducts(Stream csv, List<string> errors)
{
    var table = new DataTable();
    table.Columns.Add("Sku", typeof(string));
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Price", typeof(decimal));

    using (var parser = new TextFieldParser(csv, Encoding.UTF8))
    {
        parser.TextFieldType = FieldType.Delimited;
        parser.SetDelimiters(",");
        parser.HasFieldsEnclosedInQuotes = true;
        parser.ReadFields();                         // skip header

        while (!parser.EndOfData)
        {
            long line = parser.LineNumber;
            string[] f;
            try { f = parser.ReadFields(); }
            catch (MalformedLineException ex) { errors.Add($"Line {ex.LineNumber}: malformed"); continue; }

            if (f.Length != 3 || f[0].Trim() == "" ||
                !decimal.TryParse(f[2], NumberStyles.Number, CultureInfo.InvariantCulture, out decimal price))
            {
                errors.Add($"Line {line}: invalid values");
                continue;
            }
            table.Rows.Add(f[0].Trim(), f[1].Trim(), price);
        }
    }
    return table;
}

2. Bulk copy into a staging table

CREATE TABLE dbo.ProductImport (Sku nvarchar(40) NOT NULL, Name nvarchar(200) NOT NULL, Price decimal(10,2) NOT NULL);
using (var con = new SqlConnection(cs))
{
    con.Open();
    using (var tx = con.BeginTransaction())
    {
        new SqlCommand("DELETE FROM dbo.ProductImport", con, tx).ExecuteNonQuery();

        using (var bulk = new SqlBulkCopy(con, SqlBulkCopyOptions.CheckConstraints, tx))
        {
            bulk.DestinationTableName = "dbo.ProductImport";
            bulk.ColumnMappings.Add("Sku", "Sku");      // map by name, not by position
            bulk.ColumnMappings.Add("Name", "Name");
            bulk.ColumnMappings.Add("Price", "Price");
            bulk.BatchSize = 5000;
            bulk.WriteToServer(table);
        }

        // 3. merge from staging into the real table in one set-based statement
        new SqlCommand(@"
            MERGE dbo.Products AS t
            USING dbo.ProductImport AS s ON t.Sku = s.Sku
            WHEN MATCHED THEN UPDATE SET t.Name = s.Name, t.Price = s.Price
            WHEN NOT MATCHED THEN INSERT (Sku, Name, Price) VALUES (s.Sku, s.Name, s.Price);", con, tx)
            .ExecuteNonQuery();

        tx.Commit();
    }
}

The staging table keeps the real table untouched until every row has arrived; the final statement inserts or updates in one pass. If two users may import at once, add a batch id column to the staging table instead of deleting everything. Without explicit column mappings, SqlBulkCopy maps by ordinal position, a frequent source of silently swapped columns.

The page

protected void btnImport_Click(object sender, EventArgs e)
{
    if (!fuCsv.HasFile || !fuCsv.FileName.EndsWith(".csv", StringComparison.OrdinalIgnoreCase)) return;
    var errors = new List<string>();
    DataTable rows = ParseProducts(fuCsv.PostedFile.InputStream, errors);
    if (errors.Count > 0) { blErrors.DataSource = errors.Take(50); blErrors.DataBind(); return; }
    ImportProducts(rows);
    lblResult.Text = rows.Rows.Count + " rows imported.";
}

Rejecting the whole file when any row is invalid is often clearer for users than partial imports. Upload limits and safe handling of the file are covered in Upload and download files.

BULK INSERT in T-SQL

BULK INSERT dbo.ProductImport
FROM 'D:\Imports\products.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001');

Fast and simple, but the path is read by the SQL Server service on its own machine, and the login needs the ADMINISTER BULK OPERATIONS permission. FORMAT = 'CSV' (quoted fields) requires SQL Server 2017 or later. From a web application, SqlBulkCopy is usually easier.

Excel files

For .xlsx uploads, read the sheet with a library such as ClosedXML or ExcelDataReader into a DataTable, then use the same bulk copy. Avoid the ACE OLE DB driver on servers: it requires an Office component installation and is not supported for server-side use. Passing smaller sets of rows to a stored procedure is shown in Table-valued parameters.

Official documentation