3 Replies Latest reply: May 15, 2014 9:57 AM by Tom Whiteside RSS

    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

        • Excel (Database) - lost data
          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.

           

          Ben[/b][/quote]There 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.0EnginesExcelImportMixedTypes 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.0EnginesExcelTypeGuessRows

           

          Sorry it's not simpler.

          • Excel (Database) - lost data
            Ben _

            It's working !!!!

             

            Thanks a lot.

            • Excel (Database) - lost data
              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[/email]