About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Troubleshooting
Problem
When using i2 Analyst's Notebook to import data from an excel spreadsheet into the Analysis Repository, sometimes long string values are getting truncated to 255 characters when the data is imported. There is no warning or error message that appears in the summary after the import is complete.
Is there a way to avoid this problem?
Cause
The i2 Analyst's Notebook Importer relies on the OLE DB drivers available in Microsoft Access Database Engine redistributable package to access the content in the Microsoft Excel columns. When determining the data type of a column value, the Microsoft Access database engine by default will examine the first 8 rows of the spreadsheet to try and figure out the formats of the column. If a cell that contains > 255 characters is *not* in those first 8 rows then it will assume a text field that has a 255 character limit. If a cell with > 255 characters *is* in the first 8 rows then it will assume a memo field.
Therefore, if the long string value in your Excel import data does not appear in the first 8 rows, its value will be truncated to 255 characters.
Resolving The Problem
Fortunately, the number of rows the Microsoft Access Database Engine examines to determine the datatype is configurable in a setting in the Windows Registry, TypeGuessRows. Change the value from 8 to 0 to have the data type checking include the first 16,385 rows.
The actual registry key location depends on the version of the Access Database Engine you have installed on your system as well as whether your Windows system is 32-bit or 64 bit. You can refer to this article to find the location of the registry key on your system:
Using the TypeGuessRows setting for Excel Driver
For example, if you have Microsoft Access Database Engine 2010 on a 64 bit Windows system, the location of the TypeGuessRows key is:
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
Related Information
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSXVMQ","label":"i2 Analyst's Notebook Premium"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Historical Number
TS001525587
Product Synonym
ANB
ANBP
Was this topic helpful?
Document Information
More support for:
i2 Analyst's Notebook Premium
Software version:
All Versions
Operating system(s):
Windows
Document number:
793675
Modified date:
08 January 2019
UID
ibm10793675