Data Import & Export

How to Fix SSMS 19 Importing Excel Numeric Values as NULL

Jason Miller Oct 20, 2026 869 views

Question details

Numeric values in an Excel file are imported as NULL when brought into SQL Server Management Studio (SSMS) 19.

How to Fix SSMS 19 Importing Excel Numeric Values as NULL

Product: Microsoft Excel, SSMS 19
Device & OS: not provided
Scenario: Importing an Excel worksheet containing numeric data into a SQL Server database table using SSMS 19.
Observed behavior: Valid numeric values in the Excel file are converted to NULL during the import process because SSMS infers an incompatible column data type due to mixed data types, spaces, or inconsistent formatting.

Before you start

Make a backup of your original Excel file to ensure you do not lose any raw data while cleaning cell formats and removing hidden characters.

Solution 1Recommended

Clean and Standardize Excel Data Formats

Ensure all cells in the affected column are strictly formatted as numbers and free of hidden text characters that cause SSMS to misinterpret the data type.

The Excel import engine in SSMS relies on sampling the first few rows to determine the data type of the entire column. If it detects text strings, leading spaces, or blank cells in the sample, it expects text. When it later encounters pure numeric values, the type mismatch forces those numbers to import as NULL.

Remove hidden spaces

Select the problematic column in Excel. Press Ctrl+H to open Find and Replace. Enter a single space in the 'Find what' field, leave 'Replace with' empty, and click 'Replace All'.

Clear text formatting

Select the entire column, right-click, and choose 'Format Cells'. Under the Number tab, select 'Number' and click OK.

Convert text to columns

With the column selected, go to the 'Data' tab and click 'Text to Columns'. Choose 'Delimited', uncheck all delimiters, and click 'Finish'. This forces Excel to re-evaluate the data as numbers.

Save and retry

Save the Excel workbook and run the SQL Server Import and Export Wizard again in SSMS 19.

Clean and Standardize Excel Data Formats

Check SSMS Destination Types: Always verify in the SSMS Import Wizard's mapping section that the destination column is set to a compatible numeric type like INT, FLOAT, or DECIMAL.

Solution 2

Convert Excel to CSV Before Importing

Bypass Excel's data type inference issues entirely by saving the worksheet as a plain text CSV file, giving you explicit control over data types during import.

Data Cleaning Solution

Prepare Data for SSMS Flawlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data cleaning tools and highly reliable CSV export capabilities to help you prepare your data for a seamless import into SSMS 19 without NULL errors.

1. Open data in WPS: Launch WPS Spreadsheet and open your problematic Excel workbook.
2. Clean the column: Select the numeric column, right-click to choose 'Format Cells', and set it uniformly to 'Number'.
3. Export to CSV: Click 'Menu' > 'Save As' and select 'CSV Format' to strip away hidden metadata.
4. Import to SSMS: Use the SQL Server Import and Export Wizard with the Flat File Source option to import your clean data.

Free and lightweight spreadsheet toolHigh compatibility with Microsoft Excel (.xlsx) formatsIntuitive Find & Replace and Text-to-Columns features to clean invalid charactersReliable and clean CSV exporting to bypass SSMS data inference issues

microsoft office alternative - wps office

Frequently Asked Questions

Why does SSMS guess the wrong data type from my Excel file?

SSMS uses an OLE DB provider that scans only the first few rows (usually 8) to determine the data type. If it finds text, formulas returning blanks, or empty cells in those sample rows, it assumes the column is text. When it encounters actual numbers later, it fails to convert them and imports them as NULL.

How do I use BULK INSERT for Excel data?

The BULK INSERT command in SQL Server cannot read native .xlsx files directly. You must first save your Excel worksheet as a .csv or .txt file. Once saved as a flat file, you can run the BULK INSERT command pointing to that file path.

Can I force the Excel driver to read mixed data types as text to avoid NULLs?

Yes, if you use the OPENROWSET function in a query, you can append 'IMEX=1' to your connection string. This tells the driver to treat mixed data columns as text, preventing numeric values from turning into NULL, though you will have to convert them to numbers after they are in SQL Server.

Does cell formatting in Excel guarantee the correct SSMS data type?

No. Visual cell formatting (like setting a column to 'Number') does not always change the underlying stored value type in Excel if there are hidden characters like apostrophes. You must use tools like 'Text to Columns' or 'Find and Replace' to truly clear text strings.

Excel & Data Problems