Fix SQL Server Import Error: Column Does Not Allow DBNull.Value
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.

- 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.
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.
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.
Launch your preferred spreadsheet application (such as WPS Spreadsheet) and open the problematic CSV file.
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.
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.
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.
Go to 'File' > 'Save As' and ensure the format is set to 'CSV (Comma delimited)' before re-attempting the SQL Server import.

Verify Column Mappings and Null-Handling Settings
Check the SQL Server Import and Export Wizard settings to ensure nulls are handled correctly during the transfer.
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. 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. Apply AutoFilter: Select your primary key column, navigate to the 'Data' tab, and click the 'AutoFilter' icon.
- 3. Remove Blanks: Use the filter dropdown to isolate 'Blanks', right-click the row numbers, and delete the offending rows entirely.
- 4. Export the clean CSV: Click 'Menu' > 'Save As' and select 'CSV (Comma delimited)' to generate a database-ready file for SQL Server.

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).




