3 Replies Latest reply: May 15, 2014 9:57 AM by Dee Moore RSS

    Altering Field Types

    ron cousino

      I am using Monarch Pro version 8.0 and find that Monarch will change the field type when opening a database which creates problems on external lookups.  I am taking an Excel file that lists data by account number.  I change the format to text since Monarch interprets my main database account number as a character.  When I try to do the linking I get the error that the Source and Table windows must be of the same type.  Monarch changed my Excel database field back to a numeric.Why does Monarch do this and what is the cure?

        • Altering Field Types
          Data Kruncher

          Hi Ron,

           

          In Excel, when you change a cell's formatting to text, it doesn't actually change the underlying field type, it only changes the on-screen presentation of that value. So Monarch still sees a numeric field.

           

          Suppose that cell B2 has the value 4000 under the heading AcctNum. To convert the numeric value to a text value, type the formula =TEXT(B2,"0") into, say, cell A2. Type the heading TextAcctNum into cell A1. Copy this formula to all of your account number rows and save the file.

           

          When you define the external lookup in Monarch, connect the TextAcctNum to the account number field in your lookup table that Monarch already recognizes as Character, and all should be fine.

           

          Let us know if this is a practical solution for you, and if it does indeed resolve your problem.

           

          Regards,

          Kruncher

          • Altering Field Types
            Grant Perkins

            Hi Ron,

             

            Taking Kruncher's suggestion from the other direction, you can make the lookup work using a calculated field.

             

            So if your lookup Excel field is really numeric but your master database field is alpha just add a calculated field to your model to make a numeric version of your alpha field.

             

            VAL(Alpha_field) will do that.

             

            Then when you define the link to the Excel lookup file make the link using the new numeric calculated field rather than the original alpha version of it. Should work fine even if you hide the field.

             

            Remember to save the model and the project if it is something you repeat regularly!!

             

            So two approaches to choose from. I would use whichever is the most convenient at the time.

             

             

            Grant

             

            PS. the STR() function would allow the opposite (numeric to alpha) conversion should it be required.

            • Altering Field Types
              Dee Moore

              I have another option for you.........

               

              Use Access, a true relational database with supported Datatypes.

               

              Import your Excel files to create new Access Database files and join them in Monarch instead of the Excel files.

               

              Regards,

               

              Dee Moore

              Datawatch Technical Support