Tip: How to fix the upload excel "numbers as text" problem

Tip: How to fix the upload excel "numbers as text" problem

  
Symptoms:
When uploading an excel file that in a text data type column has it's firsts rows as numbers, the returned RecordList has the associated column without any text literals entries and only number-like entries.

Problem:
The component used by the OutSystems Plataform to upload excel files is the Microsoft Jet engine 4.0, which tries to guess a column data type by inspection of the first 1 to 16 lines. Which means that when a column in excel of text data type has it's first 1 to 16 values as numbers and the remaining values has text literals, instead of assuming a text data type column it assumes an integer data type column, eliminating any entry that does not directly converter to integer.

Workaround:
Since version 3.1.3.9 of OutSystems Hub Server a fix has been released that forces the inspection of the all rows in a column of an excel file. However, for this fix to work it must be changed a key in the windows registry:

Set the key HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to "0"

Afterwards one must restart the IIS by issueing the IISRESET command.