9 Replies Latest reply: May 15, 2014 9:59 AM by Burlch _ RSS

    External Lookup - Excel errors

    Zimkatz _

      Can anyone help me please


      i have a large number of reports set up through Monarch, my starting report is html and the model converts this into a report which then has an external lookup to an excel file.  If the information in the current report does not exist in the excel file it brings back a null value.  Then i export all the fields with a null value back into the excel file.  This is done as the html file is updated daily and i need the new information in my excel file. 


      This model with the lookup works fine on 20+ different files, but all of a sudden one of them has come up with an error which reads


      "An external lookup data source “K:service…” is either missing or invalid.  From the table menu choose data/external lookups to select a new data source for the offending lookup"


      i have tried re-creating the excel file in case this one was corrupt and i still get the same error, i also tried setting up the model again using one that does work and i still get the same error.


      now im lost...

        • External Lookup - Excel errors
          Grant Perkins



          The usual source for this type of message is either an edit of a script (or model in this case perhaps) where the full path to the link has been slightly miskeyed on entry and therefore does not exist  - difficult to spot without checking character by character - or something else has changed or been changed that affects the link.


          It looks like you Excel file might be on a server, given the drive name is 'K:'. So the first check is whether you can still see drive K: and if you can can you also see the Excel file that you look up from? Does the path to it match exactly (Make sure it has not been renamed at any point.) Secondary to that is ensuring the file has content and has not been changed in any way that makes it incompatible with the link you have defined.


          You may also want to consider a less explicit link by naming the server in the path rather than referring to your local drive K: in case the drive mapping changes at any point.


          The second consideration is whether anything has change at the server end that might mean you process no longer has access to the file - access rights and security for example. Has the server operating system been upgraded recently?


          One thing to be careful about, especially if you are running the process as a batch rather than interactive activity, is whether the user account running the batch file has the same access rights as your personal login.


          The other thing with batch based processes, especially if run overnight, can be that files may become temporarily unavailable for backup processes, for example, or because the routines that create them are running later than they used to run. This can cause confusion when processes fail intermittently, as a prelude to consistent failure, when backup times extend as the amount of data to be backed up grows but varies from day to day for example.


          I'll take a guess and suggest that you may want to talk to your system admin people to see if anything has been changed at around the time your problem appeared. If not at least you should have eliminated one possible problem source. Maybe.


          I hope this helps.




          • External Lookup - Excel errors
            Zimkatz _

            Dear Grant,


            Thanks for your reply - sorry i should have included this information in my post.


            Yes the file is on a server, but when i have tried re-creating the monarch model etc i always go through the external lookup and use the browse function to navigate to the file i need to use.  If the network was down, it would not be available there.


            Also, i am the only person in this organisation who uses Monarch - which is also why i get distraught - no-one to help me:(, so nothing else has been changed.


            As i said in the original post, i have about +- 20 different excel spreadsheets that are set up in the same way as this one and there are no problems with them.  so i am really really confused as to why this one has all of a sudden shown an error. 


            the only batch system i use is to save the project on the desktop as a .bat file but i have to double click this in order for the batch to run. 


            Any other ideas???

            • External Lookup - Excel errors
              Grant Perkins

              Ok, so one out of 20 or so does not work but the rest are all accessed in the same way at the same place (or similar?) and even if you recreate the model for this one it still does not work.


              In that case rather than the most common 'missing' problem we need to consider 'invalid'.


              There are some notes in the elp files whihc may be worth glancing at to refresh your memory - I usually do when dealing with this sort of problem.


              'Invalid' might mean that something in the data or structure of the xls file has changed. Is the file sourced from somewhere that may have changed the format? Or perhaps there are some data duplication problems, though I would expect to see a different message for that.


              Another thing to double check is that the link is to something that will provide the right data - or indeed any data. For instance make sure that the link points to the Excel table and that the table is populated. In some cases you may need to link to a named range in a table and this can cause problems if changes are applied to the xls file which may ignore the existence of the named range OR the range is selected in such a way that it is no longer appropriate for the purpose and returns an error.


              Effectively the error message is telling you it cannot verify that the file is OK BUT neither can it be specific about what is wrong. If you can copy the file and run the process pointing to the local version and yet still get the same error than it would be more consistent with having a problem  with somthing in the xls file rather than on the network setup.


              If you have the option to send the file and the lookup file(s) out of the company I would be happy to take a look at it for you to see if I can spot anything.


              Let me know and I will provide you with an email address to send to.









              Sometimes it takes a fresh pair of eyes to spot the problem.

              • External Lookup - Excel errors
                Zimkatz _

                Hi Grant,


                Yes I do agree, a fresh pair of eyes sees a lot more than the frustrated ones!!!


                i am more than happy to send you the information - its not very sensitive - its a list of invoices basically.


                if you give me your email address, i will send the files and explain everything in it for you.


                You've been a great help!

                thank you so much.

                • External Lookup - Excel errors
                  Grant Perkins

                  Interim Update for general reference.


                  This looks like a corrupt Excel file problem. Oddly it looks OK if you view the worksheets in Excel but reading them into Monarch as a database presents some cells as blanks though they are populated in the Excel view.


                  My first guess is that the Excel code works around the corruption internally for pragmatic reasons but is less 'helpful' interfacing to external systems.


                  Or you could take a view that such problems would be best spotted, reported and fixed by a suitable utility tool within Excel ...


                  Of course there may be some other technical explanation. Anyone out there got any suggestions?




                  • External Lookup - Excel errors
                    Burlch _

                    We are having a similar issue with an external lookup to an excel sheet.  The excel sheet has location, product, quantity, cost and cost per ton (linked on location and product).  It is also created by another Monarch project (untouched by human hands).  Everything works fine from my workstation.


                    The issue is when I set the same projects and models up on another workstation.  We first get the "either missing or invalid" error message.  When we edit the external lookup and browse to the lookup source, Monarch finds the file until you finish the edit.  At that point, you get the "either missing or invalid" error message again.  When we delete the external lookup and recreate it, it will read the data, but now our numeric fields are read in as text.  Consequently, none of the calculations using the external data are valid.


                    Is there any way to force the data type in the external lookup?

                    Is there any known condition which causes the "either missing or invalid" error?

                      • External Lookup - Excel errors
                        Grant Perkins



                        Very often the 'missing or invalid' messages indicate some sort of access rights problem  - permissions in general. Could be the file or it could be an entry in the registry. Have you already looked into those sorts of issues up to now?


                        I think the recreation of the lookup sounds like it is reverting to default but you may have redefined the imported fields in the model's original lookup? A bit of a guess. I assume you modelled in V9 Pro and the other workstation is also running V9 Pro?


                        A couple of diagnostic process starter question rather than a solution. Sorry about that but hopefully they will help lead us to the answer.