Text from a excel data import is truncated.

Environment:

Seen in ServiceDesk 7.7.1

Review Date:

10/12/14

Problem:

When importing text from a excel spreadsheet the text is sometime cut short / truncated eventhough the options in the import mappings are not set and the attibutes are the correct size.

Cause:

The can be due to the "Microsoft Access Database Engine" as the import is only collecting the data that is provided by this component.

These two links help to explain the possible reason.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e16cc2ed-42b0-446c-a16f-b2b732eaf7be/text-truncated-to-255-using-ssis-aceoledbprovider-to-import-excel-2007-spreadsheet?forum=sqlintegrationservices

http://msdn.microsoft.com/en-us/library/ms141683.aspx

For other data import issue please review the following acticle.

Data Import - Troubleshooting Guide

Solution / Workaround:

Before trying the suggestions from the above Microsoft links please remember that changes to the registy are taken at your own risk.

Please make sure you have back or any registy key of files that are needed to be changed also consider take a system restore point or snapshot of the system.

The follow registy key seem to cause the issue.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

and

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

The default is to guess 8 rows.

I have changed to 16 and this resolved the issue.