For BI purposes I have been working on a tool chain that fetches data from a wide variety of systems in mostly intermediate Excel steps to eventually import all of this into SQL Server and do the real smart BI magic.
Using the OPENROWSET command to Connect from SQL directly to an Excel file with the Microsoft.ACE.OLEDB.12.0 driver. However, from time to time the column configuration of the SQL tables would change and be incorrectly detected.
Today I finally found that there is a specific behavior in the driver that can be changes with the registry key TypeGuessRows under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
This forces the driver to look further then the default 8 rows for determining the data type. This is important if you have only small amounts of text in the first 8 rows but after that longer amounts of text that would require the field to be NTEXT instead of NVARCHAR(255).