6 Replies Latest reply: May 15, 2014 10:08 AM by Olly Bond RSS

    Model is sensitive for source database?

    Hirono _

      Good afternoon,

      I need your assistance.

       

      Since my data source excel spreadsheet (Excel 2003) added many records and hit the limitation in number of rows, I converted into Excel 2007.  I saved as xlsx file.  Source file is now ok that I continue to add records; but I encounter a problem when I open the file with the monarch model.

       

      Now, the model I have been using won't open with the data source.  Am I not supposed to use the same model, should I re-create one?  Does monarch models detect excel version even though data content/fields are same?

       

      Model has many formula based values or liked values--if I need to re-create one, is there any way to re-create/copy model easily and accurately?

       

      I will appreciate your assitance!  Thank you.  Hirono

        • Model is sensitive for source database?
          Hirono _

          If needed, I am the user for Monarch 10.5.  Thank you.

            • Model is sensitive for source database?
              Olly Bond

              Hello Hirono,

               

              The reference to the source file will be in the project file, not the model file. I've had a look at a model that references data from an xlsx file but there's nothing that stands out in the XML.

               

              I think you'll need to open the new database and define the basic table fields again, but all the other features of the model (filters, sorts, calculated fields, summaries etc) can be imported from the original model.

               

              To do this, go to the relevant dialog, e.g. Data > Calculated Fields and click on the Import button. Then direct Monarch to look in the old model file and select which fields you need.

               

              HTH,

               

              Olly

                • Model is sensitive for source database?
                  Hirono _

                  Mr. Olly,

                   

                  Will you please explain about the first two sentenses you mentioned.  I am

                  sorry but I did not understand.  

                   

                  I am not sure why but i can open my converted file on Excel as xlsx, but i encounter "Not Corresponding" error when i try to open the source via Monarch 10.5. 

                   

                  I just did quick test taking a few records and saving as new source file in xlsx.  I could open it via Monarch.  So, my converted file is somehow bad file for Monarch, even though i can open via excel...  Could this be happened? 

                   

                  Thank you.  Hirono

                    • Model is sensitive for source database?
                      Olly Bond

                      Hello Hirono,

                       

                      I've just tested this with some data - I made a small table in Excel 2003, and made a copy of this table in Excel 2007 also.

                       

                      I opened the 2003.xls file in Monarch, and created a calculated field, and then saved the model file. I then closed Monarch.

                       

                      I then opened 2007.xlsx in Monarch, and at the dialog told Monarch to use the model from the previous step. Monarch opened the data fine, and the calculated field was present in the table window.

                       

                      Is the data very sensitive, or would you be able to email it across? The best address to use is olly@greenbar.info[/email].

                       

                      Best wishes,

                       

                      Olly

                        • Model is sensitive for source database?
                          Hirono _

                          After reading your test result by knowing you could do successful, I tried one more time (with long patience) opening xlsx file with the model I have been using for excel 2003.

                           

                          It took 4min and 50 sec opening the file with model; it is successful.  (I guess I encounter "Not Responding" was in the middle for process and I hit "Next" command button that I was not supposed to.) 

                           

                          Mr. Olly, however though, I have one more to ask--is there any idea to shorten this long process time or should I wait that long every time I need to work on the file?  If you could advise, I will appreciate it very much! Hirono

                            • Model is sensitive for source database?
                              Olly Bond

                              Hello Hirono,

                               

                              I'm glad you got it to work! As Excel 2003 could not hold the data, I know you have more than 65,000 records. But I don't know how many you have now - 1 million? And how many columns? I've seen Monarch take a few minutes to open large databases (approaching the limit of 2 gigabytes), but that was on a laptop a few years ago.

                               

                              If you are opening the data straight into a summary window, then you may be forcing Monarch to open the data, calculate some extra fields, go through the data once to work out which records meet the active filter, and then go through the data twice more to calculate and display the summary.

                               

                              You might find that setting the active window to Table and the default filter to None will let you open the data more quickly. I also tend to use Access MDB format for data rather than versions of Excel, as Monarch stores data in memory in MDB format, but I don't have any technical evidence that this should be faster than XLSX. Perhaps Gareth can confirm?

                               

                              Best wishes,

                               

                              Olly