4 Replies Latest reply: May 15, 2014 9:54 AM by joey RSS

    Import data file from Access

    Kent Johnson

      Okay I'm new to this forum but I've found tons of good information by searching the archives.  I can't seem to find anything about this so here goes.  I have many reports that I'm Importing into Monarch from MS Access.  The problem is I'm having a formatting issue.  When I view the data in Monarch it looks fine but when I try to export the data to a third party appliction the data is shifted over and being truncated.  Here is the formula I came up with to shift the data to get it to stop truncating:

       

      Right(Stuff(AC1,12,1," 0"),11). 

       

      I know I shouldn't have to do this but I'm not sure why this is happening and I'm hoping someone can point me in the right direction.  I'm using Monarch pro v7 ( I know it's old) and I'm importing from an Access 2003 database.  To add one more issue on top of this.  I would like this information to be black if no character is in the field.  (in other words I don't want it to print if the field is null)  Any handy tips for doing that???

       

      Okay bring on the questions....and thanks in advance.

      KJ

        • Import data file from Access
          joey

          Welcome to the forums Kent.

           

          A few questions that may help answer your questions.  Is it all columns or just a specific column that is causing you problems?  If it is a specific column, what is it defined as in Access?  And in Monarch?  When you say that it has issues when exported to a third party product, is it a specific export format?  Have you tried exporting to Excel, test, and perhaps another Access database?  Hopefully these questions will help to show where the problem is occuring.

          • Import data file from Access
            Kent Johnson

            Joey,

             

            Those fields are defined in Access as number fields.  From there I have to change them to character in Monarch for the 3rd party application to accept them.  This issue affects about 30 columns in Access.  Some of the columns import just fine but for some reason these require additional formatting from Monarch.  I will try to export that into Excel to see if that makes any difference.  I will keep you posted.

            • Import data file from Access
              Kent Johnson

              Joey,

               

              As it turns out there was erroneous data in the fields coming out of Access.  I'm not exactly sure why but there seems to be a "'" in the left side of those data fields.  Now I just have to figure out how to get rid fo that???

              • Import data file from Access
                joey

                I suggest pulling the numeric columns from Access to a numeric Monarch column.  Make sure the lengths of the data match.  Then, add a calculated field to convert the numeric column to a text field in Monarch.  Since you want a space instead of a null for cases where there is no numeric data, I would say

                 

                if(isnull(Numeric-Field)," ",Numeric-Field)

                 

                Hopefully, this will solve the problem for you.  You're doing the conversion from Numeric to Text explicitely using Monarch's caluclated fields.