Strings truncated to 256 characters when importing Excel sheet to SQL Server

Problem

Value of some Excel cells which contain long strings are truncated to 256 characters when importing an Excel sheet to SQL Server.

Case

There is an Excel sheet which should be imported to SQL table using SQL Server import data wizard. In the wizard the data types mapping is set to nvarchar(max) so the strings longer than 256 chars are imported without being truncated. Import is executed and finished without errors. When checking the resulting table all long string are truncated to 256 characters even though the field type is nvarchar(max).

Investigation

SQL checks the first 8 rows to make assumption for the data types. If there are no strings longer than 256 characters in a particular column in the first 8 rows SQL will consider it as nvarchar(255) and will truncate all the cells of that column to 256 character. Even if you change the mapping type in the import wizard to be nvarchar(max) it will ignore it and won’t fix the problem. The worst thing is that the import is finished “successfully” without any error or warning about the truncated strings.

Solution

There are two ways to fix this:

1. If there are cells with text longer than 256 characters then make sure you have such a row in the first 8 rows. For example add a temporary row in Excel before importing in the first 8 rows and add strings longer than 256 characters to let SQL know that that column should be treated as nvarchar(max).
2. Another way is to change a registry key (HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows) and increas the default number of 8 column that SQL uses to guess the content of the rows to a bigger number so it will includes the string longer then 256 chars in its evaluation.
   Check this link for more details about changing this registry key PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer Overflow Error