Text from a excel data import is truncated.

Version 4

    Verified Product Versions

    Service Desk 7.7.xService Desk 7.8.xService Desk 2016.xAsset Manager 2016.xAsset Manager 2017.xService Desk 2017.x

    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.