3 Replies Latest reply: May 15, 2014 10:03 AM by RalphB _ RSS

    External Lookup problem

    Itech _

      I am having trouble with External Lookups to Excel 2007 spreadsheets using Monarch V10.


      The lookup was setup to match on a two position field and works fine most of the time, but under certain circumstances causes an error. 


      Say that Column A is the two position matching field and is of type text.  If column A has a non-numeric character in any row then the lookup works fine .  However, whenever all of the values in column A are numeric then Monarch tries to import the column as numeric and the model gives an error message.


      This data will work correctly.

      Column A     Column B

        '12               Test1

        'AB               Test2


      This data will cause an error.

      Column A     Column B

        '12               Test1

        '34               Test2


      Any idea on what is causing this problem?  This doesn't happen when I use Excel 2003.  I know I can switch to a database but would like to know if there is something obvious I am missing.  Like a way to specify the format type of a lookup field used for matching.

        • External Lookup problem
          Grant Perkins

          Hi Itech,


          At first (and second and third!) reading this sounds like a difference in the way Monarch's Excel interpreter interprets the cell in the worksheet but, out of interest, is that cell in a consistent CHARACTER column or is it a mixed column of Character and Numeric cells? (I assume it is the exact same worsheet you are using in the 2003/2007 comparisons.)


          What I'm thinking is that knowing whether, for example, the 2003 interpreter reads a numeric and converts to character whereas the 2007 does not conversion OR the 2007 interpreter, perhaps, reads each cell and ignores the current type, re-inerpreting on the way in.


          As Excel can have cell level types but Monarch required a field (column) to be of a single type I would guess that there is some interpretation checking required whichever way things are done.


          I would guess that you could do the comparison via a further interpreter field. How complex that might become would depend on whether the Excel column was ALWAYS Numeric or could be a mix on some occasions.


          Just 'thinking aloud' at the moment.




            • External Lookup problem
              Itech _

              I'm inclined to think it is a difference in the Excel interpreter as well. 


              The column is a consistent character column.  Interestingly, even if all of the cells in the column contain all numeric characters, if 1 of them has a leading zero then the column is pulled in as text.


              The lookup spreadsheets are created by the first of two projects creating a report.  For now I will just plan on exporting the lookup to an Excel 2003 spreadsheet.  However, I wish Monarch v10 had the ability to specify the column type when setting up a lookup.