Data truncation problem in Excel sheets

Hi All,

Problem

Recently I faced a problem like automation scripts (I used Jscript, but the problem is independent of any scripting language) not able to extract more than 255 characters from a Microsoft Excel column which holds strings as rows.

Basically I need to store strings in the particular column and get those inside scripts…

The Excel was able to hold the strings when we store it (> 255 characters (or) < 255 characters) but the problem occurs only while reading it through scripts.

Finding

It is the functionality of Excel to scan the first eight rows (or 16) in a column to determine its data type and then act up on the data type.

In my Excel sheet, the first 8 rows of desired column don’t have any string with more than 255 characters. Thus the column is expected to hold only 255 characters and data type string is set to that column. (Determined by Excel by scanning first 8 rows)

< 255 Characters – ‘String’ type

> 255 Characters – ‘Memo’ type

Whatever data stored in the rest of the rows (even with more than 255 characters) will be discarded and only the first 255 characters are fetched when reading out for scripts through any excel driver.

Similar conflict may happen with numeric and alpha-numeric data types when you store numeric data in first 8/16 rows of particular column and have alpha-numeric data in the below rows. Excel assumes this column will hold only numeric data and will truncate any alphabets found in the following rows.

To overcome it, we have workarounds [http://support.microsoft.com/kb/189897]. It suggest to tweak the registry values of Excel Application.

Due to the following reasons the workaround may not be feasible…

1. Security policies may not allow you to change registry entries (or) typo error mat cause application to crash

2. Having 0 in the ‘TypeGuessRows’ DWORD may affect performance because it scans all the rows to determine the data type

Simple solution will be placing data which will exactly reflect your intended data type in the first 8 rows of the column so that excel scans and gets the exact data type. (this worked for me)

Have you faced similar kind of problem before? What other workaround you suggest to this problem?

Any suggestion/corrections are welcome…

 

Thanks & Regards,

Giri Prasad