SQL Server: Columns truncated to 255 characters when using OPENROWSET or OPENDATASOURCE to import from Excel

I was using the SQL command OPENROWSET to directly import a load of text from an Excel spreadsheet to my database. The columns were of varying widths, some up to 3,000 characters.

The problem I experienced was that for some columns the output of the statement was being truncated to 255 characters - but not for all of them. I couldn't understand why this would happen and tried various combinations of OPENROWSET and OPENDATASOURCE, but to no avail.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

    INSERT INTO tblRichTextFields (Requester,feet,approval) 
    SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="C:\RichtextFields.xls";Extended Properties=''Excel 12.0''')...[Sheet1$]

GO

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 0
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

The only thing I could think of was that it was something to do with the Excel cell formats. I compared the columns which were being truncated to the column which was not, but they were the same. I tried changing them all to Text to see if that made any difference. Still truncated.

I couldn't find much more on Google, nor in my brain - but eventually I did find a fix which proved me right - it was some underhanded 'guesswork' on Microsoft's part - surprise, surprise. See this quote:

"Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters. The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column."

I changed the Registry Setting which controls the 'Excel format guessing' behaviour from 'on' to 'off', logged out and back in - and Hey Presto! it worked.

Setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Mi­­crosoft\Jet\4.0\Engines\Excel­\­TypeGuessRows

Change the value from 8 hex to 0

I don't think this fix will work in every situation and I haven't considered every possible implication of changing this registry setting, but if your text is being truncated like this - this is worth a try. Take a backup of your registry before changing it and it's not my fault if you Michael Berk it up!

Here's some detailed info on the Microsoft website.