Troubleshooting
Problem
In an Excel file that we want to import into iBase, one column contains some large text, for example some descriptions.
We want to import this column into a Multi-Lines field in iBase.
But on some occasions, the imported text in iBase is cut after 255 character.
This means that all the text that was after the 255th character is lost.
Cause
This problem happens if the first 8 or 10 lines of the column that holds the text are all less than 255 chars longs.
Excel tries to "guess" the type of the columns, based on the first 8 values of each column.
For example, if you have a column where the first 8 or 10 values (excluding the column header) are Dates, then Excel will consider that the entire column has a Date format, even if a value at, say, column 38, is a text.
For the text columns, Excel also looks at the first 8 values to determine if the column format is Text. But it also looks at the maximum length of the data of these 8 first rows.
If the max length is less than 255 chars, then Excel will keep the information that this column is a Text format, limited to 255 chars (even, as noted previously, if some cells on the same columns after the first 8 rows have more than 255 chars).
The new default value must be high enough to be certain that there will not be Excel files where the first N values of a column don't represent correctly all the values.
But it must not be too high, as it will slow down the opening speed of the Excel file.
Resolving The Problem
To resolve the problem, you must change the default number of lines that Excel is looking to guess the format of each column.
As we saw earlier, the default value is 8 rows.
To do this, follow the steps :
- Close all Excel files
- Close iBase and ANB
- Open the Windows Registry software with the command Regedit
- Go to the folder HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office
- Search for the key TypeGuessRows
- Depending on the version of Excel, this key can be in a different sub-folder.
- For example, with the recent Excel versions, it is is :
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
- By default, the value for this key is 8.
- Change the value to make it 100 or 200.
Related Information
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSXW43","label":"i2 iBase"},"ARM Category":[{"code":"a8m50000000Ci92AAC","label":"i2 iBase and i2 Analyst\u0027s WorkStation-\u003EImport\/Export"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
More support for:
i2 iBase
Component:
i2 iBase and i2 Analyst's WorkStation->Import/Export
Software version:
All Versions
Document number:
6518344
Modified date:
22 November 2021
UID
ibm16518344