2 Replies Latest reply: May 15, 2014 10:16 AM by adonis _ RSS

    MS Excel Imported into Monarch (field Size issue)

    adonis _

      MS Excel Imported into Monarch (field Size issue)

      Monarch Pro 10.01


      Do the newer versions of Monarch after Monarch Pro 10.01 handle the bug that occurs when converting an MS Excel Field into Monarch Database Table Field where the field may have a max field value i.e. 15 bytes currently but subsequent files may have larger sized fields which makes the field i.e. 20 bytes but Monarch will automatically upload the spreadsheet and cut off the last 5 digits because of the size.


      is there a work around for this or is this something that has been fixed afterwards in newer versions?


      Or is there a way to update the current project to take into account the larger sized fields?  When I refresh the database upload process thinking it will go to the location, pull the new file and then elongate to fit the width, but nope, that doesn't work using the Open Database and selecting the data source and re-establishing the fields to be used etc.

        • MS Excel Imported into Monarch (field Size issue)
          Olly Bond

          Hello Adonis,


          Monarch handles four types of data. These are character fields (text up to 254 characters in length), date/time fields, numeric fields (18 characters in length, allowing for numbers like -1.2345678901234E-10) and memo fields (blocks of text up to 32kb long).


          The behaviour you observe is when you have a model to deal with date like:


          "Mozart","Mass in C","01/01/2013","$9.99","5"[/CODE]


          The field list here might show a length for the Composer field of 6 characters.


          When you use it handle data like:


          "Rimsky-Korsakov","The Symphony With A Very Long Name","02/01/2013","$19.99","3"[/CODE]


          you get data like "Rimsky" which is no fun.


          The workaround is to edit the fields in the model - it's easiest in the field list - to make sure that your Character fields have a sensible Data Length. Note that this is different from the Display Width - I can set the Composer column to be 1 character wide or 100 characters wide without changing the Data Length. Set the Data Lengths to be whatever you need, up to 254 max, and save the model, and your data will be fine.


          The reasons Monarch doesn't default to making the data length of every character field to be 254 are first, that it's expensive in processing time and storage to create all that empty data, but more important that the underlying Access database structure doesn't allow for the overall length of all the fields for one record to be more than a few thousand characters. So if you have ten character fields, and a couple of date fields, and ten numeric measures, if all your character fields were the maximum length then the database engine would fall over.


          You can get around this, if you have lots of long data fields, by defining the fields as Memo - as these don't count towards the overall line length. But watch out - you can't use Memo fields in summaries.


          Hope this helps,