Workaround: Data Truncated Using Import Database Dialog Box (DBImport API)

[Derek Read]

Product(s):
XMetaL Author and XMetaL Developer versions 4.0+

Description:
Some strings may be truncated to 255 characters when importing data from an Excel spreadsheet using the DBImport APIs.

This issue is due to a known limitation in Microsoft's Excel ODBC driver. Luckily there is a workaround for this issue.

How the Excel ODBC Driver Works:

• The driver looks at the data in the Excel worksheet to figure out what SQL type to assign to each column of data.
• The driver examines a fixed number of rows of data in the worksheet and based on the values it finds in the cells determines what the SQL type should be for that entire column. By default the number of rows the driver looks at is 8 but the number can be changed by setting a windows registry value.
• When the driver finds text in the first 8 rows of a particular column and the text in all 8 cells contains fewer than 256 characters it assigns the type 'string'. If one or more of these cells contains more than 255 characters it assigns the type 'memo'. An SQL type 'string' is limited to 255 characters while the SQL type 'memo' can have virtually any number of characters.
• If cells further down in the worksheet contain more than 255 characters but the driver has decided that column is of type 'string' the content in those cells is truncated to 255 characters.

Solutions:
There are 2 possible ways to make the Excel ODBC driver assign the type 'memo' to the cells in a column:
   1. Modifying a registry setting.
   2. Adding additional data to the Excel worksheet.

[u]Solution 1: Registry Setting[/u]
Modify the registry setting that controls the number of rows the driver checks. Acceptable values are 0 to 16 inclusive. A value of 0 means “examine all rows”. Microsoft states that setting the value to 0 may cause some performance problems for large worksheets (given that all rows are examined). The setting is also applied globally, meaning that any other application that uses the Excel ODBC driver will behave the same way, possibly with a similar performance drawback. If you know that your worksheets will always contain a very long string somewhere in the first 16 rows you can set a value of 16 (or lower). The setting is a DWORD value called TypeGuessRows and is contained inside one of the following registry keys:

• Excel 97: HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet3.5EnginesExcel
• Excel 2000 and later versions: HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel

Temporary Registry Change Using Script
If you find you need to avoid making this change permanent, so that it does not affect other applications, it should be possible to make the registry change temporarily using script. You would place code that makes the registry change (setting the value to 0) at the start of your script. The middle of your script would perform the import from the Excel file (using the DBImport APIs). Then after the table has been inserted into the document the script would reset the value in the registry to the default (8).

We specifically do not provide an example of a script that manipulates the Windows registry because this can be dangerous and should be done with caution by someone that has thoroughly read and understands the Microsoft documentation on this subject. For information about manipulating the Windows registry using script see RegWrite and RegDelete in the External References section of this article.

[u]Solution 2: Add Data to the Excel Worksheet[/u]
Include a cell containing more than 255 characters in the first row of the worksheet for each column that may contain strings larger than 255 characters (further down the column). A string with length 256 is sufficient. This may be an easier way to work around the problem but may add unwanted data to the spreadsheet unless it is possible to move an existing long string from the bottom of a column nearer the top. People working with the spreadsheet will need to avoid altering the content of this cell. In addition, this extra row will be included in the data that is imported into XMetaL Author. It should be easy to remove this row via script right after the table has been inserted.

JScript Example for Blindly Removing the First row in an HTML Table:
[code]//XMetaL Script Language JScript:
rng = ActiveDocument.Range;
rng.MoveToElement(“table”,false);
rng.MoveToElement(“td”);
rng.DeleteRow();[/code]

JScript Example for Blindly Removing the First row in a CALS Table:
[code]//XMetaL Script Language JScript:
rng = ActiveDocument.Range;
rng.MoveToElement(“table”,false);
rng.MoveToElement(“entry”);
rng.DeleteRow();[/code]

Both of these scripts assume that the code immediately preceding it (the portion that does the table insertion using the DBImport APIs) leaves the selection (cursor) to the right of the inserted table element (the default location after a table has been inserted into a document).

Note that the method DeleteRow() expects the Selection (or Range) to end up inside a table cell (td / th / entry element) and not inside a table row (tr / row element).

Alternative Ideas:
A slightly different (and perhaps not so elegant) idea might be to include a long string with a known set value that would not normally be used in your Excel data (perhaps something like: DO NOT MODIFY OR REMOVE ME DO NOT MODIFY OR REMOVE ME [etc…] Your script would then search specifically for this string to locate the row (replacing the first three lines in the scripts above).

Another option might be to manipulate the content of the Excel spreadsheet using script by calling Excel as an ActiveX object then inserting a long string at the start of the sheet, calling the XMetaL Author DBImport APIs to import the content from the Excel spreadsheet and then undoing the changes made to the spreadsheet once the data has been imported into XMetaL Author.

External References:

• Data truncated to 255 characters with Excel ODBC driver: http://support.microsoft.com/kb/189897
• SQL Server 2005 Books Online – Excel Source: http://msdn2.microsoft.com/en-US/library/ms141683.aspx
• Windows Script Host methods – RegWrite: http://msdn.microsoft.com/library/en-us/script56/html/wsMthRegWrite.asp
• Windows Script Host methods – RegDelete: http://msdn.microsoft.com/library/en-us/script56/html/wsMthRegDelete.asp

Reply

[Author]

Posts