The postings on this site are my own and do not represent my Employer's positions, advice or strategies.


  Tuesday, March 19, 2019


bob tech
Contact Bob
VW Buses
Cook Book



  bob Links
Welcome to Sunspiced...
'Twas a woman who drove me to drink, and I never had the courtesy to thank her for it. - W.C. Fields
Wider View Insert

IBM - AS400
Performance Counters
Service Broker
SQL Server
     Temporary Database
     SQL Mail
     DTS - Data Transformation Services
          Excel Buffer Errors
     Jobs SQL Agent
     User Management
     System Databases
     Temp Tables
Windows OS

Insert Category

Will be added as a sub-category of, Excel
Excel Buffer Errors
Why do I receive buffer size errors when trying to import an Excel spreadsheet? Chances are, this is caused by a registry key that is set incorrectly for your Excel Jet driver. Generally, the full error will look like this: "Error at source for row number 9.Errors encountered so far in this task :1" General Error: -2147217887(80040E21) Data for Source Column 1('Col1') is too large for the specified buffer size. When importing an Excel spreadsheet using the Jet driver, the OLE DB provider will scan the first few rows to look for any large columns. If it doesn't find any, it sizes the buffer to be smaller. But if a large text row is later in the spreadsheet you'll experience the problem. Fix You can either move one of your larger rows up to the top of the spreadsheet or edit a registry key to fix it forever. The registry setting to modify is: HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows By default, the value is 8, which means it will scan 8 rows to size the buffer for columns larger than 255 characters. By clicking the TypeGuessRows data value and selecting Decimal, you can specify a new number. Before you click OK, change it back to a Hexidecimal. Please note that editing the registry could be dangerous. Always backup your registry before making any changes to it. It is suggested that you make an emergency repair disk. For more information on this problem, see Microsoft support article Q281517.