The data from Excel is truncated to 255 characters
Article ID: 276 - Updated: Jul 21, 2015 - Products: All products - Version: Any - Category: License and Activation
When you retrieve data from Excel spreadsheet using the Microsoft Excel ODBC driver, the fields that contain more than 255 characters might be truncated to 255 characters.
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.
Even though you can change the Rows To Scan value in the ODBC Microsoft Excel Setup dialog box to something higher than 8 (but not higher than 16) this value is not being used by Excel. The Excel ODBC driver uses the TypeGuessRows DWORD value of one of the following registry key to determine how many rows to scan in your data.
NOTE: The registry keys below were taken from 64-bit machine. If you have 32-bit Windows installed, remove "Wow6432Node" from the path.
- Run Registry Editor.
- In Registry Editor, navigate to the key:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel (if you have Microsoft Office 2007)
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel (if you have Microsoft Office 2010)
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel (if you have Microsoft Office 2013)
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel (for any version of Microsoft Office)
- Double click TypeGuessRows.
- In the Edit DWORD Value dialog box, click Decimal in Base section.
- Enter the number of records that will be scanned for maximum field length. You can enter zero (0) to scan all records in Excel spreadsheet. Note: This is not recommended for large Excel spreadsheets.