MCTS 70-433 Training Material

https://www.dropbox.com/sh/urvrdb7ee2bh0mv/bWbOafD5Lu 

Data truncated to 255 characters with Excel ODBC driver

When you retrieve external data using the Microsoft Excel 2010 ODBC driver, fields that have more than 255 characters may 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:

Please do the following and then try to import data using ADO . This will solve the issue.

1.       Close any programs that are running.

2.       On the Start menu, click Run. Type regedit and click OK.

3.       In the Registry Editor, expand the following key:

• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
•  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

4.       Select TypeGuessRows and on the Edit menu click Modify.

5.       In the Edit DWORD Value dialog box, click Decimal under Base. Type value 0, for Value data. Click OK and quit the Registry Editor.

NOTE: For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is very large. When this value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column.

APPLIES TO

• Microsoft Office Excel 2010

References-  http://support.microsoft.com/kb/189897