Others

Fix SQL Server Import Error: Column Does Not Allow DBNull.Value

Khadija KhanKhadija Khan Nov 2, 2026 868 views

Question details

The user needs to resolve an error preventing a CSV file from being imported into a database due to unexpected null values in a primary-key column.

How to Fix SQL Server Import Error: Column Does Not Allow DBNull.Value
Product
SQL Server Import and Export Wizard
Device & OS
not provided
Scenario
Importing a CSV data file into a SQL Server database table that contains a primary key constraint.
Observed behavior
The Import Wizard throws an error stating that the column does not allow DBNull.Value, which immediately halts the data import process.
Before you start

Ensure you have a backup of your original CSV file before making any structural changes or deleting rows to fix the hidden null value errors.

Solution 1Recommended

Clean CSV Data and Remove Empty Rows

Use a spreadsheet editor to find and remove hidden empty rows or spaces at the end of your file that SQL Server interprets as null values.

Even if data appears clean, CSV files often contain hidden trailing blank rows or empty cells in the primary key column. The SQL Server Import Wizard reads these empty strings as DBNull.Value, triggering an error when inserting into a NOT NULL or Primary Key column.

1
Open the CSV file

Launch your preferred spreadsheet application (such as WPS Spreadsheet) and open the problematic CSV file.

2
Filter for blank values

Select the primary key column, navigate to the 'Data' tab, and apply a 'Filter'. Click the filter dropdown on the column header and select only '(Blanks)' to reveal empty rows.

3
Delete empty rows

Highlight all the rows that appear as blank, right-click the row numbers on the left, and select 'Delete' to remove them entirely from the dataset.

4
Clear trailing file rows

Scroll to the very bottom of your dataset, select several empty rows below your last line of data, right-click, and delete them to remove hidden trailing commas.

5
Save as CSV

Go to 'File' > 'Save As' and ensure the format is set to 'CSV (Comma delimited)' before re-attempting the SQL Server import.

Clean CSV Data and Remove Empty Rows
Tip: Using the 'Find and Replace' tool to replace multiple spaces with nothing can also help clear out cells that look empty but contain whitespace.

Prepare Error-Free CSV Files for SQL Import with WPS Office

Use WPS Spreadsheet to easily filter out blank rows, handle null values, and ensure your CSV data perfectly matches your SQL Server database schema before importing.

  1. 1. Open the CSV in WPS Spreadsheet: Launch WPS Office and open your target CSV file to view the raw data in an organized grid.
  2. 2. Apply AutoFilter: Select your primary key column, navigate to the 'Data' tab, and click the 'AutoFilter' icon.
  3. 3. Remove Blanks: Use the filter dropdown to isolate 'Blanks', right-click the row numbers, and delete the offending rows entirely.
  4. 4. Export the clean CSV: Click 'Menu' > 'Save As' and select 'CSV (Comma delimited)' to generate a database-ready file for SQL Server.
Effortlessly handle large CSV files without lagging or crashing.Advanced filtering and data validation tools to instantly spot null values.Fully compatible with Microsoft Excel file formats, including XLSX and CSV.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the SQL Server Import Wizard read empty strings as nulls?

By default, the SQL Server Import Wizard interprets entirely empty fields in a CSV (e.g., two consecutive commas like ,,) as NULL values. If the destination column is a primary key or has a NOT NULL constraint, inserting this NULL value will cause the import to fail.

Can I skip rows with errors during the SQL Server import?

Yes. In the Import and Export Wizard, under the 'Review Data Type Mapping' or 'Error Output' configuration pages, you can change the default 'Fail' action to 'Ignore' for specific error types. This allows the wizard to skip problematic rows and import the rest of the clean data successfully.

How do I handle trailing spaces causing import issues?

Trailing spaces in a CSV can sometimes cause mapping and validation issues, making cells appear empty when they actually contain space characters. You can use the TRIM function in a spreadsheet software like WPS Spreadsheet to remove extra spaces before saving the CSV, or apply a derived column transformation if you are using SQL Server Integration Services (SSIS).