Sql-server – the cause of this problem with CONVERT()

The problem isn't the number of rows, the problem is that you have a row which does not fit into the decimal(18, 0) data type. If you're on SQL 2012 you can identify the rows fairly easily (and can do your own number checking function on other versions).

Create Table dbo.Moo (
    Data Nvarchar(255)   
)

Insert dbo.Moo Values ('-1'), ('100'), ('1.1'), ('Cows')

Select  Convert(Decimal(18, 0), Data)
From    dbo.Moo
-- Msg 8114, Level 16, State 5, Line 7
-- Error converting data type nvarchar to numeric.

Select  *
From    dbo.Moo
Where   Data Is Not Null
And     Try_Convert(Decimal(18, 0), Data) Is Null
-- Cows

Once they're identified you can fix the data on those rows and then convert the table over as you originally attempted.

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255).

According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal.

Add one more cast to nvarchar(255) in the first branch of the CASE.

SELECT
CASE 
    WHEN [Parcel Number 1] like '%E+%'
    THEN CAST(CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
    ELSE [Parcel Number 1]
END
FROM XLSimport