6 Replies Latest reply: May 15, 2014 10:09 AM by Datamax _ RSS

    Problems with Excel-database (.xlsx)

    Datamax _

      Hello

       

      I have created a Monarch Pro project-file and a model-file to access a database (Excel 2010, .xlsx) and two look-up-tables (also .xlsx) with Monarch Pro V 10.5 (German). Everything works fine as long as I don't need a new database or new look-up-tables. It's clear that the new database and the look-up-tables have same data structures as defined before, must have same names and must be located in the same directory.

       

      What I did: I renamed the "old" database and moved a new database into the directory, with same name and location as defined in the project-file. Monarch Pro is not able to find the database.

       

      If I rename the old database, Monarch Pro can open the "original" database without any problems. It seems that there's in some ways a "physical" link to source data... I know this sounds strange but I have no better explanation...

       

      If I need to work with the database with new contents, I have to define the "new" path (which is in fact the old one) and the "new" name (same as before). Then I have to define the fields to be imported again. After these steps, Monarch Pro works fine again until I need an updated database to work with.

       

      Can you help me? Thanks a lot!

        • Problems with Excel-database (.xlsx)
          Data Kruncher

          Hello Max,

           

          As much as I acknowledge that there's real value in using Monarch with Excel files, there may be a better way in this case.

           

          I perform work with a similar approach weekly, in that there's new data for the main database (though my lookup tables don't tend to change to much, a new row or two once in a while is all). My approach with this task has been to add an introductory step and store the data that I receive as an Excel file into a csv file (which doesn't change in name or location). The Monarch project then connects to the csv file as the database source and the Monarch work for this task has been 100% reliable for over a year now.

           

          Using CSV files also often alleviates the problems that are sometimes introduced with how Monarch interprets the data type for a particular column in the Excel file.

           

          I wonder if your problems are due to Excel range names but I couldn't say for certain. Often problems with Excel range names become evident with Monarch exports rather than inputs from Excel files.

           

          Though you've detailed the problem very well, at the moment I don't see why... sorry, interrupting myself. When you first connected to the database (xlsx file), you have the option to connect to the "Worksheet" or "Named Range" objects. I wonder if you selected the "Named Range" object instead of the worksheet object, and that's why it's now failing to connect.

           

          I'd still recommend moving to using a CSV file approach for this type of task[/I] as it works very well.

            • Problems with Excel-database (.xlsx)
              Datamax _

              Hello Data Kruncher,

               

              Thanks for your help! Monarch Pro reads that CSV-database very fast and accepts now updated (new) databases without problems!  Unfortunately, there are semicolons (= separation value for CSV-format) in some textfields. That's why I have chosen the tab-separated text format as export option to keep already existing Excel data structure. And..that's it!

               

              I have connected my previous Excel-database and the look-up-tables to specific worksheets; there were no named range objects.

               

              By the way: I'm a subscriber of your very useful/interesting newsletters and I have purchased your Monarch Pro training-book (30 Days...). Probably 30 hard days for me to work it all through, but I'm sure it's worth!

                • Problems with Excel-database (.xlsx)
                  Steve Caiels

                  Hi Max,

                   

                  I think Monarch will be looking for a named range in the 'database'. 

                   

                  The Name Manager can be found on the formula tab in Excel 2007, I'm not sure where it is in 2010.  You need to highlight the entire data set and give it the same name that you had in your old database.

                   

                  Regards,

                  Steve.

                  • Problems with Excel-database (.xlsx)
                    Data Kruncher

                    Monarch Pro reads that CSV-database very fast and accepts now updated (new) databases without problems![/QUOTE]

                     

                    Glad to hear that this approach with the little twist of tsv instead of csv has worked for you.

                     

                    As you might imagine, I haven't yet sold an abundance of the 30 Days training packages to customers named Max in Switzerland, so I wondered if it was you. I'm pleased to learn that you're enjoying the site and the training package so far.