Excel (Database) - lost data

[Ben]

Hi everyone,

We have a problem with an EXCEL spreadsheet; we lost some data.

When we open this file, Monarch assign a wrong datatype to a column. This column contains numeric values and alphanumeric values. Monarch set this column to Numeric... and Monarch replace alphanumeric values by NULL. And we can't change this Datatype !

We want to keep all data. Anybody have suggestion or solution ?

Thanks in advance.

Ben

[Gareth Horton]

Originally posted by Ben:
Hi everyone,

We have a problem with an EXCEL spreadsheet; we lost some data.

When we open this file, Monarch assign a wrong datatype to a column. This column contains numeric values and alphanumeric values. Monarch set this column to Numeric... and Monarch replace alphanumeric values by NULL. And we can't change this Datatype !

We want to keep all data. Anybody have suggestion or solution ?

Thanks in advance.

BenThere is no very simple solution to this.

This is a known Microsoft issue when using the ISAM driver for Excel to import mixed data from an Excel spreadsheet.

The problem is discussed in Microsoft KnowledgeBase Article Q194124.

Microsoft recommends retyping the spreadsheet, or altering the connection string.

Please note the warnings regarding using the IMEX
setting in the knowledgebase article.

In order to alter the connection string Monarch is using to import data you must use the IMEX=1 setting described, by changing the registry setting -

HKEY_CURRENT_USERSoftwareDatawatchMonarch ProImportExcelJetFormat to "Excel 8.0;IMEX=1"

You also need to check that:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesEx celImportMixedTypes is set to "Text"

You may also have to increase the number of rows checked when the Excel driver examines the file to determine the datatype in:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesEx celTypeGuessRows

Sorry it's not simpler.

[Ben]

It's working !!!!

Thanks a lot.

[Tom Whiteside]

Ben,

Double-check that the first (top) field value of your alphanumeric field is either alphanumeric, or, filled in with an apostrophe ('), to force Monarch to read it as text/character. Monarch "judges" a spreadsheet field's type by looking at it's first (top) value, and sets the field format accordingly. Even if the first few field values are null (empty), I have had Monarch judge that as a numeric field. It has just become a necessary precaution to quickly examine all spreadsheet top field values before opening them with Monarch. :eek:

Tom Whiteside
whitesidetom@yahoo.com